How to Apply date filter in Report using Cognos DMR model

[Kiran Kandavalli]

Team,

1) I have two date prompts -> p_StartDate and p_EndDate in the Report Page. These prompts are optional - when we select the dates and hit Refresh button then the records should be filtered.
2) I wanted to filter the Launch Date based on these prompts selection (i.e.) Launch Date should be in between Start Date and End Date
3) I have attached the Hierarchy path.


Applied filter as below

1) In Data Item Expression --> [Cube].[Promotion Launch Date].[Promotion Launch Date].[Day]
2) Detail Filter --> [Day] between ?p_StartDate? AND ?p_EndDate?

Its working fine when I run the View Tabular Data. when I run the report it is throwing me the below error.

ERROR

The specified parameter type is 'date', but the expected type is 'memberUniqueName'. Error processing filter '[Day] between ?p_StartDate? and ?p_EndDate?'.

Can someone help me on how to apply the filter / Slicer in the report.

Thanks!
Kiran

[MFGF]

Team,

1) I have two date prompts -> p_StartDate and p_EndDate in the Report Page. These prompts are optional - when we select the dates and hit Refresh button then the records should be filtered.
2) I wanted to filter the Launch Date based on these prompts selection (i.e.) Launch Date should be in between Start Date and End Date
3) I have attached the Hierarchy path.


Applied filter as below

1) In Data Item Expression --> [Cube].[Promotion Launch Date].[Promotion Launch Date].[Day]
2) Detail Filter --> [Day] between ?p_StartDate? AND ?p_EndDate?

Its working fine when I run the View Tabular Data. when I run the report it is throwing me the below error.

ERROR

The specified parameter type is 'date', but the expected type is 'memberUniqueName'. Error processing filter '[Day] between ?p_StartDate? and ?p_EndDate?'.

Can someone help me on how to apply the filter / Slicer in the report.

Thanks!
Kiran

Hi,

With a DMR model, the underlying relational data is effectively being converted into dimensional structures (dimensions, hierarchies, levels, members, measures). Instead of having values for each date, you now have members for each date. These date members have a key and a caption. The key is used to identify the member and makes up part of the Member Unique Name (MUN), and the caption is what you see displayed in the report, and is a character value.

Given the above, hopefully you can see that date prompts are not going to work here. These return date values, but your report needs Member Unique Names to be returned. To be successful, you are going to need to figure out what a MUN looks like for your date members - you can see this by right-clicking on a member in the package tree and choosing Properties.

Cheers!

MF.

[CognosPaul]

The trick here is to create the set of dates you need. When building the time dimension in FM, make sure you have an attribute defined that has a date value. Alternatively you could also have an attribute with an int defined as yyyymmdd.

Once you have the attribute defined and associated with the date level, you could define your slicer like:

member(total(currentMeasure within set
filter([Cube].[Time Dimension].[Time Hierarchy].[Date Level],[Cube].[Time Dimension].[Time Hierarchy].[Date Level].[Date Attribute] between ?start? and ?end?)
)
,'DateRange'
,'DateRange'
,[Cube].[Time Dimension].[Time Hierarchy]
)

[HelloCognos]

Thank you Paul. this is very helpful for me also.  :) :)

[Kiran Kandavalli]

Thanks Paul for your response!

I tried the way as you stated and it is not working for me. can you please suggest me to fix this logic.

Applied Slicer as Below

member(total(currentMeasure within set
filter([Cube].[Promotion Launch Date].[Promotion Launch Date].[Day],[Accessories Cube].[Promotion Launch Date].[Promotion Launch Date].[Day].[Day - Long Name] between ?p_StartDate? and ?p_EndDate?)
)
,'DateRange'
,'DateRange'
,[Cube].[Promotion Launch Date].[Promotion Launch Date]
)

Members for Day has been to changed to Integer like 20180101 (Please find the same attached)

[Kiran Kandavalli]

Please find the XML attached for your reference.

This Cube is built in Transformer based on Excel Data and Start Date and End Date Prompt are optional.

[Francis aka khayman]

this might help

http://cognosm.blogspot.com/2015/11/dimensional-current-month.html

[CognosPaul]

It's been a long time since I've worked with PowerCubes, but if I recall the way they implement properties leaves a lot to be desired. They're always strings, even if they are based on numbers.

In this case you need to generate a list of dates to the end date, and EXCEPT a list of dates to the day before the start date.
The first step is to find a way of identifying a member. Can you paste an example of what the memberuniquename looks like? And what does the Day - Long Name look like?

at the end of the day you will need to do something like:

Code Select Expand


member(
total(currentMeasure within set
except(
    periodsToDate([All Level],[End Date MUN]))
  , periodsToDate([All Level],prevMember([Start Date MUN]))
)
)
,'DateRange'
,'DateRange'
, hierarchy)