How to Sort Table and Select Top 5 Based on a Calculated Field

• Question

• Hello SSRS Experts,

I am writing a report to show the Top 5 Largest Customer. For this I look up the table  SalesOrder and look at the field "Total Sales", which can be positive or negative based on the SalesOrder type (return or invoice).

So I insert a calculated field called Return = IIF (SalesOrderType.Value = "Return", TotalSales, 0)

I also create a calculated field called Invoice = IIF (SalesOrderType.Value = "Invoice", TotalSales, 0)

So the total sales for one company would be = Sum(Invoice) - Sum(Return)

The question is, how do I sort the table based on this total Sales ( Sum(Invoice) - Sum(Return) )? I tried putting a sort value on the table and group property as follow:

Expression =Sum(Invoice) - Sum(Return)

Direction = Ascending

However I get an error about the sorting expression when running the report. Can someone please help me with this?

Thanks in advanced for your help, greatly appreciate it.

-Elizabeth K.

Tuesday, June 15, 2010 6:03 PM

Answers

• Hi FogHorn

Initially I tried the sort function on the table property with:

Expression =Sum(Invoice) - Sum(Return)

Direction = Ascending

Before I got an error with this... but now it is working again :o not sure why.

Thank you for your reply! :)

-Elizabeth K.

Tuesday, June 15, 2010 11:48 PM

All replies

• Is tehre any reason why you can't do the sorting in the SQL Query?

Can you try that, then run the report and see if the sorting works?

Tuesday, June 15, 2010 11:11 PM
• Hi FogHorn

Initially I tried the sort function on the table property with:

Expression =Sum(Invoice) - Sum(Return)

Direction = Ascending

Before I got an error with this... but now it is working again :o not sure why.

Thank you for your reply! :)

-Elizabeth K.

Tuesday, June 15, 2010 11:48 PM