Rearranging Column Order in Report Studio - Cognos 8

[Slicemahn]

Hi,

I have a couple queries that I use to produce a customer tenure report. In Report Studio, I use a cross tab that would show calls against aging. My problem is that I have columns headings such as: above 5 Years, 0-3 months, BLANK,1 YEAR, 2 YEARS, 4-6 MONTHS, 5 YEARS, 4 YEARS, 7-9 MONTHS, 3 YEARS. These column headings within themselves cannot be arranged in a alphabetical format. Are any suggeistons on how I may achieve arranging my columns in chronolgoical order?

Many thanks
Slice

 

[blom0344]

Define an additional dataitem like:

CASE WHEN
[AGE_VARIABLE] = 'BLANK' THEN
'01.BLANK' 
WHEN
[AGE_VARIABLE] = '0-3 months' THEN
'02.0-3 months'
...
...
ELSE NULL END

Use this data item and you will get the proper sort you want.
If you want the original names for [AGE_VARIABLE] then
simply produce an index like:

CASE WHEN
[AGE_VARIABLE] = 'BLANK' THEN
1
WHEN
[AGE_VARIABLE] = '0-3 months' THEN
2
...
...
ELSE NULL END

and use this as a sorter for the crosstab axis..

Ties Blom

 

[Slicemahn]

Thanks blom. Forgive my additional question(s). I am new to the Cognos environment. The code example is written where exactly? With respect to your code, would I need to fully describe the expression as in:

Topics].[Acct Since Range].[Acct Range Desc] where Acct Range Desc (Account Range Description) is my age variable? Once this Case is executed I would need to perform an ORDER BY ...?

Let me know your thoughts.
Thanks
Slice

 

[blom0344]

1.Choose the query that is used for the crosstab.
2.From your toolbox drag a new dataitem into the querylist.
3.Cognos will open up a new GUI where you can define the expression.
4.Build the expression by using a CASE WHEN ELSE END construction.
5.Rename the new expression (which has a default name like Dataitem1) to a meaningful one


The CASE expression would something like:

CASE WHEN
[Topics].[Acct Since Range].[Acct Range Desc] = 'BLANK' THEN
'01.BLANK' 
WHEN
[Topics].[Acct Since Range].[Acct Range Desc] = '0-3 months' THEN
'02.0-3 months'
...
...
ELSE NULL END

If you use the new dataitem in the crosstab, then the sorting will probably based on the 01, 02 prefix automatically. Otherwise you need to apply a sort from the menu.


Ties Blom

 

[Slicemahn]

Blom this is excellent! It works!