How to use 2 date prompt in a slicer on a dimentional cube

[NewtonsCannon]

Hello;

It took me a long time to get this to work after looking at many examples.  Since I now have it up and running I thought it best to share.

Date dim is Time/Date
Using a Cube
Cog 8.4

1)Create a prompt page with 2 prompts.
2) on a query , create 2 data items;
StartRange     

Code Select Expand


[Sales Written].[Dates].[Dates].[Day]->:[PC].[@MEMBER].#sb(join('',split('-',prompt('FromDate','Date'))))#

EndRange

Code Select Expand


[Sales Written].[Dates].[Dates].[Day]->:[PC].[@MEMBER].#sb(join('',split('-',prompt('ToDate','Date'))))#

This creates 2 muns.
3) new data item , StartFilter

Code Select Expand

intersect(lastPeriods(-9999,[StartRange]),lastPeriods(9999,[EndRange]))
4) drag StartFilter into the slicer.
I saw many peeps talk about the filter function.

Best of Luck.

[gosoccer]

Thank you so much for sharing.
:(I am using your suggestions for a simple date range (From: To:). I created the two Data Items (Macros) and created the StartFilter. I am not sure about "drag StartFilter into the slicer" since I haven't used slicer previously. It's about 4 days now and I'm not getting anywhere with the date range issue in our Dementional Cube using Report Author. I'm getting data returned but the same exact data everytime no mater what date range I give it.

Here is what I have tried to do but not getting anywhere,

filter(
[Time Dimension].[My Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[My Hierarchy])) >= #sq(prompt('From', 'Date'))# and
roleValue('_businessKey', currentMember([Time Dimension].[My Hierarchy])) <= #sq(prompt('To', 'Date'))#
)

Any help would be a tremendous help on this.

[CognosPaul]

There are a few ways to filter date hierarchies with date prompts, but it depends entirely on your olap setup.

The first question is, what type of cube are you using? Powercubes, MSAS, Essbase, or something else entirely?

Do you have any date attributes set up. Remember, you can't filter dates against strings. In your example, you're asking Cognos to filter the date hierarchy where the business key string is within the date range. What you could try doing instead is:

Code Select Expand

filter(
   [Time].[Hier].[Date].[Day Level]
, [Time].[Hier].[Date].[Day Level].[Date Attribute]>= #prompt('From','date')#
    and [Time].[Hier].[Date].[Day Level].[Date Attribute]<= #prompt('To','date')#
)

That will only work if you have a date attribute in your day level.

If you don't, you can try doing what NewtonsCannon suggested by building the member unique name based on the date prompt. In his example, the date members in his cube look like:

Code Select Expand

[Sales Written].[Dates].[Dates].[Day]->:[PC].[@MEMBER].[20120101]

So the macro expression will strip the dashes from the date, which looks like 2012-01-01, and wrap it in square brackets, returning it to the data item. If you want to follow that, you need to determine exactly what the date muns look like, and figure out a way to convert the date prompt into the expected text. Once you're able to build a date member with the date prompt, then you can use the intersect expression he wrote.

[gosoccer]

I am using the Cube.
This works great Paul using the Long Name for the date as Date Attribute,
filter(
   [Time].[Hier].[Date].[Day Level]
, cast([Time].[Hier].[Date].[Day Level].[Date Attribute],date)>= '2008/01/01'
    and [Time].[Hier].[Date].[Day Level].[Date Attribute]<= '2008/05/05'
)
following error, OP-ERR-0177
      Invalid to compare values with different dataTypes.
Unfortunately, the Day Level Date Attribute is in Character (String).

So I tried the cast so I can convert the Character to Date format since I know the picker is in date format.
cast([Time].[Hier].[Date].[Day Level].[Date Attribute],date)>= #prompt('From','date')#

But getting UDA-SQL-0460 A general exception has occurred during local processing.UDA-EE-0094 The operation "greater_equal" is invalid for the following combination of data types: "date2" and "integer"RSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(808): QFException:
Any recommendations?

[CognosPaul]

Unfortunately powercubes only have strings for attributes. Powercubes don't support any type of cast function, so Cognos is attempting to do this locally. What this means for you is that Cognos will dump all of the data into a temp file, then cast the days into date format, then filter on it. And even then it doesn't always work well.

There are a few things to try. Try this first:
Set up the short name to yyyymmdd (effectively a number).
Then you can filter the day level with:
filter(
    [Time].[Hier].[Date].[Day Level]
  , [Time].[Hier].[Date].[Day Level].[Short Name] >= #timestampMask(prompt('StartDate','date')+' 00:00:00.000+00:00','yyyymmdd')#
    and [Time].[Hier].[Date].[Day Level].[Short Name] <= #timestampMask(prompt('EndtDate','date')+' 00:00:00.000+00:00','yyyymmdd')#
)

If that doesn't work let me know and I'll give you the other way (hint, it's a modification of the way NewtonsCannon suggested).