crosstab - sort second column

[Sunita]

Hi
I have a crosstab report in which i am showing Sends for sale_rep_name (as row) and dates (in column) (sorted) and at the end i am showing MTD sends.

Sends               | DATE(sorted)   | MTD Sends
Sales_rep_name |
                       |

Now issue i am facing with this report is i need to sort MTD sends also. How do i do that i tried selecting that column and clicking sort button but it got disabled. Most likely because i am sorting Dates as well.
Is there a way that i can sort both (Date and MTD sends).
Please let me know if my question is not clear

Thanks in advance
NOTE: I am using oracle database.

[Sunita]

Hi All,
Even if i do not sort dates .....IS it possible to sort MTD sends..It is a calculated column

Please help

[dlafrance]

In report studio, look for the function order()

You can order a dimension by a value

Ex : order([a].[a].[a]; [Value] DESC)

[Sunita]

Hi Dlafrance,

Thanks..for the reply.

Does this function works with relational model.
I looked at this function but i was not able to understand.. How do i use it

My problem is to sort a calculated column (query calculation, I am calculating MTD sends in it) in a cross tab report. 

Thanks,
Sunita

[Arsenal]

Have you tried using the pre-sort property of the calculated query item? You can set that to ascending or descending within the query that contains your calculated query item.

if ot does work for you, then be aware that it may cause a performance issue in some cases, so you might want to take some timings with and without it set so you can get a ball par idea of performance degradation, if any

[dlafrance]

order function works for relationnal datasource.

Ex ;

order ([Customer]; [Sales value]; DESC)

This will order the customer field by the sales amount.

if you put this field and the field Sales Value, you will see the customer with the highest sales first.

[Sunita]

Have you tried using the pre-sort property of the calculated query item? You can set that to ascending or descending within the query that contains your calculated query item.

if ot does work for you, then be aware that it may cause a performance issue in some cases, so you might want to take some timings with and without it set so you can get a ball par idea of performance degradation, if any

Hi Arsenal
Thanks for reply but i am not able to see pre-sort in properties. actually what i am doing is.. I created a simple crosstab report showing sends data for each date in a month and then added MTD Sends as a query calculation to show sum of Sends data till date (MTD)

Thanks,
Sunita   

[Sunita]

order function works for relationnal datasource.

Ex ;

order ([Customer]; [Sales value]; DESC)

This will order the customer field by the sales amount.

if you put this field and the field Sales Value, you will see the customer with the highest sales first.

Hi dlafrance 
Thanks again but it didn't work.
my expression is
order ( [Q_Daily_Sales].[Party Number];[Q_Daily_Sales].[Event Tran Evnt Quantity];DESC)

and the error i am getting is
QE-DEF-0459 CCLException
OP-ERR-0025 The following OLAPPlanner internal error occured:'OPASSERT(pParent!=NULL) in OPBIQuery::RemoveUnnecessaryOrderExpressions() at OLAPPlannerQuery.cpp:5121'
:(   what should i do now

[Arsenal]

Hi Arsenal
Thanks for reply but i am not able to see pre-sort in properties. actually what i am doing is.. I created a simple crosstab report showing sends data for each date in a month and then added MTD Sends as a query calculation to show sum of Sends data till date (MTD)

Thanks,
Sunita   

Go into the query that has your calculated data item. Select that data item and then look on the left side in the properties pane of that query item. You should be seeing Pre-Sort

[Sunita]

Go into the query that has your calculated data item. Select that data item and then look on the left side in the properties pane of that query item. You should be seeing Pre-Sort

Thanks Arsenal....
I change pre-sort property...even then it is not coming as sorted. Probably because this column is a calculated one and the values are coming only at runtime..not sure

Thanks

[Arsenal]

hmm

perhaps we are over analyzing this. You say you've already tried using sort from the properties pane (or Data pulldown from the toolbar) by highlightig the column header and trying to select sort?

should be working. I tried that in a simple crosstab and it doesn't get greyed out for me. I can specify the sort order in advanced sorting.

there is another round about way..you create a data source query subject, write your own sql to include the key column (which might be date in your case) and the calculated (mtd send) column and then include an Order By and create the appropriate relationships and then create a model query subject based off the data source query subject you just created. This can then be used within the report.

But seems like an awful lot of trouble for such a simple requirement

[Arsenal]

if mtd sends does not depend upon the report query then you could also try by just having it first in the model query subject that is driving your report instead of going down the SQL route

[Sunita]

Thanks Arsenal....
yes i tried to used pre-sort from properties pane, tried toolbar's sort option and with advance sort option also ....it did not give me any error but the result is also not sorted.

With SQL ...will it not slow down the report performance.

Thanks Again,
Sunita Dudi

[dlafrance]

Hi dlafrance 
Thanks again but it didn't work.
my expression is
order ( [Q_Daily_Sales].[Party Number];[Q_Daily_Sales].[Event Tran Evnt Quantity];DESC)

and the error i am getting is
QE-DEF-0459 CCLException
OP-ERR-0025 The following OLAPPlanner internal error occured:'OPASSERT(pParent!=NULL) in OPBIQuery::RemoveUnnecessaryOrderExpressions() at OLAPPlannerQuery.cpp:5121'
:(   what should i do now

Just is case... can you try again but with a list instead of a crosstab ? I think it may help.

[Arsenal]

Since the database doesn't have the calculation, at some point you will have to do the calculation whether at the report level or FM level. Pushing the calculation to the FM level may help you out. I don't think performance should be an issue by having the order by in the FM model, any worse than having it in the report.
you can take some timings ...have the calc. just in the report without sorting, then have the SQL within FM but without the order by and use that as a column and then finally add in the order by and have it as a column

[mrcool]

Hi Sunitha..

Did you find a solution for this?

Thanks

[Sunita]

No I didnot get any solution for this :(
but convinced client to take it with out sort  :)