Date Prompt to MemberUniqueName (DMR)

[IceTea]

Hi all,

we have the need to have a date prompt on our report page (not only on a prompt page) for the users to change the reported timeline.

We use the standard date prompt which is fine to give us a parameter in the format "2012-03-10".

The report  uses a DMR-Model (on Oracle) and it should be shown up with days or weeks or month in the columns of the crosstabs (we have three crosstabs, one for day-based reporting, on for week-based, one for month-based).

There is a time dimension in the model with different hierarchies, like "Year/Week/Day" oder "Year/Quarter/Month/Day" and so on.

Let's give an example about the week-report:

The columns show up
1.) the in the date prompt selected date/week/month as "actual week"
2.) the four weeks before
3.) the follow-up week


Each of this "blocks" is differently formated and has (maybe) different nested dimensions in the columns.

This is why i can't use the level "Week" and do a filter on the query to get all this weeks based on the prompt selection. I have to handle the different weeks with different formating and different nested dimensions... (as i wrote before).

Actual work-around is to have three crosstabs side by side and use three queries with different filters (one for actual week, one for the before-weeks, on for the follow-up-week).

But... what i WOULD REALLY LIKE to do... and i think is much more better... ist to wirk with dimensional functions like "prevmember" or "nextmember"... and so on.

But to achieve this, i have to use this **** parameter within the query to get the selected member from time dimension. And this is what i wanted to ask you... how can i achieve this goal?

To remember:
Date Prompt format is: 2012-03-12

The format of the MUN's from time dimension is:
Day: [Vertriebsmonitor Neu].[Zeit].[Zeit (Tag/Woche/Jahr)].[Tag]->[all].[2012].[201209].[2012-03-01 00:00:00.000]
Week: [Vertriebsmonitor Neu].[Zeit].[Zeit (Tag/Woche/Jahr)].[Woche]->[all].[2012].[201209]

What's most interesting for me is "Week"...

Ideas anyone?

Thanks in advance!

[rockytopmark]

You can get the Week MUN by using the Ancestor() or Parent() function on the parameter. 

To make this work, however, you would probably have to use a Value Prompt control, which must be sourced from the Time dimension's Day level, so that you are working with the MUN and not the attributes...

or alternatively, you would have to convert the value from the standard date prompt into a MUN, which is probably not as easy.

[IceTea]

Hi,

the problem is indeed to get the MUN of one of the Date-Hierarchy-Elements, preferably the week. To have a value prompt with the week members to chose is not an option since business says that this is not an option ;)

So... how to convert the DateValue-prompt-Parameter to a weekly MUN???

[rockytopmark]

To get the Week member of the entered Date, try something like:

filter([date dim].[hy].[week-Level], [date dim].[hy].[day-Level].[date-attr] = ?yourDatePrompt?)

[IceTea]

Thanks for the hint. I played around some hours before and this is wat i got:

item(filter([Vertriebsmonitor Neu].[Zeit].[Zeit (Tag/Woche/Jahr)].[Woche]; [Vertriebsmonitor Neu].[Zeit].[Zeit (Tag/Woche/Jahr)].[Tag].[DATUM_ISO_BEZ]= ?p_Datum?);0)

-> Doesn't work because of: "OP-ERR-0247 Unsupported filtering by property because level of property filter expression is below the level of the set being filtered"

But this here works fine, even it's no just tiny & smooth as the code before:


item(
filter (members ([Vertriebsmonitor Neu].[Zeit].[Zeit (Woche/Jahr)].[Woche]); rolevalue('_businessKey';currentMember ([Vertriebsmonitor Neu].[Zeit].[Zeit (Woche/Jahr)]))=

          rolevalue('_businessKey';
              parent(
                 item(
                   
filter(members([Vertriebsmonitor Neu].[Zeit].[Zeit (Tag/Woche/Jahr)].[Tag]);rolevalue('_businessKey';                              currentMember([Vertriebsmonitor Neu].[Zeit].[Zeit (Tag/Woche/Jahr)])) = ?p_Datum?);0)
                      )
               )
);0)