Link to home
Start Free TrialLog in
Avatar of pzozulka
pzozulka

asked on

SQL Reporting Services Expression, how to SUM

I have a Data Set in SQL Reporting Server. I attached the code below. Data produces following results:

DisplayName, FirmName, Address, City, State, PostCode, ***(BUID)***, ***(Count J.JobNo)***
BUID stands for "BusinessUnit ID" (ex. Los Angeles, San Francisco, New York)

In Report Designer, I grouped by BUID. So it categorizes all results by BUID (location).
I need the "Expression" to Give me the total of the "Count" for each BUID.

Desired Results: --Need Total for each buid...already have everything besides the total for each BUID
*Los Angeles
DisplayName, FirmName, Address, City, StateName, PostCode, 5
DisplayName, FirmName, Address, City, StateName, PostCode, 2
DisplayName, FirmName, Address, City, StateName, PostCode, 3
                                                                                                    10
*San Francisco
DisplayName, FirmName, Address, City, StateName, PostCode, 7
DisplayName, FirmName, Address, City, StateName, PostCode, 2
DisplayName, FirmName, Address, City, StateName, PostCode, 3
                                                                                                     12
So I already have everything besides those totals in the footer of the "Group".
I tried =SUM(FieldName, Dataset), but that only gives me the entire total, I simply need each Group's total, how do I alter that expression?

Thank you in advance, I appreciate your time.


SELECT C.DisplayName AS 'Contact', F.FirmName, F.Address, F.City,
S.StateName, F.PostCode,  (SELECT @JobDateFrom + ' ~ ' + @JobDateTo) AS JobDates, B.BUID,
COUNT(DISTINCT J.JobNo) AS 'Jobs'
FROM Jobs J JOIN JobsParties JP ON J.JobNo = JP.JobNo
            LEFT OUTER JOIN JobsPartiesSvc JPS ON JP.JobPartyNo = JPS.JobPartyNo
            LEFT OUTER JOIN Contacts C ON J.OrdContactNo = C.ContactNo
            LEFT OUTER JOIN Firms F ON C.FirmNo = F.FirmNo
            LEFT OUTER JOIN State S ON F.StateNo = S.StateNo
            LEFT OUTER JOIN Invoices I ON J.JobNo = I.JobNo
            LEFT OUTER JOIN BU B ON B.BUNo = J.BUNo
WHERE J.Status NOT IN (712,812,811)
AND J.JobDate BETWEEN @JobDateFrom and @JobDateTo
GROUP BY C.DisplayName, F.FirmName, F.Address, F.City, F.PostCode, S.StateName, B.BUID

Open in new window

SOLUTION
Avatar of Faizan Sarwar
Faizan Sarwar
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pzozulka
pzozulka

ASKER

Yeah, I tried that but it gives me the entire total of that field. Instead is there a way to break each sum down for each group?
How can I get around the limitation in SSRS 2005 of being able to SUM a Group level field in my FOOTER field's expression!!!  

I tried to put this in my footer expression: =Sum(ReportItems!Jobs,"BUID") & =Sum(textbox32,"BUID").
Note that I am grouping by BUID (Business Unit ID - Los Angeles, San Francisco, New York)

These are the errors I get:
[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox32' uses an aggregate function on a report item.  Aggregate functions can be used only on report items contained in page headers and footers.

[rsReportItemReference] The Value expression for the textbox textbox32 refers to the report item Jobs.  Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.
Build complete -- 2 errors, 0 warnings
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
=Sum(Fields!Jobs.Value,"BUID")

I was just unclear on one point from the code snippet above. "BUID" is the NAME of the grouping that you create. It is not a column name.

Very good solution, though. I was having a real time with this and it really saved me.
You can also check alternative approach Using TSQL -  ROLLUP
In output you can see Sum of total sales by each group then complete total at the end

REF: http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx



SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP


EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
1           NULL        55000.00
2           2005        15000.00
2           2006        6000.00
2           NULL        21000.00
3           2006        20000.00
3           2007        24000.00
3           NULL        44000.00
NULL        NULL        120000.00