I would like to share of my experience in calling a report from multiple reports.
Let me explain in terms of Paint subject area.
Case 1:
We have a detailed report, which can be called from a master report.
The master report is made of Region column, Year and Dollars.
The detailed report is attached to the dollars column so that the transaction details for the corresponding region and year can be displayed.
When the user clicks on the Dollars column then the detailed report will be called
This will work fine as long as the user clicks on the dollars column.
What if
1. The user drills down to markets first then clicks on the dollars column to get the transaction for the region, market and year?
2. The user drills down to month by licking on the year column and then clicks on the dollars column to get the transactons for a region, year and month?
3. The user drills down to any level of the hierachy and clicks on the dollars column to get the transaction level details of the dims?
In any of the above situtations, the detail report will show the transaction related to the parent region and year column. In order to display the transactions for any level in the markets dim or time dim, all the column of the markets dim and time dim should prompted in the detailed report.
In the same way, if the same detailed report needs to be called from another report constructed using a top level column from products and year column then the detailed report should be ready to accept any level of dim value from product dim or year dim.
In general a detailed report can be called from any master report and the master report can be constructed by using any column at any level from any any dim. So, the detailed report should be ready to display the transaction for any dim value at any level.
For this reason, we can add all the columns from the all the dims as prompted filters to the detailed reports.
Csae 2:
The master report is added to a dashboard page, which is having year prompt and brand column from product dim prompt.
So the master report should be prompted for year and the brand column.
In future if another prompt is added to the same dashboard page then, the master report needs to be modified by added that column as prompted filter.
In other words, the master report shold be ready to accept any dim value from the prompts.
In this case also, we can add all the columns from the dims as prompted filters to the master reports
In both the cases we have added all the columns from the all the dims as prompted filters to both master and detailed reports.
Now, what if, one of the dim is change, like a new column is added or a column is removed?
If the column is added then its ok for the reprots, only thing the reports are not ready to accept the value. But if a column is removed then, we have to open each report and remove the column from the filter; other wise the reports will throw error.
Again, as per the case 1 and case 2, the added column needs to be added as prompted filter in the all reports; means, all the reports needs to be modified one by one.
Changing all the reports is not practically possible.
What we did is, we have created some saved filters, one for each dim. These filters will contain all the columns of the dim as prompted as shown below
A saved filter for all the columns of Markets Dim
A saved filter for all the columns of Products Dim
A saved filter for all the columns of Periods Dim
We have created another saved filter called “All Prompted Objects” which is a combination of all the filter which were created for each dim as shown below
Now we have added this “All prompted Objects” to all the reports (master and detailed).
So if a column is added to the dim (say to Products dim), then we will add the column to the “Products objects filter” and the “All prompted objects” will use that. In the same way, if a column is removed, then the column will be removed from the “prompted objects filter” and the “All prompted objects” filter will not use it.
One of the new joinee in our team asked us, why we have to create the filters first at the dim level and bundel them into one “All prompted obects”, why all the collumns can’t be added directly to the “All prompted object” filter?
Well, the reason is, there are few reports which should not accept the values from one dim (say time dim). For that report we have to create another “All prompted object without time” and add all the columns as prompted. Like we have another report which should not accept values from Region Dim. So have to create another “All prompted objects without Region” filter.
Now, if a column is added or removed from a dim, then we need to modify three “All prompted objects” filter. Instead of that, if we create a save filter for each dim and create “All prompted obects” on top of them, the we have to modify only one saved filter.
Now we have added this “All prompted Objects” to all the reports (master and detailed).
So if a column is added to the dim (say to Products dim), then we will add the column to the “Products objects filter” and the “All prompted objects” will use that. In the same way, if a column is removed, then the column will be removed from the “prompted objects filter” and the “All prompted objects” filter will not use it.
One of the new joinee in our team asked us, why we have to create the filters first at the dim level and bundel them into one “All prompted obects”, why all the collumns can’t be added directly to the “All prompted object” filter?
Well, the reason is, there are few reports which should not accept the values from one dim (say time dim). For that report we have to create another “All prompted object without time” and add all the columns as prompted. Like we have another report which should not accept values from Region Dim. So have to create another “All prompted objects without Region” filter.
Now, if a column is added or removed from a dim, then we need to modify three “All prompted objects” filter. Instead of that, if we create a save filter for each dim and create “All prompted obects” on top of them, the we have to modify only one saved filter.
Comments
Post a Comment