Sum up distinct or Top 1 for specific fiel

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

Sum up distinct or Top 1 for specific fiel

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2006-02-06 : 12:45:27

This may sound a bit weird...but here it goes. I have in my SSRS 2005 report one table. In that table I have:

Header1

Groupp1

Footer1

Some of the fields in the Group look like this:

Company Number Branch FeeGoal

The problem I have is FeeGoal. It comes from a table that is simply joined to my main dataset (via the dataset's stored proc) on company number. So I do not want this one summed. I want it to be the value the user entered for that company only, not summed. I have an ASP.NET input form where each of the companies has one FeeGoal input field. I then update all company records in a temp table where they have a FeeGoal field....and update each FeeGoal Field for that company with the FeeGoal the user entered into my form.

IN the report, I do not put sum, I just put

=Fields!FeeGoal.Value

So that I end up with basically Top 1 of FeeGoal for the particular company in the group. The problem I have now is how to sum up all FeeGoals without summing up of the same FeeGoal values for each company. Remember, I just want to sum up all Top 1 values for FeeGoal in the Group.

How do I do this?

jhermiz

3564 Posts

Posted - 2006-02-06 : 13:28:04

Not sure :)

Post DDL, SAMPLE DATA, EXPECTED RESULTS.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

dba123
Yak Posting Veteran

90 Posts

Posted - 2006-02-06 : 15:15:23

Sample Data

CompanyName Field1 Field2 FeeGoal
ABC 100 2000 200000
ABC 100 232 200000
ABC 112 2 200000
DCE 23 223 300000
DCE 203 200 300000
DCE 24 229 300000
EER 22 344 400000
EER 220 111 400000

Picture that as my Dataset

Now in my Report, I have the followingfields in my Group, grouped by CustomerNumber(not shown)

Group1
CustomerName Field1 Field2 FeeGoal

In my FeeGoal, I simply put =Fields!FeeGoal.Value, not =SUM(Fields!FeeGoal.Value) because I want to only sum Distinct, not 30000 + 30000 + 30000 for example..I only want to show 300000 for Company DCE

But in the Footer, I put the same Fields!FeeGoal.Value. Of course that only returns the top result which is 20000 for company ABC.

If I then try =SUM(Fields!FeeGoal.Value), it's way over inflated because it's counting the FeeGoal multiple times per customer, I only want to sum up each common instance.

FeeGoal is a unique case, usually you let the grouping do it's work but I don't want to overinflate my total for FeeGoal in my Footer.

If there is some way to do =SUM(Top 1 FeeGoal) or SUM(Distinct FeeGoal) in SSRS 2005 VB syntax somehow in the expression builder, this is the only way to get this to be accurate unless someone else knows...

Here's a couple of screen shots. You'll see the overinflated FeeGoal sum:

http://www.photopizzaz.biz/feegoal1.jpg
http://www.photopizzaz.biz/feegoal12.jpg

jhermiz

3564 Posts

Posted - 2006-02-06 : 15:24:19

quote:
Originally posted by dba123

Sample Data

CompanyName Field1 Field2 FeeGoal
ABC 100 2000 200000
ABC 100 232 200000
ABC 112 2 200000
DCE 23 223 300000
DCE 203 200 300000
DCE 24 229 300000
EER 22 344 400000
EER 220 111 400000

Picture that as my Dataset

Now in my Report, I have the followingfields in my Group, grouped by CustomerNumber(not shown)

Group1
CustomerName Field1 Field2 FeeGoal

In my FeeGoal, I simply put =Fields!FeeGoal.Value, not =SUM(Fields!FeeGoal.Value) because I want to only sum Distinct, not 30000 + 30000 + 30000 for example..I only want to show 300000 for Company DCE

But in the Footer, I put the same Fields!FeeGoal.Value. Of course that only returns the top result which is 20000 for company ABC.

If I then try =SUM(Fields!FeeGoal.Value), it's way over inflated because it's counting the FeeGoal multiple times per customer, I only want to sum up each common instance.

FeeGoal is a unique case, usually you let the grouping do it's work but I don't want to overinflate my total for FeeGoal in my Footer.

If there is some way to do =SUM(Top 1 FeeGoal) or SUM(Distinct FeeGoal) in SSRS 2005 VB syntax somehow in the expression builder, this is the only way to get this to be accurate unless someone else knows...

Here's a couple of screen shots. You'll see the overinflated FeeGoal sum:

http://www.photopizzaz.biz/feegoal1.jpg
http://www.photopizzaz.biz/feegoal12.jpg

This is a simple problem to solve. Consider this, RS allows you to display multiple objects on one single report. This means you can have two table reports in one report. One table report is in your details section etc with your regular query. The other table can be placed into your footer and its dataset will be a DIFFERENT dataset then your first table.

I just tried it and got a perfect working model.

Here is some sample code you can drag and drop into query analyzer and see that the sums are only done on distinct values.

CREATE TABLE #TEST
(
VAL int
)

INSERT INTO #TEST(VAL) VALUES(1)
INSERT INTO #TEST(VAL) VALUES(1)
INSERT INTO #TEST(VAL) VALUES(2)
INSERT INTO #TEST(VAL) VALUES(3)
INSERT INTO #TEST(VAL) VALUES(3)

SELECT SUM(THETESTER) FROM
(
SELECT MAX(VAL) AS THETESTER FROM #TEST GROUP BY VAL) t

DROP TABLE #TEST

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

dba123
Yak Posting Veteran

90 Posts

Posted - 2006-02-10 : 09:51:58

Here's the problem, I don't want to code huge calculations in SQL...it's way too complicted for the calculations that are in this report to hand code that in SQL so I'd rather use a reference to the group field.

Thanks though....!!

jhermiz

3564 Posts

Posted - 2006-02-10 : 16:56:16

dba123, you DONT have to perform those "huge calculations" i was demonstrating a test-case. Meaninig it will work for just one simple sql statement:

SELECT SUM(THETESTER) FROM
(
SELECT MAX(VAL) AS THETESTER FROM YourTableName GROUP BY VAL) t

Thats it...where do you see huge ?

The tsql i included, is because I did not have those tables that you are using. It is a test case to prove it will work for your data.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources