Issue with date prompt and slicer expression

[dsg1]

I have a crosstab report that have Cognos date prompt. Data will be filtered between the date selected as From_Date = first_of_month(add_months(?prompt?,-12)) and To_Date = ?prompt?. 

The slicer item will be like :

filter([FINANCIAL].[CALENDAR].[CALENDAR].[DATE],
[FINANCIAL].[CALENDAR].[CALENDAR].[DATE].[Calendar Date] >= _add_months(#prompt('P_From_Date','date')#,-12) and
[FINANCIAL].[CALENDAR].[CALENDAR].[DATE].[Calendar Date]  <=  #prompt('P_To_Date','date')#) 

However, _add_months here is not working as this is not a dimensional function and throwing error. Do we have any other functions that can be used to achieve this?

Please help.

Thanks

[Lynn]

When your work with dimensional sources it is important to understand that elements in your date dimension are not dates. Everything is identified using a MUN. You need to look at the MUN to understand how it is structured. Then you need to create an expression to get the desired MUNs based on the prompts supplied by the user.

There are many posts already on how to construct a MUN from the current date. Do a search if the one example I provided below isn't what you need.

http://www.cognoise.com/index.php/topic,28946.msg93249.html#msg93249

[Ricardo Julio "Ricky" Villa]

This is a set expression I use to find the periods between 2 dates, which I then put in a slicer:

intersect(lastPeriods(-9999, [Saxon Sales Performance Cube].[All Dates].[All Dates].[Month]->?p1?),
lastPeriods(9999,[Saxon Sales Performance Cube].[All Dates].[All Dates].[Month] ->?p2?))

[dsg1]

I am trying but struggling to generate the MUN for the last year date from date prompt as I need to do the add months and olap is not supporting any relational functions in the data item. The report has only 1 date prompt.

The date prompt is generated in the format 'YYYY-MM-DD'

The MUN for date is like
[FINANCIAL].[CALENDAR].[CALENDAR].[DATE]->:[RO].[CALENDAR].[CALENDAR].[All].[2016].[20161].[201601].[20160101].[20160101] 

[MFGF]

I am trying but struggling to generate the MUN for the last year date from date prompt as I need to do the add months and olap is not supporting any relational functions in the data item. The report has only 1 date prompt.

Hi,

The issue here is you're thinking about this in relational terms. In your cube you don't have rows of data with dates in them - you have members and measures. The members are usually held in hierarchies in a number of dimensions, and you can use this to your advantage here.

You're using a filter function in your slicer here, but you don't need to. Instead you need to deliver a set of members corresponding to the last 12 months.

Let's say you are prompting for a date. In fact, you're not - you're prompting for a member. It looks like this will be a day member in your case. Now you have that member, you can use dimensional functions to grab the set of members you require.

Let's say your prompted-for member is [03-03-2016]. This belongs to a month member higher up in the hierarchy, which in turn belongs to a year member. In this case you want to return a set of members starting with [01-03-2015] and ending with [03-03-2016]

You can get rid of the macro and use the expression [your Day level] -> ?prompt? to prompt for a day member

You can use the periodsToDate() function to bring back all day members from the beginning of the month to the prompted-for member

periodsToDate([your Month level], [your prompted-for member])

You also need members for the prior 12 months.

The function lastPeriods() can be used for this, in league with ancestor(), prevMember() and descendants()

ancestor([your prompted-for member],[your month level]) will bring back the month member your day member belongs to (ie [Mar-2016] in this case)
prevMember(<the expression above>) will bring back the month member prior to this (ie [Feb-2016] in this case)
lastPeriods(12,<the prevMember expression above>) will bring back the 12 month members starting with [Mar-2015] and ending with [Feb-2016]
descendants(<the lastPeriods expression above>,[your Day level]) will bring back the day members that belong to the 12 month members

So now we just need to UNION these members to get a set that spans 12 months plus the days up to the prompted-for day

union(descendants(lastPeriods(12,prevMember(ancestor([your day level] -> ?prompt?,[your month level])),[your Day level]),periodsToDate([your Month level], [your Day level] -> ?prompt?))

This would appear to be the slicer expression you need. Go to your query, delete the existing slicer, drag in a Slicer Member Set, and use the above expression (using your real day and month levels from your hierarchy)

In this case the resultant set would be a set of day members beginning with [01-03-2015] and ending with [03-03-2016]

Cheers!

MF.

[dsg1]

Thank you for the detail explanation. Will try to implement on the same lines.

[dsg1]

@MFGF and everybody for your suggestions...Thank you sir...The detail solution from MFGF is working great.

Solution:

1. First I created a filter expression as [Day Filter] :

filter([New Cube].[Test Date Dim].[New Hierarchy].[Day],[New Cube].[Test Date Dim].[New Hierarchy].[Day].[Prompt Date] =#prompt('Parameter1','date')#)

2. Created a member for the above query item as [Item Day Level]:

item([Day Filter],0)

3. Next created the slicer as :

union (descendants (lastPeriods (12,prevMember (ancestor ([Item Day Level],[New Cube].[Test Date Dim].[New Hierarchy].[Month]))),[New Cube].[Test Date Dim].[New Hierarchy].[Day]),
periodsToDate ([New Cube].[Test Date Dim].[New Hierarchy].[Month],[Item Day Level])
)