More Crosstab Sorting issues

[jcrouch]

Hi,

Im getting much better at sorting crosstabs, but just when i think I have it figured out I get stummped by something that I think should work...

I have a crosstab that has sales categories and product lines as rows, years as columns and sales and share as measures. This has a relational data source.

I have a custom sort on the product line row, and that works great. I have a data item that uses a case statement to flag certain product lines with a 0, everything else gets a 1. Then in the custom sort I have it sortes ascending by that 0,1 and then descending by sales. This makes the 'Others' group always be at the bottom, and the ones above are sorted by sales.

Now I need to sort the sales categories by the sales on one of the product lines, say Grapes. I added another data item:

CASE WHEN ([Product line] In('Grapes'))
  THEN ([Orders].[Sales])
  ELSE (0)
END

I then added a custom sort to the sales categories row, sorting by this new data item descending. It is not sorting. No matter what I do the sales categories are always sorted in alpha order.

I have attached a simple example of my report. On It I need the categories (AA, BB, CC) sorted by the sales of Grapes. So the sorting would be BB, AA, CC if it worked properly.

I did find an article stating that Cognos would not sort based on the 'edge' of a crosstab? I wasnt sure what that meant or if it appled to my situation...

Any thoughts?

Thanks,

livens

[jcrouch]

Well, I found a round about way of doing it. But Im not sure why this way works and my original way doesnt.

I added a new query that just showed the sales categories and filtered to only show the Sales for Grapes in 2011. Sorted that descending. Then I added a data item with the rank function in it, ranking the sales categories from 1 to whatever.

Then I joined the 2 queries and added the rank data item in with the original query. Advanced sorting the sales categories row on the new rank field worked perfectly.

If there is a way to do this all in 1 query I would love to hear it, I like keeping things as simple as possible.

Thanks,

livens