Need HELP with MDX function periodtoDate???

[torre2011]

I have a report where I am attempting to build a report that shows YTD based on a prompt for a month.  Here is what I have:

1) Business Unit Level
2) Data Item # 1 labeled Current Month - item(tail(filter(members([Crown MA National Cube 10_1].[Time Dim].[Calendar Time Periods].[Calendar Month]), tuple([Crown MA National Cube 10_1].[Measures].[Base Measures].[Depletions], currentMember([Crown MA National Cube 10_1].[Time Dim].[Calendar Time Periods])) >0), 1), 0)

3) Data Item # 2 labeled YTD Dates - periodsToDate([Crown MA National Cube 10_1].[Time Dim].[Calendar Time Periods].[Calendar Year], [Current Month])

4) Data Item # 3 labeled YTD Total - total([Crown MA National Cube 10_1].[Measures].[Base Measures].[Depletions] within set [YTD Dates])

5) A measure labeled Depletions

6) An optional filter - [Current Month]=?pMonth?

I placed the Business Unit, Depletion and YTD Total on a crosstab...then ran the report.  Since the filter was optional, the report came back with accurate totals, but as soon as I change the Current Month by means of a prompt, the periodtoDate data item only shows that current month rather than the range!  :(

So I am hoping that someone here has dealt with this before, and can provide some needed assistance.

Please let me know if you require any further information to help me.

Thanks!

[CognosPaul]

And now you've learned one of the many many reasons that I disallow the use of detail filters in reports based on cubes.

The detail filter is overriding all edges that use the time dimension. It also sliced the query by that month if you're attempting to show a total.

First step, get rid of the detail filter. You don't need it.

The way you're finding the current month is a bit odd. What type of cubes are you using? If you are using PowerCubes, and you have a current month hierarchy, you can do something like:
linkMember(item([Crown MA National Cube 10_1].[Time Dim].[Current Month].[Current Month1],0),[Crown MA National Cube 10_1].[Time Dim].[Calendar Time Periods].[Calendar Month])

If you do need to use the filter, then you can get rid of the currentMember and tuple functions as filter will automatically evaluate the selected value (Depletions) with each member in the selected set (Calendar Month). Also, tail will return 1 member by default, so you can get rid of the ,1 at the end of it. Note that using linkMember is significantly faster.

Now you want to replace that member with the member selected by the user in the prompt. Or, looking at it in the other direction, you want to allow the user to select a month, but return that function if he doesn't select a month. This is a perfect case for the use of a prompt macro.

All macros begin and end with #. The prompt has six of parameters

#
prompt(
    Prompt Name
  , Data Type
  , Default
  , PreText
  , Source
  , Post Text
)
#

In this case you just need the first three. Search the forum for explanations on the others.

#prompt('pMonth','memberuniquename','item(tail(filter()),0)')#

This will allow the user to select a specific month, but return the current month if nothing is selected.

[torre2011]

This is great stuff PaulM!!!  ;D

I will definitely look into using the linkMember.  The reason I went the route I did to capture the current month, was that I needed it based on the available values in the fact table..so i needed the current month in this case be 201306...this is where I used the tuple function to provide that...  But...i did not know about linkMember at the time...and I do have a Last Closed Month Hierarchy...so this should work!!

I also appreciate your insight into the use of a detail filter when using the cube as a source.  My requirements is to provide a report that will show the starting month period based on the users selection, the parallel period from the previous year as a comparison ( i was unable to use parallelPeriod for some reason, so I used lag instead  ;), and then the variance between the two months.  Then I need to nest the YTD for both the starting month and previous year month... this is where I was running into issues.

So this is where I was testing the filter.  When the user first runs the report I wanted the starting month to be equal to the last month available in the current year that has values in the measure.  Then allow the user to change that starting period through a prompt.  That is why you see my use of periodstoDate with the data item that is affected by the prompt.

So, is it possible to use the prompt macro in place of this so that we still see the default starting period but also get the ability to change it???  So something like this: periodsToDate([Crown MA National Cube 10_1].[Time Dim].[Calendar Time Periods].[Calendar Year], #prompt('pMonth','memberuniquename','item(tail(filter()),0)')#)

I know the above expression is not working...so I am assuming I have something wrong in the syntax??

I am grateful for those who are willing to share their experience and knowledge...it is what these forums are all about!! ;D

[CognosPaul]

In my post I was just being lazy so I didn't put the entire expression in, you shouldn't leave the filter empty like filter()

Let's assume that your expression to find the current month is in a data item called [Current Month].

Use the expression:
periodsToDate([Crown MA National Cube 10_1].[Time Dim].[Calendar Time Periods].[Calendar Year], #prompt('pMonth','memberuniquename','[Current Month]')#)

As long as the result of the macro is a member or member expression it should work as expected.

[torre2011]

So after imbedding the prompt macro, I am still running into an issue with executing the prompt??

I removed the detail filter and when I run the report and view the tabular report from the query, i get the prompt...i then enter 201302...when selecting OK, nothing happens..it will not run the report...if I just hit Cancel, then the report is run using the default member.

I tried this with the HTML report, and still it seems that the prompt macro is not working as I think it should...am i missing something here? 

Again...thanks for all your assistance!

[torre2011]

Forget my last reply....it is WORKING!  And magnificently I might add  ;D

Thanks PaulM for all your assistance!!