Dear All,
Thanks for all your help in the past! I have came across another issue on a report, basically our MD wants a report sorted by brand and then by store, I have created a crosstab as he wanted revenue and budget. I have tried but cannot get the report to sort first by Brand and top revenue and then sort that by store revenue ascending, any ideas?
Below is the format of the report prior to sorting, ideally want top brand first (most left column) and the store's revenue ranked ascending which would be Brand T and then store Z as first row, hope this makes sense.
Brand X Brand Y Brand H Brand T
Store Revenue Revenue Revenue Revenue
X 560000 1500 5390 450000
Y 45000 450000 560 609
Z 500000 560000 32400 6700000
T 60000 45000 0 45000
H 56000 35000 100050 450
D 780000 65000 0 54000
No, doesn't make sense.
You can not sort revenue column in two different ways in the same crosstab.
Unless you create two separate crosstabs and align them together but that'll make the data display in wrong order.
You can sort by brand and store but when you do a top revenue sort, it can't do that as it changes sort of your other two attributes.
To the extent you've described it, it may be possible: you can sort by brand total revenue, then by store total revenue for the top brand, giving you a revenue-ranked leftmost column in your crosstab. The remaining columns will not be revenue ordered; if that's your MD's expectation s/he is asking the impossible.
How you can sort revenue in two different ways. I think its not possible
I think in Query studio I can sort the brand columns manually and then take it into Report studio and sort on the first brand, thanks for all you comments much appreciated. :D
Hi,
You can try this:
1. Create a data item for brand and one for store (e.g. if brand is not null then 1 else 0).
2. Click on Advanced Sort to open advaned sort window
3. Drag the data items in Detail Sort Group folder and apply the sort accordingly.
Thanks,
Anirban