I have a cube-based report in Cognos 8.4.1 that has the following requirements:
Months of the Year, and a Yearly Total, for Measure1. Measure2 and Measure3 need to be the full year - that's working fine.
However, Measure 1 needs to be only through the selected month based on a prompt, e.g. We need to show all 12 months, but I want the measure to be either the amount (if less than or equal to the selected period) or 0 (if not).
I'm almost but not quite there.
I can show the months of the year, and show the selected months, but can't quite see how to build a calculation or the like to have the measure be either the Amount or 0.
A slicer would fit the bill, but the slicer messes up the Measure2 and Measure3 requirements.
Try using a tuple, tuple([YTD],[Measure1]) , in place of Measure1
Hmm.
The YTD data item I came up with is: periodsToDate([cubename].[Time].[Time].[FISCAL_YEAR],[SelectedPeriod])
doing tuple([YTDSet], amount) gives me this: Invalid parameter '[months_selected]' provided for 'tuple' at position '1' for 'dataItem="Data Item2"'.
This parameter is 'memberSet' type but is expected to be one of the following: 'member'.
(Okay, sure, it's a set expression, not a member).
I have these items to work with:
levels: Fiscal Year, Fiscal Period.
Selected Year defined as [FISCAL_YEAR] -> ?Fiscal Year?
Selected Period defined as [FISCAL_PERIOD] -> ?Period?
Months for Selected Year: children([Selected Year])
YTDSet: periodsToDate([cubename].[Time].[Time].[FISCAL_YEAR],[SelectedPeriod])
(set) Months_to_end_of_year: except([Months for Selected Year], [YTDSet])
(set) months_selected: except([Months for Selected Year], [months_to_end_of_year])
I'm doing something wrong, but I can't see what it is yet.
If I nest months_selected under Months for Year, I only get the months selected... and I need to show all 12 months.
Okay, I was under the impression you had a YTD member coming from the cube.
Try doing total([Measure1] within set periodsToDate([cubename].[Time].[Time].[FISCAL_YEAR],[SelectedPeriod]))
I figured it out! (Inspiration comes at the oddest of times sometimes).
So. The layout is now this:
Columns
YTDSet (data item) Months-to-end-of-year (data item) Expenses Total Budget Year
ROWS (mA or mB by account) - calculated measure dummy measure - value 0
account1 measureA 0 measureA for year-end measureA for Budget Year-end
account2 measureB 0 YTD Exp Budget for the full year
The keys were - put the YTD set (based on a Selected Period) and the Months-to-end-of-year on the same level, and add a dummy measure with value 0.
That way, there's always the correct measure for the months-to-end-of-year, which, in this case, is 0. The expenses total correctly, and I didn't have to do a slicer which would have made the budgets not work right.
Thanks for the suggestions Paul but I figured it would be helpful to post my solution also. I just needed to reshuffle the building blocks I already had - and add a dummy measure.