Date Prompt on multidimensionally modelled data

[ertemen]

Hi All,

I am creating a report based on a cube on 8.4.1. Cube is MSAS cube by the way. Report will include a number of transactions last week, MTD and YTD for each product. The week is going to be last week Friday so as of today(8/12/2010), the date prompt should display 3/12/2010 which works fine. I am using a prompt page and date prompt object.

The idea is to create a query calculation to retrive selected date and use lastperiods to go back 7 days to display number of transactions.

So I use a crosstab and define a columns for last week, MTD, YTD.( they are all query calculations)

F and B Sales Budget.Date dimension.fin_date hierarchy.calendar date -> ? Run Date ?

The problem is that Run Date is a parameter which is bound to Date Prompt and it returns a date. But I need it to return MUN to get the correct member. The error message says, date is returned but I am expecting MUN for this.

If I use #prompt('Run Date','memberuniqueidentifier'), I think it returns all member for the level. So thousands of date prompts will be displayed. I saw Phil W's message saying you have to perform some sort of string manipulation to get the last member from the level but I don't know where to start.

I am stuck as to how to go about resolving this.

I hope what I am trying to explain makes sense. Has anyone else come across similar problem?

Thank you

Enis