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 |
|
osupratt
Posting Yak Master
238 Posts |
 Posted - 2008-03-20 : 09:38:43
|
| I have tried to get answers on this before, but haven't had any luck. I am working on trying to finish a report using SSRS in Visual Studio.The report is a Sales report. I have the report grouped by Location of our Offices and their Customers grouped underneath. The Customers revenue is Summed on the Customers group footer. I have this sorted by Top customer (highest revenue) to lowest customer (lowest revenue). This is fine and great, but now I need to limit the number of Customers (there could be upwards of 100 or more per Office) to the Top 20.I would like to do this on the report side, but if I need to do it in my SQL query I will.I would like to get the row number for the Customer group footer. Then I can limit these <=20, but when trying on the report side doing =rownumber(Customer)I get an aggregate function error for incorrect Scope.Any help would definately be appreciated. I have been stuck for about a week now trying to figure this out. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-20 : 10:03:03
|
| You want rownumber on footer? |
 |
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2008-03-20 : 12:51:10
|
| Yes. My first Group 'office' might have 50 Customers. The detail of these customers shows all their invoices w/revenue. I have summed the Customers on the Customer footer. I then sorted from most revenue to least revenue. I now need to put a row number on this Customer footer 1,2,3,4, etc. to limit the number of Customers shown on the report. I would like to limit to 20 customers. 1 - 19 being the top revenue Customers and 20 being all other customers lumped together as 'other'. |
|
|
|
modi_sanjay
Starting Member
9 Posts |
Posted - 2008-03-20 : 13:35:33
|
| I have an alternative solution for you First stepMake a query to find out the customer for top 20 revenue Second StepMake a query to find remaining customer less then from top 20 revenue customer and change the customer name as 'other'Third StepUnion All above both query and pass this query to your SQL Server Reporting Servicesi think its help you out, meanwhile i am workout how to control the above situation from reporting frontThanks & RegardsSanjay Modi |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2008-03-20 : 13:53:30
|
| I don't know how to do this. Do I create a different dataset or do I add these queries to my existing QUERY?Also, this is the only step I have remaining on this project and would like to solve this on the Report Layout side. Thanks. |
|
|
|
|
|
|
Help with ranking or row count