The new reporting tools provide great flexibility in generating a report. The majority of reports I create generally contain tables that have static columns populating the table. So even though the data populating the table may change, the overall table structure and appearance does not. Recently I was posed with a challenge to make a table dynamic meaning the number of columns in the table can change at the drop of a hat based on user defined selection through a wizard or macro.
After thinking about the problem I decided that the first thing I would have to do is create a column of data that I would be able to filter to pull the proper columns for my table via the transpose tool. Once I had all of the data contained in filterable column I was able to isolate the columns to appear in the final output table report. From here, I had to then cross tabulate the data back into its original form, but this time it would just be a cross tabulated table containing my selected variables.
Now, I just needed to create a table and render the results. Easy enough on paper... but the table tool when set to create a basic table is always looking for certain columns of data to be present. If those columns aren't there then, kaput, the Alteryx module will fail because the Table tool is not configured properly. However, if you configure the Table tool to create a pivot table, then the only column of data the tool is looking for is value sum from the cross tabulated data, thus making it possible to have an ever changing table created simply because that unique field is always there!
To see this in action download the sample module here.
