divide two table value in SSRS

Question

I´ve got two tables in my Report:

I would like to divide the value from Scrap with the value from Table Total

--> (16227 / 425841) * 100

This is the code:

Select date as Datetime, tevent.name as Event, SUM(value) as sum

    from tCount inner join tEvent ON tCount.eventid = tevent.id
             where Name in ('Drive  Fast', 'Drive Slow')
               and date > getdate() -1
               and tevent.Name in ('E01','E02','E03','E04','E05','E06','E07','E08')
           and CalName = 'Drive'

The sum of these: 'E01','E02','E03','E04','E05','E06','E07','E08', I did it in the report like this: Sum(Fields!E01.Value+Fields!E02.Value+Fields!E03.Value+...

And for the other table it looks like this:

Select date as Datetime, tevent.name as Event, SUM(value) as sum

        from tCount inner join tEvent ON tCount.eventid = tevent.id
                 where Name in ('Drive Normal')
                   and date > getdate() -1
                   and tevent.Name in ('E01','E02','E03','E04','E05','E06','E07','E08')
               and CalName = 'Drive'

And I must do a division of this two statements. But if I put in the Scrap Expression the value from Total then the result is wrong. Hope you can help me.

Are these two tables from two different datasets? How do the two tables relate to each other - is there a key not shown in your example above? — Jonnus
yes the two tables are from two different datasets, The Value of this both table is in one table in the database, the name is different. I tried to do it in one statement but it would not work. here is my other question: link — mayo
Can you provide a sample of the data your datasets are returning. It is very difficult to understand the structure you are reporting on from your description — Jonnus

Jonnus Jonnus · Accepted Answer · 2015-12-10T12:05:07

From what I understand of your question, you have two datasets, and you want to calculate the percentage of Scrap of the Total

To replicate your problem I have created two DataSets as follows

Scrap Dataset
myDateTime  Event  mySum
01/01/2015  Scrap  16227 
02/01/2015  Scrap  14637 
03/01/2015  Scrap  14174

Total Dataset
myDateTime  Event  mySum
01/01/2015  Total  425841 
02/01/2015  Total  434024 
03/01/2015  Total  405216

Create a Table in your report and set column 1 to be mySum from ScrapDataSet

Then you can use the LOOKUP function in Column 2 to look up a specific value from another dataset assuming there is a common field. In this instance

=Lookup(Fields!myDateTime.Value, 
        Fields!myDateTime.Value, 
        Fields!mySum.Value, 
        "TotalDataSet")

Compares myDateTime in ScrapDataSet to myDateTime in TotalDataSet, and where there is a match returns mySum from TotalDataSet

You can then combine these two expressions as follows to get the percentage as required in Column 3

=Fields!mySum.Value / 
 (Lookup(Fields!myDateTime.Value, 
         Fields!myDateTime.Value, 
         Fields!mySum.Value, 
         "TotalDataSet"))

In this example I formatted this third column to be a percentage format to 2 decimal places.

These steps result in a table that looks like this

Hopefully this is the output you require. If you need further assistance please ask with clarification of the original question.

As a final note Sum and DateTime are not recommended field names as these are both keywords that can be used in SSRS to either define a type of perform a function. These should really be renamed – as I have done in the example above.

divide two table value in SSRS

1 Answers