PDA

View Full Version : Sorting calculated values within a column



wstansbury
12-Nov-2014, 10:37 AM
8378
I want to sort the column "% packets Returned" by all 50 states. Not the top 10 but all 50 states. Please provide sql, on how to do this

Formula for packets returned = [[RECEIVEDFLAG]]/[[J2.DATE_CREATED@COU]]*100

Bob Cergol
13-Nov-2014, 08:20 AM
Hi Wayne,

Providing you a custom SQL script for your reports is beyond the scope of the forum, but I'd be happy to speak with you about doing so on a consulting basis.

But you can easily get the result you want by merely setting the "top" value on the list options tab in design mode to a value higher than the number of groupings in your first level. Looks like that is 50. The value is not limited to 10 -- it can be 10,000 -- if that makes sense for a given report.

Regards,
Bob

wstansbury
13-Nov-2014, 10:59 AM
Not sure if it matter but State column I want to sort is the 2nd Drilldown8384
I put 50 in the List Options tab 8385

But when I drill down to the State column it is still not sorted 8386

Bob Cergol
13-Nov-2014, 11:45 AM
TOP N setting applies only to the first group level -- and with only a single grouping in that level. You can understand why if you "Show SQL" when you run the report and look at the group by and ordering clauses in the query. Specified groupings define the "order by" clause in the query and a "desc" added on the last column in the order by won't make much sense -- especially since each group value for state then has only a single pct. value.

You might consider making the state grouping its own report with a TOP 50 on that total column and then placing it in a dashboard (report package) where a filter (Date, Region) would be shared across the two reports. I think that might be the best approach for you. Or you could try a scenario where a separate TOP N report on State is the final drill-down object from the "parent" report with just the date and region groupings.

Bob

wstansbury
13-Nov-2014, 02:59 PM
I did the top 15 8390

I now have State as the first group but still no sorting8391

Bob Cergol
14-Nov-2014, 02:36 PM
Did you specify sorting, Descending or Ascending, on the total itself? You need to. It's one of the options to the right of the total on the same options tab page where you set the top N.

wstansbury
20-Nov-2014, 01:49 PM
There is no way to sort. The "%Packets Returned" sort cell is grayed out.
8425

Bob Cergol
20-Nov-2014, 04:48 PM
Sorry Wayne, I forgot you cannot order by a calculated total in the report.

Dynamic AI allows you to group on any of your formula columns, and then specify an ordering on an underlying column on which an SQL function is applied, like Sum or Count -- but this won't work on an extra column defined in the report because it is added after the result set, i.e. those columns are not known in the underlying query so can't be used for ordering. They get appended to the results delivered by the query.

In other words a Top 10 report of sales amount by states would have this in the SQL:
Group by Sales.State
Order by sum(Sales.Amount)

I think the solution will require doing the calculation in the underlying view.

Bob