A common requirement of dashboards, scorecards, and mobile BI applications is providing the ability to slice and dice them by time periods: Week to Date (WTD), Month to Date (MTD), Quarter to Date (QTD), and Year to Date (YTD).
One way to achieve this requirement in MicroStrategy is by creating a Report Services Document with a Panel Stack consisting of 3 Panels, one panel for each time period (MTD, QTD, YTD) and the respective time period metrics for each panel.
An alternative approach, which meets the time period requirement and reduces the time to market of the analytical application is creating a Custom Group with the time periods, then using a selector on the Report Services Document to filter the data by the time periods. This solution consists of 1 Panel which significantly reduces the development work and the time periods are data-driven instead of object-driven.
The advantages and disadvantages of using the alternative approach are listed below:
Advantages
- Reduced time to market as there is significantly less development and formatting work involved.
- Perfect for scorecards with no graph time period trends.
Disadvantages
- Possible query performance issues which are dependent on the dataset size/number of objects used, database performance, and Intelligence Server specifications.
- Limited in the ability to display graphs with trending as the x-axis only supports 1 date attribute. In newer versions of MicroStrategy, 10.4 and above, this limitation can be overcome by creating a Selector with the Select Attribute option and adding the Day, Week, and Month attributes as the Candidate Attributes to filter and target the trending visualizations.
To implement this solution, follow the detailed steps below:
1. Create three time period date filters, one for each time period (Month to Date - MTD, Quarter to Date - QTD, and Year to Date - YTD)
2. Create a [Time Period] Custom Group and add the time period filters created from step 1 as shown in the image below:
3. Add the Custom Group to your report/dataset Page-by section.
4. Check your dataset SQL code, joins, and results to make sure the custom group is working as expected.
5. Create and format the Report Services Document.
6. Add an attribute element selector to the Report Services Document, uncheck the "Show option for All" option under the Selector properties, and select your [Time Period] Custom Group as the Source for the Selector.
Helpful Links: Creating Custom Groups, KB32015, KB33950
