Hi All -
The requirement is to have a column in the report to show data for a range of Periods in a single column.
Along with YTD and MTD columns, they need to have a column called 'Chosen Periods' which might include data for Periods 4,5 and 6 if the user chooses so from the prompts. So the data for periods 4,5 and 6 should be shown in one single column.
I can do that for a single period using :
ChosenPeriods = [General Ledger Cube].[Time].[Fiscal Calendar].[Period]->?pPeriod1?
I can also do
[General Ledger Cube].[Time].[Fiscal Calendar].[Period]->?pPeriod1? +
[General Ledger Cube].[Time].[Fiscal Calendar].[Period]->?pPeriod2?
although this is probably not a good way to do it.
Is there a way to combine data for 3/4 periods and show that in the same column? Let's say using total(currentMeasure within set) expression? If so, What will be the syntax of the 'set' expression? I tried using the 'filter' function discussed in the last post but can't get anywhere.
Also, I need to have a column that will work for the same Periods of last year and I created two Data Items for this:
LastyearChosenPeriods = parallelPeriod([General Ledger Cube].[Time].[Fiscal Calendar].[Year],1,[Chosen Periods])
and
total(currentMeasure within set [LastyearChosenPeriods])
The report validates but it does NOT show this column when the report is run. The column just does NOT show. It is in the Container which is a Crosstab.
Thanks in advance,
Steve
I could make the set and get the results :
Current Year = [General Ledger Cube].[Time].[Fiscal Calendar].[Period]->?pPeriod1?
Setof3Months = set([Current Year] , nextMember([Current Year]), nextMember(nextMember([Current Year])) )
Column to show data for 3 months = total(currentMeasure within set [Setof3Months])
ParellelPeriod = set(parallelPeriod([General Ledger Cube].[Time].[Fiscal Calendar].[Year],1,[Current Year]),parallelPeriod([General Ledger Cube].[Time].[Fiscal Calendar].[Year],1,nextMember([Current Year])),parallelPeriod([General Ledger Cube].[Time].[Fiscal Calendar].[Year],1,nextMember(nextmember([Current Year]))))
Column to show data for 3 months of previous year = total(currentMeasure within set [ParellelPeriod])
I don't like it though as this is hardcoding. Any suggestions to make it work for a range are welcome !!