Many a times, I deal with a conundrum to create a table similar to a manually formatted Excel, SSRS or BO report.
One of the standard requirements I have seen is, “How can you show specific measures for certain dimension values and hide for certain dimension value?”
For example, as shown in the depiction below, the user wants to see “Actual” and “Planned” numbers for the past months and only “Planned” for the future months.
One way to achieve this is by creating a straight table and adding separate measures for each column. But this solution will not group the measures by dimensions, and also at the same time it will be challenging to maintain
In this post, I will show you how you can create a dynamic custom pivot table template. First, we will create a report template in the script, and then we will use it in front-end.
Sample data looks something like this,
First, let’s get template months for ‘Actuals.’
Then, concatenate template months for ‘Planned.’
Now let’s use this “AutualPlanned” field as a template in the front end. Instead of adding two separate calculation for ‘Actual’ and ‘Planned’ we will add one measure that will pick measure based on a template field value,
You can use the same method to create a different custom layout with a different number of calculations.
Please see attached .qvf for the complete solution.
