Sorting the categories axis (x-axis) of a Cognos report doesn't seem to work

[dougp]

I posted this on StackOverflow, but I know the overlap between this site and that is not 100%...

Is it possible to properly sort the categories axis in a Cognos line chart?

Using IBM Cognos Analytics 11.2.4.2, I have tried using the line chart from each of the four visualization libraries. I'm having trouble getting the sorting correct.

My need is for cumulative values by month (human-readable month labels - mmm yyyy - on the X-axis), grouped (color/series), listed in date order with the values accumulating in month order.

As a starting point, I have created an example report spec using the GO Sales (query) package showing the line chart using all four visualization libraries, with year-month values (yyyymm) on the X-axis rather than the friendly values I want. Query1 gets my data, then Query2 (downstream of Query1) is used for the charts. It is needed so I could include running-total() without confusing Cognos.

So, starting with the report spec I provided...

(Working in Page preview to see immediate results.)

• In the 11.1 Visualizations chart
   
  
     
  • Cut [YearMo] from the x-axis.
  • From Insertable Object | Data Items, drag [Query2].[Mon Year] onto x-axis.
       
    
         
    • Notice that the categories are sorted alphabetically -- Apr 2010, Apr 2012, Aug 2010, Aug 2012, etc.
       
  • Select [Mon Year] in the x-axis slot and click on the Sort tool.
       
    
         
    • Notice that the options are Ascending, Descending, and Don't sort
           
      
             
      • fail
           
       
• In the 11.0 Visualizations chart
   
  
     
  • Cut [YearMo] from the x-axis.
  • From Insertable Object | Data Items, drag [Query2].[Mon Year] onto x-axis.
       
    
         
    • Notice that the categories are sorted alphabetically -- Apr 2010, Apr 2012, Aug 2010, Aug 2012, etc.
       
  • Select [Mon Year] in the x-axis slot and click on Sort | Edit layout sorting
  • Drag [YearMo] to the Sort list and click OK.
       
    
         
    • Notice that while the months labels are now sorted in the correct order, the cumulative values are still computed by month names, alphabetically. (Apr 2010 is the smallest value, followed by Apr 2012, etc.)
           
      
             
      • fail
           
     

The Legacy Visualizations chart and Charts chart behave the same as the 11.0 Visualizations chart.

I have also tried setting the Pre-sort property to Sort ascending for [YearMo].

What have I done wrong here? How can I make Cognos sort the labels and values correctly?

It seems completely bonkers that a high-end reporting product like Cognos can't do basic charting stuff. And I'm pretty sure this all worked properly in Cognos 10. I honestly hope I missed something.

Solutions are great.  But if you are a Cognos report development expert and also can't solve this problem, I would love to also see that in the comments.

[cognostechie]

Hey Doug

I had the same problem with the line chart , version 11.1.7. This is what I did:

1> Create a calculated data item in the query which is used by the line graph:

     cast( extract ( year,[date] ), varchar(4) ) + cast( extract ( month,[date] ), varchar(2) )

2> Use this data item in the sort property and use your other data item to display the months in the X axis.

I still had a problem where in drilling down it wasn't maintaining proper sorting. If you don't have drill downs then this should work just fine.

[dougp]

Which visualization library were you using?

[cognostechie]

11.1

I just checked. When the data is hierarchical then it allows you to sort by one data item while using another data item to display but when the data is relational then it allows only Ascending or Descending option on the same data item that you use in X-Axis.

So basically you have to create a calculated data item and use the numbers instead of Text. (Ex: For Month - Number = 01, Text = Jan)  When sorted alphabetically, it will go from left to right so Apr 2021 will be followed by Apr 2022 and then Aug 2021. When you use numbers it will be 202104 followed by 202105 and so on.

[dougp]

When the data is hierarchical

Do you mean dimensional (cube) vs. relational?

How would you change my example report to make it work?

[cognostechie]

Do you mean dimensional (cube) vs. relational?

Yes

How would you change my example report to make it work?

Exactly like I said earlier:

There must be a Date in the package which is associated to the YYYYM. Create a data item with this expression:

cast( extract ( year,[date] ), varchar(4) ) + cast( extract ( month,[date] ), varchar(2) )

Use this data item in the X-axis and sort ascending.

[cognostechie]

I mean the way you are doing running total is the problem. It is calculating cumulative totals by the MMYYYY so the running total should be based on the data item I mentioned above.

[dougp]

Are you saying you actually tried that and got good results using the example report spec I provided?

I don't understand what you are saying about the running-total being wrong.

Code Select Expand

running-total([Query1].[Quantity] for [Country])

So I'm getting the running total per row within each Country.  I don't see a way to define the sort, so I assumed the sort is defined however the viz is sorted.  And given that the viz groups by Country, the "for" clause is probably redundant.

I did not use the "at" feature.  Cognos help says

The "at" option defines the level of aggregation and can be used only in the context of relational datasources.

with no example of what that means.  What is the "level of aggregation"?  Does that relate to sorting somehow?

So what should it be?


You recommend:

Code Select Expand

cast( extract ( year,[date] ), varchar(4) ) + cast( extract ( month,[date] ), varchar(2) )

I used:

Code Select Expand

cast([Year] * 100 + [Month (numeric)], varchar(6))

I would expect they return the same thing, except...

I developed the example from the GO Sales (query) package, so there are years and months (and dates).  But my real data doesn't have dates in this context.  It has fiscal periods which, unfortunately, look a lot like months, but are not a time element.  It is financial data with a closing period at the end of each fiscal year.  There is no "date" for MonthNumber = 13.

2> Use this data item in the sort property

I don't get it.  For the 11.1 chart, the sort property can be set to Ascending, Descending, or Don't sort.

When the data is hierarchical

So I need to change my model to DMR, then maybe the other sorting options will appear for the 11.1 visualizations?

For the other chart libraries, displaying one query item and sorting by a different one did not help.  While the x-axis values are sorted correctly, the running-count is hosed.

Did you see the behavior by starting with my report spec and doing the following?

 

   
• Cut [YearMo] from the x-axis.
• From Insertable Object | Data Items, drag [Query2].[Mon Year] onto x-axis.
     
  
       
  • Notice that the categories are sorted alphabetically -- Apr 2010, Apr 2012, Aug 2010, Aug 2012, etc.
     
• Select [Mon Year] in the x-axis slot and click on Sort | Edit layout sorting
• Drag [YearMo] to the Sort list and click OK.
     
  
       
  • Notice that while the months labels are now sorted in the correct order, the cumulative values are still computed by month names, alphabetically. (Apr 2010 is the smallest value, followed by Apr 2012, etc.)
   

[cognostechie]

I don't have Cognos samples installed so can't use your example but I used my data set and it sorts correctly

https://app.box.com/s/5h7qgb8bizkiwl92f9o7asm8m15blg1h

[dougp]

OK.  I lied.  My original question said 11.2.4.2 because that's what I was running by the time I posted here.  The problem was identified in 11.2.4.1IF1.  It turns out I should have tested before I posted.

Someone answered my question on SO.  Funny:  The graphic he provided shows the results are still wrong.  Oops.

But after I noticed the problems, I upgraded to 11.2.4.2.  Apparently it's fixed in Fix Pack 2.

The screen caps you provide match my example report spec.  That's before steps in my instructions.  What does it look like after?  What version of Cognos are you running?

[cognostechie]

What does it look like after? - Not sure what you are saying here - I provided the screenshots

What version of Cognos are you running? - 11.1.7 FP 6

[dougp]

The image on the SO answer is mostly covered by Cognos dialogs, but feel free to go look.

I can't reproduce the problem now because I have upgraded.

Consider this.  If you're listing the running-total with the x-axis labels like Apr 2010, Apr 2012, Aug 2010, Aug 2012, etc. (MMM YYYY sorted alphabetically) and you get a line chart growing from left to right.  Now, sort the x-axis by the month number (201001, 201002, 201003 => Jan 2010, Feb 2010, Mar 2010), but the values on the line chart remain associated with the original month numbers.  So now it's a jagged line with the lowest value being at Apr 2010, the next being at Apr 2012, etc.  So the x-axis labels are sorted numerically by the YYYMM values but the running-count values are sorted alphabetically by the MMM YYYY values.

Basically, start with what you provided and follow the instructions in my original post.

Maybe the problem didn't exist in 11.1.7.  Or maybe it was fixed by FP6.