Hi!
I am working with a TM1 datasource in Cognos report studio 10.2
I have a prompt where user can select year. These year member looks like:
[cube].[case].[case]->:[TM].[case].[case].[@MEMBER].[AAP2016]
[cube].[case].[case]->:[TM].[case].[case].[@MEMBER].[AAP2015]
[cube].[case].[case]->:[TM].[case].[case].[@MEMBER].[AAP2014]
I tried a data item:
#'[wwsign].[case].[case]->:[TM].[case].[case].[@MEMBER].['+
CASE prompt('p_CurrentActual','memberuniquename')
WHEN 'AAP2011' THEN 'AAP2010'
WHEN 'AAP2012' THEN 'AAP2011'
WHEN 'AAP2013' THEN 'AAP2012'
WHEN 'AAP2014' THEN 'AAP2013'
WHEN 'AAP2015' THEN 'AAP2014'
WHEN 'AAP2016' THEN 'AAP2015'
END
+']'
#
but its not picking up the desired dimension member.
The only expression which is working is:
#
substitute ('AAP2016','AAP2015',prompt ('p_CurrentActual','memberuniquename'))
#
but I want to add multiple years.
Please help,
Hi
It looks like you are providing a prompt to the user that shows a year and then wanting to return data for the prior year. If that is the case, can you not just use the Lag function to return the previous member, something like this:
lag (#prompt('p_CurrentActual','memberUniqueName')#,1)
So if the prompt chosen is [cube].[case].[case]->:[TM].[case].[case].[@MEMBER].[AAP2014] then the above function would actually return [cube].[case].[case]->:[TM].[case].[case].[@MEMBER].[AAP2013]. I am of course making some guesses about your cube structure but I just tested a similar scenario on one of my own packages and the above worked.
Hope that helps.
Dax