COGNOS: Compare column with previous column in a crosstab with dnamically generate columns

Question

- Hi all, i need to change the color of the values based on a condition. If the value of the previous column is lower then the actual value then the value should be blue otherwise the value should be grey. I am working with a crosstab and the columns are dynamically generated. Is it somehow possible to do this without changing to static columns?

Additional notes:

If I use a column that contains only one attribute, then it works. But if my column contains multiple attributes, then it doesn't work.

For example, I want to see the sales for different departments for the last 12 months. If the column contains more then one attribute then Cognos compares the sum off all departments of the current month with that sum of the previous month and colors all values of this month with the same color.

In the "Added Picture" you can see the outcome of my query as the output of the crosstab.

Added Picture

In my query i have created a data item "Style" that displays 'a' if the actual month is bigger and 'b' if its lower. I am using this Data Item for Conditional Style.

Thanks in advance

Answer 1

Make 2 Queries and join them

First Query is what you currently have

Second Query is a copy of Query 1 and will be used to get the prior month. The query is almost the same, except we add a data item to connect to the prior month

This is based on the value of the month +1

New data item: [Prior Month] definition = [Month] +1

Next the join (which builds the third query) is based on the key values except the part for the month from query 1 is the set to JOIN to the new data item for month +1 (let's call that prior month) from Query 2 (the relationship should be something like 1.1 to 0.1, i.e. Q1 defined as a 1.1 and Q2 defined as 0.1)

This third query data items are mostly from Query 1, except we want the metric data from Query 2 to represent the prior month

Now you can make a simple conditional style comparing the two If the value of the previous column is lower then the actual value then the value should be blue otherwise the value should be grey

To see this/and unit test Make a list and see the values for the metric and the prior month in the list side by side. Then try out the conditional style

Note: you may want to handle month 1 where the year changes, there are number of ways you can do this. However this should get you started and if you do not care about month 1, this should work.

Let me know how it goes!

Additional notes:

• Cut Prior Month Metric. In general my suggestion, avoid deleting stuff (use cut, not delete). This way you don't lose data items in the query (results in doing extra work).
• Move the one metric in the crosstab to the top left corner
• Crosstab intersection property (the part with the metrics/numbers between the rows and columns) set the conditional style.
• Next, click on the month/period. Look for the property group for data, set the properties section (this should be a list of checkboxes). Select the metric for Prior Month (now you can use this value even though it's not in the crosstab layout).

Double check that the conditional style is set. The data item for prior month sales is still in query 3 (it might be accidentally deleted depending on the order you edit things)