Hi All,
How to show empty data columns in YTD report. Example - Current month is April, so we do not have data for May-Dec. So in this case, I would like to see empty data columns from May- Dec of this year. Could you please advise how can I achieve this?
Currently I have tried this - periodsToDate ([Income Statement].[IS Period].[IS Period].[Year],[time]). But this does not show empty data columns. This is crosstab report.
Thnaks,
P
you could try placing a Case statement on your measure to say something like 'Case when (<measure> is null) then (0) else (<measure>) end'
Doing so would for zero amounts into the monthly columns. You could then format the '0' measure to show as null if necessary.
Quote from: pooja on 24 Apr 2014 02:24:09 PM
Hi All,
How to show empty data columns in YTD report. Example - Current month is April, so we do not have data for May-Dec. So in this case, I would like to see empty data columns from May- Dec of this year. Could you please advise how can I achieve this?
Currently I have tried this - periodsToDate ([Income Statement].[IS Period].[IS Period].[Year],[time]). But this does not show empty data columns. This is crosstab report.
Thnaks,
P
PeriodsToDate() returns only members up to and including the [time] member. If this is the current month you will only see members for the year up to April. If you want to see the whole year, you could use the descendants() function with the current year showing all the month members in the cube for the year.
Of course, this will only show the months if they are already defined in the cube. If not, you can't see them because they are physically not there.
Cheers!
MF.
Thank you.
MFGF - are you saying i need to use descendants() on this - periodsToDate ([Income Statement].[IS Period].[IS Period].[Year],[time]) function? Please correct me if i am wrong.
Quote from: pooja on 25 Apr 2014 10:06:17 AM
Thank you.
MFGF - are you saying i need to use descendants() on this - periodsToDate ([Income Statement].[IS Period].[IS Period].[Year],[time]) function? Please correct me if i am wrong.
No. You are wrong. :)
You use the descendants() function on the year member for the current year to get all months belonging to the year.
Cheers!
MF.