Applying Date Range Prompt to Filter Cognos Report with Powerplay Cube or DMR datasource by leveraging Multidimensional Expressions( MDX ) and Macros.

The requirement was to setup Date Range prompt to filter a Cognos Report with Powerplay Cube as the datasource. The end users will select Begin Date and End Date, Once the prompt selection is submitted the Cognos report should be filtered for the Date Range selected by the user.

Step 1:
Setup Prompt Page with two Date prompts naming them Begin Date and End Date.

Step 2:  Constructing the Date MUN from the Date Prompt selection made by the user.

The Date member unique men( MUN ) of the cube i am using is listed below

[great_outdoors_sales_en].[Years].[Years].[Month]->:[PC].[@MEMBER].[20160101]

Prompt Selections: Begin Date = 2016/01/02 , End Date = 2016/01/05
Note: we will be deriving MDX expressions for these Date selections throughout the report.

So with the below Cognos macro expression we should be able to construct the MUN for the date selected by the user.

Begin Date MUN Macro expression:
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pBeginning_Date’ ,’token’ ) ) ) + ‘]’#

End Date MUN Macro expression:
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pEnd_Date’ ,’token’ ) ) ) + ‘]’#

Result: Begin Date = 2016/01/02 , End Date = 2016/01/05

Step 3:

Lets assume the cube only has following 6 Days worth of data ( 2016-01-01 ,2016-01-02 , 2016-01-03 , 2016-01-04 , 2016-01-05 , 2015-04-06 )

The following Macro expressions will retun all the dates in the cube till the selected date.

Begin Date Macro:
periodsToDate(
[sales_and_marketing].[Time].[Time].[Time] ,
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pBeginning_Date’ ,’token’ ) ) ) + ‘]’#
)

Result: 2016-01-01 ,2016-01-02

End Date Macro:
periodsToDate(
[sales_and_marketing].[Time].[Time].[Time] ,
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pEnd_Date’ ,’token’ ) ) ) + ‘]’#
)

Result: 2016-01-01 ,2016-01-02 , 2016-01-03 , 2016-01-04 , 2016-01-05
Step 4:
Merging the Begin Date Macro and End Date MDX/ Macros expressions from Step 3

Using Except mdx funtion we can eliminate the dates not in range and returning result set will contain date range needed by the user bar the Begin Date.

except(
periodsToDate(
[sales_and_marketing].[Time].[Time].[Time] ,
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pEnd_Date’ ,’token’ ) ) ) + ‘]’# )
,
periodsToDate(
[sales_and_marketing].[Time].[Time].[Time] ,
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pBeginning_Date’ ,’token’ ) ) ) + ‘]’#
)

Result: 2016-01-03 , 2016-01-04 , 2016-01-05

Step 5:
Union the Begin date mun with the Step 4 output.

Since the Begin date got filtered out of the result in step 4 it has to be added back to the data range using the Union mdx function.
set(
union(
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pBeginning_Date’ ,’token’ ) ) ) + ‘]’# ,
except(
periodsToDate(
[sales_and_marketing].[Time].[Time].[Time] ,
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pEnd_Date’ ,’token’ ) ) ) + ‘]’# )
,
periodsToDate(
[sales_and_marketing].[Time].[Time].[Time] ,
#'[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[‘ + substitute ( ‘-‘ ,” , substitute ( ‘-‘ ,” , prompt( ‘pBeginning_Date’ ,’token’ ) ) ) + ‘]’#
)
) ) )

Result: set( 2016-01-02 , 2016-01-03 , 2016-01-04 , 2016-01-05 )
Step 6:

The set expression from Step 5 can be used as part of the calculation or in report slicer to achieve the desired result.