Print Page - Help with conversion from Cube to DMR Expression

Title: Help with conversion from Cube to DMR Expression
Post by: tarunkrdas2013 on 10 Aug 2015 01:48:47 PM

Hi Gurus,
I have a requirement where in we are converting Cube with DMR ,so we in Report studio have a expression where from cube package we have expression as :-

   union(
   union(
   union(
   member(aggregate(currentMeasure within set (periodsToDate ([C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL DATE] , [C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL DATE]->:[PC].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'DAILY','DAILY',[C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR])
   ,
   member(aggregate(currentMeasure within set (periodsToDate ([C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL WEEK] , [C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL DATE]->:[PC].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'WTD','WTD',[C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR])
   )
   ,
   member(aggregate(currentMeasure within set (periodsToDate ([C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL PERIOD] , [C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL DATE]->:[PC].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'PTD','PTD',[C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR])
   )
   ,
   member(aggregate(currentMeasure within set (periodsToDate ([C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL YEAR] , [C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL DATE]->:[PC].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'YTD','YTD',[C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR])
   )

but try to make same report using in DMR package when i change the above string i get Parsing error as we doesn't have [C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL YEAR] , [C_Department_Flash].[FISCAL CALENDAR].[FISCAL CALENDAR].[FISCAL DATE]->:[PC].[@MEMBER] like above rather it has [DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Date]->[all].[2015].[1].[1].[2014-09-01]

can anyone suggest how to get the above expression in cube to work in DMR.

Past 2 days searching answer to the above problem

Title: Re: Help with conversion from Cube to DMR Expression
Post by: tarunkrdas2013 on 10 Aug 2015 01:53:16 PM

Error screenshot

Title: Re: Help with conversion from Cube to DMR Expression
Post by: karthik.kadambi on 10 Aug 2015 04:13:40 PM

Have you tried starting with just periodsToDate function and see if it throws up parsing error?

Title: Re: Help with conversion from Cube to DMR Expression
Post by: tarunkrdas2013 on 10 Aug 2015 04:52:04 PM

Thanks karthik,

This got solved by

union(
union(
union(
member(aggregate(currentMeasure within set (periodsToDate ([DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Date],[DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Date]->[all].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'DAILY','DAILY',[DMR].[Fiscal Calendar].[Fiscal Calendar])
,
member(aggregate(currentMeasure within set (periodsToDate ([DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Week],[DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Date]->[all].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'WTD','WTD',[DMR].[Fiscal Calendar].[Fiscal Calendar])
)
,
member(aggregate(currentMeasure within set (periodsToDate ([DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Period],[DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Date]->[all].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'PTD','PTD',[DMR].[Fiscal Calendar].[Fiscal Calendar])
)
,
member(aggregate(currentMeasure within set (periodsToDate ([DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Year],[DMR].[Fiscal Calendar].[Fiscal Calendar].[Fiscal Date]->[all].[@MEMBER].#sb(timestampmask(prompt('p_Date','Date', _add_days ($current_timestamp,-1),'','','T00:00:00Z'),'yyyymmdd'))#))),'YTD','YTD',[DMR].[Fiscal Calendar].[Fiscal Calendar])
)

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: tarunkrdas2013 on 10 Aug 2015 01:48:47 PM