Date Prompts Filters/Slicer in DMR models

[Cognos Talk]

I am developing report off the DMR model and trying to apply a filter or slicer in the query and it doesn't work.

i have date to and date from prompts, which are regular dates passed from parameter page. Filter reads: [Calendar Date] between #prompt('pDateFrom')#
AND #prompt('pDateTo')#
Calendar date is cast([DMR].[Calendar Day].[Calendar].[Day].[Calendar Date],date)
What is my filter should be or maybe i have to do the slicer? How should I orginaze it properly?
Can you kindly advise? Cognos is v10.2
Thank you

[CognosPaul]

When working with DMR, you should try your best to keep your query built dimensionally. The DQM can get ugly if your try to build a query incorrectly. If your model is DMR, think of it as OLAP and stick within the restrictions of OLAP. That includes no detail filters and no casting.

You're referencing the Calendar Date attribute in your filter. DMR supports dates as attributes, so why don't you drag in a field that's already in date format?

Next, are you using the date inside your report, or do you need to aggregate the report by that date range? Try creating a data item (dateRange) with:

Code Select Expand

filter([DMR].[Calendar Day].[Calendar].[Day],[DMR].[Calendar Day].[Calendar].[Day].[Attribute in date format] >= #prompt('pDateFrom','date')# and [DMR].[Calendar Day].[Calendar].[Day].[Attribute in date format] <= #prompt('pDateFrom','date')#)

Will return a set of the dates in that ragne. Then you can put:

Code Select Expand

member(total(currentMeasure within set [dateRange]),'myDateRange','myDateRange',[DMR].[Calendar Day].[Calendar]) into the slicer.

[Cognos Talk]

Thank you very much for this useful information. However i could not make it work properly, my data item (filter) gives me an error, can you kindly take a look inside, i am attaching report itself the query is called MCI.
Also should i continue and use date from and date to or use date range parameter that will cover the range???
Thank you in advance for your help

[CognosPaul]

To begin with, try to avoid using casting in DMR. NEVER use casting when working on a true OLAP source. Same with substring.

Also, you're trying to find change in a relational way, but since this is DMR you're just giving yourself more work. Moving on.

Your filter expression is:

Code Select Expand

filter(cast([DMR].[Calendar Date].[Calendar].[Day].[Date CD],date), '[2000-02-01]'<= #prompt('pDateFrom','date')#)


First, you are trying to cast a level, which won't work since a level isn't a value and won't work since cast isn't an OLAP function. Replace that bit with the level itself:

Code Select Expand

filter([DMR].[Calendar Date].[Calendar].[Day],

Next, you're doing the Boolean check against a specific member, February 1 2000. Instead, you should be comparing against the attribute.

Code Select Expand

filter([DMR].[Calendar Date].[Calendar].[Day],[DMR].[Calendar Date].[Calendar].[Day].[Date CD] >= #prompt('pDateFrom','date')#)

Is Date CD in date or numeric format? If it's date, then this should work. If it's a numeric in the ISO 8601 format (yyyymmdd) you can do something like:

Code Select Expand

filter([DMR].[Calendar Date].[Calendar].[Day],[DMR].[Calendar Date].[Calendar].[Day].[Date CD] >= #timestampMask(prompt('pDateFrom','date')+'T12:00:00.0Z','yyyymmdd')#)

The timestampMask function will convert the selected date into yyyymmdd allowing you to use it in numeric filters.