Combining data sets in SSRS to produce a report

I need to combine the results of two different datasets to produce a report.  I have provided a sample in the file.  Please suggest a solution using SQL Server Reporting Services.  I appreciate your help.
Who is Participating?
ValentinoVConnect With a Mentor BI ConsultantCommented:
Here's another article explaining the lookup functions which huslayer mentioned:

They do have one limitation though: you can only use one key field to do the match.  In your case you'd need two.  The easiest way around this limitation is probably to set up a calculated field on each dataset.  In that field, combine the two key columns with a separator to get something like "client name;employee".
Mez4343Connect With a Mentor Commented:
I would rewrite the SQL query for the dataset in the SSRS RDL so that the dataset returns all 8 result fields you need.

If you need some help with the sql, post a sample of the current SQL query in the dataset and some idea how the new Column3 and Column4 fields relate to the original data.
tikkubAuthor Commented:
These sets are not sql results.  They are from SSAS cube.  They cannot be part of the same dataset since the granularity of some of the measures would explode the data.  I am seeking a solution with in SSRS report.  Thanks.
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
Which version of SQL you're using?  if it's 2008R2 you could use

if not, so you might need a reporting staging database, dump your data there, join it and read from this reporting staging mart.
tikkubAuthor Commented:
I appreciate your help.  The trick was to use the lookup function.  The thing that helped fully solve the problem was to use calculated field combining the grouping fields.  Thank you very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.