How to create a dynamic custom group to display a set of date ranges in Microstrategy Developer
This document explains how to create a custom group using an ApplyComparison statement to choose from two different date prompts and calculate for the number of different days between the two prompts for the element.
Create two Date Prompts and name them P1 and P2.
Create three filters (F1, F2, F3) using Applycomparison statements. The screen shot below shows the F1 filter which returns the dates between the first date prompt minus the number of different days between the two date prompts.
ApplyComparison ("#0 BETWEEN (dateadd(day, -datediff(Day, #1, #2), #1)) And #1 ", Day@ID, ?, ?)
The screen shot below shows the F2 filter which returns the dates between the two date prompts:
ApplyComparison ("#0 BETWEEN #1 and #2 ", Day@ID, ?, ?)
The screen shot below shows the F3 filter which returns the dates between the second prompt date plus the number of different days between the two date prompts:
ApplyComparison ("#0 BETWEEN #2 And dateadd(day, +datediff(Day, #1, #2), #2) ", Day@ID, ?, ?)
Create a Custom Group and use the three newly created filters to select its elements as shown in the screen shot below:
Save this custom group and name it "Custom Date". Make sure to select the option "Show only the individual items within this element" for each custom group element.
Add the new "Customer Date" custom group in a new report as shown in the screen shot below:
Run the report and select Jan 30 2001 for the first date prompt and Feb 15 2001 for the second date prompt.
The screen shot below displays the first page of the expected report:
