Sorting data based on 2nd column (nested crosstab node) in crosstab report

[sharkbait1211]

Hi guys,
I have been searching on some forums for this sorting issue but so far have not found a solution for the below described problem yet.

A crosstab report, created on a dimensional data source, having 3 columns: Account Number(1), Account Name (2) and Revenue (3)
The sorting is required on the second column (Account Name)

Acct No    Account Name Revenue   
N1           A1                   100
              B1                    1200
N2           A2                   500
              B2                    700

The report is expected to be displayed as below:

Acct No   Account Name   Revenue
N1           A1                   100
N2           A2                   500
N1           B1                   1200
N2           B2                    700

I have tried to put a Sort on Account Name and set Source Type = Data Item Value. But it did not work! The sorting only works when the order of the first 2 columns are switched (Account Name to be the first column!) But then the measure value (Revenue) is not correct.. I doubt that there is a grouping automatically set on different columns in each case!

Any suggestions for a solution or reference to some previous discussions about this would be very much appreciated!!!

Rgrds,

SB

[sharkbait1211]

One more input:
I've tried to use function ORDER() for Account Name but this did not help

Any ideas from u guys are very much appreciated

Tks,

SB

[MrTy]

Did you ever get a response to this?  I have the same problem on a report.

[MFGF]

Hi,

The issue is that you are not trying to sort on a crosstab column, but instead on a nested row heading. Because of the inherent grouping which occurs in crosstab edges, any sort on a nested value will always be within the context of the groupings above it in the nesting order, so sorting on Account Name in your situation will always be done within each Account Number.

One alternative would be to build this as a List report instead of a crosstab, allowing you to take absolute control over the grouping.

Regards,

MF.