Specified sort in crosstab

[Janezz]

Hello,

I have an application where user can select any number of products and the crosstab will then display summary data for each selected product in its own column and all other products (not selected) will be combined in one column named 'Other'. The stored procedure for the report already combines all other products in one column, so for example if user selected three products, the stored procedure will return data for those three products and will group all other products in a column named 'Other'. Stored procedure sorts products alphabetically by product name, so the position of the column 'Other' in the dataset is not fixed for every report execution, but it depends on the names of the selected products.

Now the problem:
I always want to display column 'Other' as a last column in the crosstab, no matter which or have many products have been selected by the user. I tried to use specified sort in a crosstab the following way:
1. I have specified group name 'Other' where the field value 'is equal to' Other. So I have one group on 'Specified order' tab.
2. I have checked 'Leave in their own groups' option on 'Others' tab.

This now always displays 'Other' column as the first column in the crosstab and all other columns are sorted alphabetically after the 'Other' column. What I want is to display 'Other' column as the last column in the crosstab. Is this possible with the specified sort?

Thanks

 

[IanWaterman]

Not tried this but how about changing your formula so that Others are say

ZZOthers

Leave in alphabetic order. ie Not specified

Then in Group Options select Customise Group Name

Check Use a formula as group name,

If GroupName = 'ZZOthers' then 'Others' else GroupName

Ian

 

[CR85user]

Try the following - Ought to work provided none of the genuine groups start with a 'ZZZ' prefix.

Create a formula:

//{@Order}
if {resultofsp} like 'Other' then 'ZZZOther' else {resultofsp}


Put {@Order} in place of the column header.

Right click the column header and choose Column Options -> Group Options -> Click on 'Customise Group Name Field', Select Use formula as.....

In the X+2 box enter as the formula:
if {@Order} startswith 'ZZZ' then replace({@Order},'Z','') else {resultofsp}

CR8.5 / CRXI - Discovering the impossible

 

[lbass]

The approach that has been suggested is the best approach, because it is dynamic and will work regardless of the user selections. However, I just wanted to say that you could use the dropdown in the specified order tab to select the groups in order, placing the 'Others' group (which already exists as a group) in the last position.

-LB

 

[CR85user]

@ Ian - It's great to see that we both had the exact same thought process lol.

LBass you are spot on in your approach also if static parms as stated.

I'm off to practice typing faster

CR8.5 / CRXI - Discovering the impossible