Filtering on a sum - SQL Server Forums

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.

All Forums

Development Tools

Reporting Services Development

Filtering on a sum

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2011-10-17 : 15:51:28

I have a report that deals with a bunch of totals. I want to have it only show the top 20 customers based on their total spending, but I can't filter on a SUM. I read a little bit about why this isn't supported but I didn't really understand it.

How can I go about doing this? It's VS2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:15:43

you're asking for sql query? if yes it will be like


SELECT TOP 20 customerid,customername,SUM(sales) AS totalSpend
FROM table
GROUP BY customerid,customername
ORDER BY totalSpend DESC

if you can provide some sample data from your table we will be able to give more accurate soln

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:16:46

if you want to do it in reports, can you specify how your current dataset looks like? are you getting data summarised in report or are you doing aggregation inside report expression?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2011-10-18 : 09:45:04

I guess I want to do it in the report, I have too many fields in the SP to do an aggregate function in there. I'd have to do something to each column if I use the SUM function in the SP itself.

As for the dataset, I'm doing the aggregations in the expressions. I'm not sure what you mean by "how your dataset looks", what exactly do you need to know?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 10:06:13

ok. if in report do like this

http://jetlounge.net/blogs/teched/archive/2007/12/16/get-ranking-of-groups-in-ssrs-top-x-ranking.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2011-10-18 : 10:59:49

I'm not having any luck with that either. I really can't understand why it's so difficult to get a top number or something. What is the reason you can't put aggregates in a filter? Why can't you just assign one of the aggregate columns to another variable and then filter on that?

Here is a screenshot of the layout of the table.

The groups are as follows:

Group 1 - ozp field
Group 2 - dzp field
Group 3 - Routed SCAC field
Group 4 - canm field

I'm trying to do the top 20 on the Routed Save column. I put in a detail row and tried the RunningValue function but all I got was an incrementing number starting with 1.

meef
Posting Yak Master

113 Posts

Posted - 2011-10-18 : 16:00:12

I figured something out, turns out I was able to filter in one of the groups for what I needed. I thought I'd need it on the whole table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources