Sorting in cross tab for nested level

[Manu0521]

             ProductSet1      CustomProductsDataItem      TOTAL(Productset1+CustomProductsDataItem)      
              YTD    PYTD                     YTD    PYTD                             YTDCHANGE   PYTD   YTD
Salesrep 100   80                     100   80                                    40                   160   200

I am try to sort on the sales rep for the  ytd change nested under total data item. I click on the ytd chage under total and select the sort by value descending. this gives me the below error.

The nesting context is not clear from the selections. Either try sorting in Page preview view or use advanced set sorting.


IF I use order function and give tuple(total, ytd change ) on the sales rep it sorts properly . but on the drill down of sales rep the sorting is not mainitained for the next level since the oder function is for sales rep . customers is the next level.

How to achieve this sorting on a nested data item and make it work for drill down as well.

Thanks,

[Manu0521]

Guys,

Any help on this.

I am still looking for an approach to do this.

Thanks,

[MFGF]

Hi,

Have you tried selecting the Sales Rep row headings and defining Advanced Sort on these? You are sorting the Sales Reps in the rows, not the columns...

Cheers!

MF.

[Manu0521]

Yes, Advanced sorting in the sense Set sorting on Sales rep member set right - I have to sort the sales rep based on my Ytd Change nested under total. But I am not able to find my total in the advanced sort.

Order method on sales rep will work but when i drill down on salesrep that sorting doesnt work for the next level .

Thanks,

[Manu0521]

I tried this approach, but its not working
http://www-01.ibm.com/support/docview.wss?uid=swg21370960
I dont no how to relate my level1 item and level 2 drill down.

Is there any other way ?

Thanks,

[Manu0521]

Any updates on this issue. I am still stuck !!

Thanks,

[Lynn]

Don't use the order function for your rows. Just drag in a level or starting member you want. Create a tuple in your query to use as the sort key. Click on the rows and use layout sorting based on the tuple you created.

[MFGF]

Don't use the order function for your rows. Just drag in a level or starting member you want. Create a tuple in your query to use as the sort key. Click on the rows and use layout sorting based on the tuple you created.

Ha! You wrote this as I was replying with the same approach! :)

Hi Manu,

Select your Salesrep row headings, press the Sort button on the toolbar and under "Other sort options" choose "Edit Layout Sorting". You can bring across the desired column and sort descending on this.

MF.

[MFGF]

Oh, and Manu...

Please don't keep sending members personal messages pestering them to help you. If someone knows a solution and has time, they will post an answer. If not, they will simply think you are rude and pushy. I already deleted a couple of your posts asking for answers less than two hours after your initial question was posted. You have read the forum etiquette post, right?

Thanks,

MF.

[Manu0521]

Sorry about that. I will not use PM's anymore.

If I use layout sorting , I can sort on only one column. Now I have a nested column Total and below that YTD Change. And the same YTD change also comes under other column as well.

So specifically if i want to sort the YTD change column under total , Lynn asked me to create a tuple with a query calculation and use that single data item in the layout sorting.

since total is a data item and not a measure or a member, tuple(total,ytdchange) gives me an error.

If this is working I can use this item in my layout sorting. Also will this work in drill down also ?

Thanks,

[Lynn]

It works for drill down. You said in your original post that you used that tuple in your order function. How did it work there but not as a data item? How did you create your total data item? Can you create it as a calculated member?

And for goodness sakes....how many times must it be said.....

...

since total is a data item and not a measure or a member, tuple(total,ytdchange) gives me an error.

...

WHAT IS THE ERROR?

[Manu0521]

Hi Lynn,
Usually when we drag a query calulation we have four options right? calculated member, calculated measure ,set expression and other expression.

Error :   The argument at position 1 of the function 'tuple' is invalid in data item 'f' of query 'Query1', expected one of the following types: 'member, measure' .

so the total which I used was a dataitem and not a calculated measure.

Now when I change it to calculated measure it works fine and sorts perfectly.

But this might be very basic question even though i used a data item for total it was working properly . So when exactly a data item should be used and a the different options in query calculation be used.

Thanks,

[Lynn]

Try dragging a tuple from the toolbox into your query then use that as your sort field.