1

- 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?

enter image description here

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

Kre
  • 69
  • 12
  • There are many ways to handle the presentation Maybe you want a conditional style based on a SQL function that determines if the metric based on the attributes should show? Can you tell me a little more - is this relational metadata (vs DMR, etc)? – VAI Jason Dec 05 '19 at 17:52
  • Hello VAI Jason, its an relational metadata. My query contains 3 data items and one filter. The data items are categorie, month and the value. I have a filter as prompt for [G_BIS_PART] that contains values like '201911' which filters the last eleven months. Filter : [G_BIS_PART] between ( to_char(add_months ( to_date(to_char( #prompt ('p_G_BIS_PART';'int' )#);'YYYYMM');-11);'YYYYMM')) and ( to_char(add_months ( to_date(to_char( #prompt ('p_G_BIS_PART';'int' )#);'YYYYMM');0);'YYYYMM')) – Kre Dec 06 '19 at 08:35

2 Answers2

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)

VAI Jason
  • 534
  • 4
  • 14
  • 1
    Hello Jason, first of all thank you for your solution. It works as soon as i use insert the value of the previous month into my crosstab. Is there any way i can solve this without inserting the previous month into my crosstab? As soon as i leave it out i got an error message: If the item exists in the query but is not referenced on the layout, add it to the property list. Thanks. – Kre Dec 10 '19 at 14:55
  • Hi Kre, adjusted the answer above to handle the layout questions see Additional Notes – VAI Jason Dec 10 '19 at 15:26
  • Hi Jason, i have adjusted my question above too. Thanks for your effort!! – Kre Dec 11 '19 at 08:22
  • What is a,b,c? For the example that follows I will call it category Use the function total to isolate the data i.e. total(Sales for [category]) This way you compare at the correct level – VAI Jason Dec 11 '19 at 18:19
  • @kre I think you need to either define the prior month metric in the cross tab layout and then hide it (see general > Define Contents = Yes and change the text label to be blank) or it becomes a lot trickier where you have to define the metrics with functions to isolate the values. – VAI Jason Dec 11 '19 at 22:23
  • 1
    Hi Jason, I have solved it now by hiding the prior month metric in the crosstab! Thanks for your effort. – Kre Dec 13 '19 at 07:28
0

Given its a relational data source, you could see if your DB supports the lag and lead. If it does you could use some type in SQL to obtain your datepart offset. Here is an example against MSSQL:

http://www.sqlservertutorial.net/sql-server-window-functions/sql-server-lag-function/

Daniel Wagemann
  • 741
  • 4
  • 6