COGNOiSe.com - The IBM Cognos Community

Hello!

Here I am again with a rare need on report studio. I have a report over a PowerCube and need to achieve the following crosstable:


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  LESSER
Worker2    11    11     7     9  GREATER
Worker3    16    15    13    14  GREATER


The last column named NEW doesn't exist yet, I need to create it. It have to compare the value of the last 2 years (columns) and show a text value.

Any idea of how can I create that column?

Thanks.

Please see, if this is what you are looking for :

https://www.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.1.1.doc/t_rs_insert_query_calc.html (https://www.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.1.1.doc/t_rs_insert_query_calc.html)

Quote from: qvixote on 02 Aug 2018 03:12:17 PM
Hello!

Here I am again with a rare need on report studio. I have a report over a PowerCube and need to achieve the following crosstable:

Code Select Expand


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  LESSER
Worker2    11    11     7     9  GREATER
Worker3    16    15    13    14  GREATER


The last column named NEW doesn't exist yet, I need to create it. It have to compare the value of the last 2 years (columns) and show a text value.

Any idea of how can I create that column?

Thanks.

Here is a possible approach to pursue (I'm a little rusty on dimensional but I've done this in version 11 and it should work the same for you in 10):

Create a query calculation, nested alongside the years as you've shown, to work out the difference between the last two years:


lastChild ( [Time] ) - lag ( lastChild ( [Time] ), 1 )


This should show:


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  -2
Worker2    11    11     7     9   2
Worker3    16    15    13    14   1


Next, unlock the report layout and put a layout calculation next to the <#1234#> in your crosstab intersection. Put this expression in the layout calculation:


case
   when CellValue () < 0 then 'LESSER'
   when CellValue () > 0 then 'GREATER'
else 'SAME'
end


This will show the label on every intersection, not just in your new column. Use an advanced conditional style for the layout calculation with a condition of ColumnNumber() < 5 and set the box type property to none for this condition. Create another conditional style for the value <#1234#> with box type none for the condition ColumnNumber() = 5.

The end result should show the value in the four columns corresponding to the years 2015 through 2018 but will not show the value in your change column.

Thanks for your answer, Sdf.

In the link you shared is described the general procedure to add query calculations. I know how to add query calculations, and I know thats what I need to do, but the thing is I don't know how to make the query calculation to do exactly what I've described. I have used query calculations to perform many calculations, but not anything like that. I don't know how to compare the value of only the las 2 columns (years) to indicate if the current year value is lesser or greater than the -1 year.

Thanks again for your reply.

i see you have a duplicate post

Quote from: sdf on 03 Aug 2018 09:42:13 AM
i see you have a duplicate post

Not any more. I merged the two posts into this combined one.

MF.

Quote from: qvixote on 03 Aug 2018 08:45:07 AM
Thanks for your answer, Sdf.

In the link you shared is described the general procedure to add query calculations. I know how to add query calculations, and I know thats what I need to do, but the thing is I don't know how to make the query calculation to do exactly what I've described. I have used query calculations to perform many calculations, but not anything like that. I don't know how to compare the value of only the las 2 columns (years) to indicate if the current year value is lesser or greater than the -1 year.

Thanks again for your reply.

My reply was in the other thread that is now merged with this one. Hopefully this helps address your problem.

Sorry, I was with connection problems and that surely have duplicated the post. Thanks, MFGF, for resolving it.

Thanks, Lynn, for your answer, it is what I was looking for, but I'm still a step from getting it solved.

First, your recomendation (lastChild ( [Time] ) - lag ( lastChild ( [Time] ), 1 )) gives me an error, but just to try I inserted directly this:

[2018] - [2017]

and get the same result (obviously, only work for 2018), but I forgot to mention an element in the table. I have previously made the following query calculation:

average ([ATENCIONES_TOTAL])

Being [ATENCIONES_TOTAL] the measure in the cross table. The previous calculation is in the rows, so I get the average of each column, like this:


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  -2
Worker2    11    11     7     9  2
Worker3    16    15    13    14  1
AVG        14  11,3  10,6    11  11


The NEW column only repeat the value of 2018, but I want it to show the difference between average of 2018 and average of 2017.