How to Combine Fields from Two Datasets SSRS?

Question

I am working on a report to show the total number of hours an employee spent working. Our company tracks labor hours by Service Request and Work Order so I need to bring totals for each into the report.

I created two datasets- one for Work Orders and one for Service Requests. Ideally, I would like to combine the total number of Work Order hours with the total number of Service Request hours and present that number listed by employeeID since both datasets have the employeeID field.

I thought it would be as simple as:

=(SUM(Fields!TOTALHOURS_WO.Value, "DataSet1") + SUM(Fields!TOTALHOURS_SR.Value, "DataSet2"))

I don't get an error, however, I am getting a number which repeats for each employee so I know I'm doing something wrong.

Any help is greatly appreciated.

There are many questions about working with fields from different datasets. You have to use the Lookup function so try searching for that. — StevenWhite

Lucky Lucky · Accepted Answer · 2018-04-05T20:38:29

Mal,

As @StevenWhite mentioned, LOOKUP is probably the function you are looking for.

Here is an example for you. For the example datasets:

EmployeeID | TOTALHOURS_WO
-----------------------------------
123        |   12
456        |    3

EmployeeNum| TOTALHOURS_SR
-----------------------------------
123        |    2
456        |    5

You will note that each table in a SSRS report needs a DataSet assigned to it. I will assume your table is using our first DataSet, which we will name "DataSet1". The second dataset above will be "DataSet2".

For your total hours you will use an expression. It should look something like this:

=TOTALHOURS_WO + LOOKUP(Fields!EmployeeID.Value, Fields!EmployeeNum.Value, Fields!TOTALHOURS_SR.Value, "DataSet2")

So you will be adding the TOTALHOURS_WO from your local dataset to the result from the LOOKUP function. What lookup is doing is taking the first field from your local dataset, finding a match in the dataset provided to the function (as a string), and returning the field from the row it matched to. The last parameter is the dataset to search.

Just in case you get an error... it's always a good idea to cast data to the type you want to work with in case it comes in wrong. So...

=CINT(TOTALHOURS_WO) + CINT(LOOKUP(Fields!EmployeeID.Value, Fields!EmployeeNum.Value, Fields!TOTALHOURS_SR.Value, "DataSet2"))

This assumes you have a one to one match on employee ID. If you have to SUM both fields you can try this:

=SUM(CINT(TOTALHOURS_WO)) + SUM(LOOKUPSET(Fields!EmployeeID.Value, Fields!EmployeeNum.Value, CINT(Fields!TOTALHOURS_SR.Value), "DataSet2"))

SUM for TOTALHOURS_WO will give you the SUM in your current table group (so make sure you are grouping by staff ID in the table). It will then add it to the SUM of LOOKUPSET. LOOKUPSET works the same as lookup but returns an array of matches instead of the first.

Hope this helps.

How to Combine Fields from Two Datasets SSRS?

1 Answers