Good Morning Gurus,
We have reports in Cubes and we are building those Cube reports again using DMR and Database is Netezza, requirement is whenever user selects Daily,WTD,PTD,YTD,ALL in Report prompt page,i have attached attachment which shows the report output i expect for Daily,WTD,PTD,YTD,ALL.
I have pasted below DMR expression which i used to achieve Daily,WTD,PTD,YTD,ALL.Please suggest why i am unable to achieve the Cube output attached for WTD,PTD,YTD,ALL
also let me know if you can suggest what else expression i could write
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])
)