Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
esundblad
Starting Member
3 Posts |
 Posted - 2007-10-16 : 11:17:12
|
| Hello-I am new to Reporting Services and need a little advice on the formatting of my reports. Specifically, sorting by the revenue subtotal. I am attempting to sort my report by top revenue totaled over a 12 month period. I have attempted to apply the sorting to the "Total" field, however, this causes the report to sort by the first month of data and not the overall total.Does anyone know how to sort by total or any work-around that would help solve this problem?Thanks for your help! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-10-16 : 14:20:44
|
| What do you mean by "Top Revenue"? Per what? For example, revenue per Customer? If you describe your problem in more detail, we can provide better, more specific answers to help you.The generic solution is: You'll need to include a Total column in your data, and then sort by that BEFORE your group. i.e., sort or group on CustomerTotal DESC, and then group on Customer. If you are using SQL 2005, there's an easy way to calculate Totals alongside your details in T-SQL:http://weblogs.sqlteam.com/jeffs/archive/2007/05/21/60211.aspxotherwise, you would have to join to a derived table with your totals.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
esundblad
Starting Member
3 Posts |
Posted - 2007-10-17 : 12:08:49
|
| Thanks Jeff-"Top Revenue" was just an example of a field within my data range. I should have just said "Revenue". Essentially I need my report to be sorted by total revenue in ascending & descending order. I think your solution of creating a total's column and sorting will work, I will try this out. I appreciate the help.-Eric |
|
|
|
|
|
|
|
|
Sorting by Totals