Report Table - Dynamically select columns in final Report

[roshan_dsouza23]

I tried reading through a number of articles and realized it's not as flexible in 'Dynamic Select' to pick the columns which will finally reflect in the 'Reporting -> Table' tool.

I have a report to show all Quarter Data based on the reports I add to the workflow for comparison as Previous vs Current Quarter . It's a dynamic column and will change in the YYYY value too i.e. 2020 -> 2021 -> 2022 .. 

How do I capture something like this as the 'Reporting -> Table' doesnt allow you to use select rules. Appreciate some help and alternate approaches.

[shreyanshrathod]

Hi @roshan_dsouza23 ,

Not sure if I have interpreted your requirement accurately, but you want two columns (whose name are current quarter and previous quarter) out of all columns you have, is that correct?

Regards,

Shreyansh Rathod

[roshan_dsouza23]

Hi Shreyansh,

Selecting 'Current and Previous Quarter' is relatively straightforward as the naming convention is constant however the problem is with the columns that have a YYY MM DD format. Note these columns are built at run-time based on which quarter report I load for comparison hence its a bit 'dynamic' and I can't pre-determine the field name until the data is loaded in the workflow and a column created.

In below example I loaded data from 3 quarters i.e. 2020/09 , 2020/12 and 2021/03 . Similarly I could load data across 'n' number of quarters and the fields would automatically be created and last 2 selected to denote previous and current quarter for threshold check. Only other option is I always show the 'Current and Previous Quarter' fields but that a compromise 🙂

Ideally I would have liked a way to select something based on data-type e.g. 'Double' as in Dynamic Select and that way be assured all YYYY MM DD names columns are picked . Another option is can we do RegEx i.e. 2xxx% and not be worried for decades and centuries ?

[apathetichell]

you're trying to change the column names in the table tool to match your dynamic select?

you can change the table tool via an action tool so you'll have to figure out what kind of interface tool you can use and how to do it - the most obvious is a control parameter/batch macro solution - with a slew of action tools to update the fields in the table to reflect the value being passed into the control parameter.

[shreyanshrathod]

@roshan_dsouza23 , still not able to interpret your query.

But I think this should help.

If out of all YYYY MM DD columns, you are looking to select only 2 quarters (Previous YYYY MM DD and Current YYYY MM DD), you can do so via "Select via a Formula" in DYnamic Select.

ALso, in the Table tool, the "Dynamic and Unknown fields" box allows all future columns (that are not currently present). SO you should also see whether you want to check or unckeck that.

Regards,

Shreyansh Rathod

[gautiergodard]

The best solution I have found for this issue is stitching two basic table tools together. By default, the table tool has the "Show Column Headings" option checked off. In this solution you will uncheck this option on both tables, and create one table for your column headers which you can dynamically rename as your column headers change, and another table containing the underlying data. 

Attaching a simple example to help illustrate the solution. 

Hope this helps!