Issue in Cognos Report studio.
Crosstab Data looks like :
Month1 Month2 Month3 Month(CurrMTD)
------------------------------------------------------
Measure PLO 20 30 15 20
Other Measure 1 x x x x
Other Measure 2 x x x x
Other Measure 3 x x x x
I have a date prompt based on which i have a slicer to filter previous Months and Current MTD.
The value for Measure PLO is considered to be PLO value for last day of each month (for previous months) and PLO value for the date selected (current MTD) in date prompt.
Measure PLO = PLO value for each month end date + PLO for date selected
PLO for previous months are coming fine and calculated at db level.
But PLO for Current MTD is giving issues. Data is summing up for all the months when I am doing :
aggregate([PLO] within set [End Date Selected])
as I have 2 members from the same date dimention. 1. Current Month Name to display and 2. [End Date Selected] for calculation.
Please help
dsg
Quote from: dsg1 on 17 May 2016 06:49:16 AM
Issue in Cognos Report studio.
Crosstab Data looks like :
Month1 Month2 Month3 Month(CurrMTD)
------------------------------------------------------
Measure PLO 20 30 15 20
Other Measure 1 x x x x
Other Measure 2 x x x x
Other Measure 3 x x x x
I have a date prompt based on which i have a slicer to filter previous Months and Current MTD.
The value for Measure PLO is considered to be PLO value for last day of each month (for previous months) and PLO value for the date selected (current MTD) in date prompt.
Measure PLO = PLO value for each month end date + PLO for date selected
PLO for previous months are coming fine and calculated at db level.
But PLO for Current MTD is giving issues. Data is summing up for all the months when I am doing :
aggregate([PLO] within set [End Date Selected])
as I have 2 members from the same date dimention. 1. Current Month Name to display and 2. [End Date Selected] for calculation.
Please help
dsg
Have you tried tuple([PLO], [End Date Selected])
This assumes [End Date Selected] is a member returned from your prompt...
MF.
Hi,
I did the following:
created a data item [Item 1] = tuple([PLO], [End Date Selected])
and then
PLO for currentMTD = aggregate([Item 1] within set [Store],[Actuals]) --I did not mention the other members [Store] & [Actuals] in the previous thread.
This is giving the right PLO data for current MTD but again rolling up for the previous months as well and showing same aggregated data for all the months.
Can we bring in the other members within the tuple function to work?
I tried tuple([PLO], set( [End Date Selected],[Store],[Actuals])) but not working.
Thanks
dsg
Issue got resolved. Creating another Time Dimension and displaying the Month in the crosstab from this new dim.