CODEWITHSUNDEEP
reporting-servicesssrs-2008aggregate-functionsssrs-grouping
superartsy
superartsy

Reputation: 489

manually Aggregate or Sum data fields in groups in SSRS

I have a Sales Table and a Discounts table.

The main dataset is a Sales table has sales per person.

Every person is associated under only one project and every project has only one supervisor.

The second dataset - discounts table has the discount entry for each Project

Sales Table

Supervisor  | Company   |Project    |Person |Sale
Adam        | CompanyA  |CompA-01   |Amy    |100
Adam        | CompanyA  |CompA-02   |Beth   |200
Adam        | CompanyA  |CompA-02   |Cary   |200
Jenny       | CompanyA  |CompA-05   |Dawn   |300
Jenny       | CompanyA  |CompA-05   |Emma   |400



Discounts table

Company | Project   |Month  |Amount
CompanyA| CompA-02  |Jan    |500
CompanyA| CompA-02  |Feb    |500
CompanyA| CompA-05  |Jan    |1000

I want to display a report where the Discounts field is calculated as below

Supervisor  | Company   |Project    |Person |Sales  |Discounts 
Adam                            
          CompanyA                           500       1000 <---HELP CALCULATE THIS
                         CompA-01            100       0
                                      Amy    100
                         CompA-02            400       1000
                                      Beth   200
                                      Cary   200
...
...
TOTAL                                        12000     2000

I have following row groups in my report:

Supervisor Group
---Company Group
-----Project Group
----------Details

I am able to calculate the Discount column at the Project Grouping level as follows

=Code.SumLookup(LookupSet(Fields!Project.Value,Fields!Project.Value,Fields!amount.Value,"Discounts"))

Then at the Total Level I calculate the discount as:

=Sum(Fields!amount.Value, "Discounts")

I need help figuring out how to calculate the sum of the discounts at the Company and Supervisor level.

As many of you suggested that my query was the problem I just wanted to go ahead and demo exactly what issues I am facing.

As suggested I redesigned the query using a join

SELECT Sales.Supervisor, Sales.Company, Sales.Project, Sales.Person, Sales.SaleAmt, SUM(Discounts.Amount)
FROM Sales 
LEFT OUTER JOIN Discounts 
ON Sales.Company = Discounts.Company 
AND Sales.Project = Discounts.Project 
group by Sales.Supervisor, Sales.Company, Sales.Project, Sales.Person, Sales.SaleAmt, Discounts.Amount

created a report like

enter image description here

The report previews like thisenter image description here

Notice how the discount values are summed up all wrong. What can I do to correct this?

Upvotes: 1

Views: 17641

Answers (3)

user2354464
user2354464

Reputation: 1

Please use below query

SELECT Sales.Supervisor, 
       Sales.Company, 
       Sales.Project, 
       Sales.Person, 
       Sales.Sale, 
       Discounts.Amount
FROM  Sales 
LEFT OUTER JOIN Discounts ON Sales.Company = Discounts.Company 
                          AND Sales.Project = Discounts.Project
GROUP BY Sales.Supervisor, 
         Sales.Company, 
         Sales.Project, 
         Sales.Person, 
         Sales.Sale, 
         Discounts.Amount

in the data set and with the help of Tabular mode you will able to generate the report. I have tried it and it worked.

Upvotes: 0

djangojazz
djangojazz

Reputation: 13242

You are making too much work for yourself IMHO having the code do it. SSRS is built in with an understanding of the level in the grouping you are on. EG: If I have a detail level with a function like Sum(Discount), then I add a 'Project' grouping and choose to add a 'header' row. If I put that calculation in the new row above the 'Detail' row you now are grouping by the other data. You can then group the projects by Company, and the Companies by Supervisor, etc.. You can then add the same calculation on a different row and it will achieve different values merely by knowing the level it is on.

Upvotes: 1

StevenWhite
StevenWhite

Reputation: 6034

Rather than writing complex expressions or nested queries to join these two datasets, it would be easier to add a separate dataset that gets the data you need for a lookup. For example, add one dataset called CompanyDiscounts which would look like this:

select Company, sum(Amount) as Discount
from DiscountsTable

Now you can add a lookup function to get the total discount at the Company level.

=Lookup(Fields!Company.Value, Fields!Company.Value, Fields!Discount.Value, "CompanyDiscounts")

You can repeat these steps for the Suporvisor level.

Normally I wouldn't recommend a method like this, but the nature of your data structure lends itself to this sort of workaround. Ideally, you would have a sale ID with an associated discount so that you can join all the relevant data in one query and just use grouping aggregates to fill in the table.

Upvotes: 0

Related Questions