Dynamic Sorting in chart Report

[Shailaja0520]

Hi,
i have a chart report (any chart report).
User should be able to sort  the column in a chart asc or desc  (to be Prompted to user).

Apreciate any help

Thanks

[Luhaine]

This is just an idea but you might be able to do something with a Boolean style variable that responds to a prompt that you build into the report.

The prompt would basicly contain two values: one that would represent ASC and one for DESC. Based on whether or not the variable = true the list could be sorted ASC or DESC if it's not true.

You'd create the boolean then sort it ASC in the layout where one condition is met and DESC in the other. This would only allow the user to sort one specific column as far as my understanding goes. You'd have to make a specific boolean and prompt for every column that you want the user be able to sort and then do the style thing I think.

Hope this gives you an idea of where a solution might be found.

[Shailaja0520]

Thanks.Can u pls give a brief descrition of  how to dynamicallt sort a measure in chart report.

[wyconian]

Hi

The way to do this depends on what you want to sort the chart on.  It's easier if you want to sort on a numeric field.

Add a column to the report that feeds the chart.  This will be the sort column.

Add a prompt asking the users if they want to sort ascending or descending.

The sort column should be a calculation something like

case when ?p_prompt? = 'ascending' then numeric_field else numeric_field * -1 end

then sort the sort column ascending

If you want to sort on a text value you need to find someway of converting the text to a number probably by using a rank on the text field, your calculation for the sort column will then be something like

case when ?p_prompt? = 'ascending then rank(text_field for report) else (rank(text_field for report) * -1) end

Hope that helps

[Shailaja0520]

Thanks,

I want to sort the measure.

I tried this but for descending  sort it is showing negative values.is there is any way to avoid this in descending sort.

Thanks

[wyconian]

You need to have the negative values in the sort column but the sort column should be in addition to the column that holds your measures.  The 'proper' measures column will be shown on the report but the sort column won't be but will just be used in the sort.

Does that make sense (sorry it was our xmas party last night and my head hurts).

What I'm trying to say is this..

In your query you will have the measures column and the sort column.  The report/chart will only display the measures column, as long as the sort column is in the query you should be able to use it in the sort option.  If you can't see the sort column when you go to do the sort try selecting it in the propeties property of the query.

Hope that makes sense let me know if it doesn't

[Shailaja0520]

Thanks for the solution. Its working now.
Thanks again