manually Aggregate or Sum data fields in groups in SSRS

Question

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 this enter image description here

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

score 1 · Answer 1 · answered Jul 09 '13 at 19:45

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.

score 0 · Answer 2 · answered Jul 09 '13 at 18:55

0

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.

answered Jul 09 '13 at 18:55

StevenWhite

5,907
3
21
46

The problem with your suggestion is under Adam for Company A,I will see a discount of 2000 instead of only 1000. because technically Adam is only linked to project CompA-01 and CompA-02 projects of Company A – superartsy Jul 09 '13 at 19:02
This really comes down to a data structure design issue. You are trying to match up values from different datasets based on multiple conditions. I would suggest looking at the table/query design and following standard relational database guidelines so that you can just let SSRS use its built-in grouping features. You really shouldn't have to use multiple datasets for something like this. – StevenWhite Jul 09 '13 at 20:18
the problem is that the discount are not relevant at the person level. only project level and above.So creating a join an aggregating will not work. – superartsy Jul 11 '13 at 15:22

score 0 · Answer 3 · edited Jun 18 '15 at 06:39

0

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.

edited Jun 18 '15 at 06:39

Trubs

2,829
1
24
33

answered Jul 10 '13 at 10:22

user2354464

1

what do you mean by Tabular Mode? Also Discount amount will show up at the person level which is not correct. It needs to only show from Projects and up – superartsy Jul 10 '13 at 17:55
also the main the problem is that the discount are not relevant at the person level. only project level and above.So creating a join and aggregating may not necessarily work – superartsy Jul 11 '13 at 15:23

manually Aggregate or Sum data fields in groups in SSRS

3 Answers3