We work with Cognos 8.4.0.
My business user have a report (based on a PowerCube) in which they select a month and the type of period they want to see (selected month, calendar year until the selected month or the last 12 months until the selected month). The report has a prompt page and works just fine.
However, most business users just want to see the last month by default and then have some prompt controls to alter the default selection if needed. I tried to accomplish a default selection on the last month using some JavaScript (http://www-01.ibm.com/support/docview.wss?uid=swg21370539), but whilst this works in Report Studio it doesn't when running the report from Cognos Connection.
So, I created a new dataitem ([SelectionMonth]) in my query with a prompt macro:
#prompt('month';'memberuniquename';'closingPeriod([PFZ].[Period].[Period].[Month])')#
Next, I altered my existing filter like this:
(?PeriodType? = 2 and [PFZ].[Period].[Period].[Year] = parent(parent([SelectionMonth])) and
[PFZ].[Period].[Period].[Month] in (periodsToDate([PFZ].[Period].[Period].[Year]; [SelectionMonth])))
or
(?PeriodType? = 3 and [PFZ].[Period].[Period].[Month] in (lastPeriods(12; [SelectionMonth])))
or
([PFZ].[Period].[Period].[Month] = [SelectionMonth])
The report runs fine and automatically shows the last month. I can also adjust the month using the prompt control on the report.
However, if I select PeriodType 2 the report fails:
PCA-ERR-0062
The "parent" node is not expected to be a "qs:member".
PeriodType 3 throws a similar error:
PCA-ERR-0062
The "closingPeriod" node is not expected to be a "qs:member".
It seems as if the MDX functions in the filter fail when using the [SelectionMonth] dataitem.
Is there a way to solve this or a different approach to fulfill my business requirement?
Your help will be much appreciated.
I eventually fixed this with another piece of JavaScript that *does* work.