Period data based on a prompt from Cube Source

[cognostechie]

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

 

[cognostechie]

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 !!