YTD Vs PYTD and Selected Year Vs Prior Year

[raj_aries81]

Hi All,

For a DMR Report,I have an year prompt(2010,2012,...2014). Whenever current year is selected the report should display YTD Vs PYTD and if any other year is selected the report should display Selected Year Vs Prior Year.

whenever I try to use the below Case Statement

Case when Caption([Sel Year])=ClosingPeriod([Year Level]) then [Prior YTD] else lag([sel year],1) end
I get
The item '__ns_0' is not accessible

I cannot use an if clause instead of Case statement because [Year Level] and [Prior YTD] are from different hierarchies.

[raj_aries81]

Hi All,

Are there any restrictions on using Case Statement in DMR calculation

For eg:

I have a data item [selected year] and it has [Year]->?Year?

Case when ?year?=closingPeriod([Level]) then [Prior YTD] else lag([selected year],1) end

Regards
Raj

[raj_aries81]

Hi All,

Here is a requirement -

For a DMR Report,I have an year prompt(2010,2012,...2014). Whenever current year is selected the report should display YTD Vs PYTD and if any other year is selected the report should display Selected Year Vs Prior Year.

Please note that there are three different hierarchies - YTD, Prior YTD and a time dimension that has all the years,qtrs and months

Solution

Since there are three different hierarchies YTD, Prior YTD and a time dimension with years,qtrs and months. Now, when current year is selected report should use YTD and PYTD Hierarchies and for any other selection, Years should come from Time Dimension.

I have created a dataitem called [PYTD Months] with the below calculation -

periodstoDate([Prod Level Cube].[Report Period].[Report Period].[Year],linkMember(closingPeriod([Prod Level Cube].[Report Period].[Prior YTD].[Month]),[Prod Level Cube].[Report Period].[Report Period].[Month]))

and then I have used [PYTD Months] dataitem in a If clause as below -

if (caption([Sel_Year])=caption(closingPeriod([Prod Level Cube].[Report Period].[Report Period].[Year]))) then (total(currentMeasure within set [PYTD Months])) else (lag([Sel_Year],1))

where [Sel_Year] has [Year]->?Param_year? calculation.

Please let me know if there is any better approach to achieve this.

Thanks
Raj.