Adding Values from two datasets

Question

Adding Values from two datasets

RenzoSQL

Ten Centuries

Points: 1252
More actions
November 15, 2010 at 12:29 pm

#242896

Hello, I am fairly new to SSRS:
I currently have two tables on a report with the following setup:
Table1:
Employee | Starting Count | Current Count
Table2:
Deduct Count
Table1 uses a dataset that displays the employee name plus the current count of items under him.
Table2 uses a dataset that calculates the number of items to deduct from the current count on dataset1 so we can calculate the Starting Count on Table1
Unfortunately the way I HAVE TO set up the tables is by having the starting count between employee and current count just like shown above so creating a third table and having starting count to the right of current count is not an option.
What I am trying to do is to subtract the CurrentCount - DeductCount from the two datasets and assign it to the StartingCount.
Under the StartingCount expression I have tried to use the following expression:
=First(Fields!Headcount.Value, "MRMcCoy")-First(Fields!Headcount.Value, "McCoyLeadershipLOA")
Obviously this only works for the first field and using the SUM just returns the aggregation of all the values which doesnt work.
The question is, how can I go about combining fields from two datasets that allows me to do calculations the way i explained above.
Thanks so much.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

I am assuming that you have some way to tie the 2 tables together. Why not just tie the two tables together in your dataset and use one table on your report? The column order on the report can be anything you want, so I am not sure I understand you issue of setting up your tables with one field to the left or right of another.

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 2

The reason I can not put everything on a single dataset is because is a recursive query that cant be combined in order for me to get headcounts based on different criteria.

The Starting Count has to be in the middle of the Employee and the Current Count and the reason i said i cannot move it, its because I would create a third table and create a dataset that calculates the difference between the Current and the Deduct counts instead of trying to do the calculation on reporting services.