We have a requirement to build multiple reports/dashboards based on a single data model. Each report needs a group by based on different columns of the data model.
For example, if we have a common data model displaying invoice details, one report needs group by based on BU and the sum of amounts for each BU, other needs group by based on Supplier name and sum of all invoice amounts for that supplier from the data model and so on.
Having multiple groups in the same data set will not help us achieve what we are looking for. We tried the below options.
1. Add multiple data sets in the same data model. The SQL for each data set will remain the same. What changes is the group by in the data set. WE tried using DM property 'skip unused data set query' to avoid running all the data sets for all reports. However, this does not work when the data model is invoked from multiple reports. It works for just one report. The other report does not give data. Let us know how issue with this option can be fixed to make it work for all reports. We do not want all the data sets to run for all reports
2. We tried not using a group by in the data model. Instead handling the group by in interactive viewer similar to RTF template feature. This does not allow us to use aggregate functions forcing us to use the RTF approach(which does not have graphs looking as good as the ones from interactive viewer.
Kindly help us with the 2 issues above and suggest the best possible approach to use the same data model for multiple reports needing different group by