Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

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.
join-data-sets-in-ssrs-report.xlsx
0
tikkub
Asked:
tikkub
3 Solutions
 
Mez4343Commented:
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.
0
 
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.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Which version of SQL you're using?  if it's 2008R2 you could use
http://prologika.com/CS/blogs/blog/archive/2009/08/12/reporting-services-lookup-functions.aspx

if not, so you might need a reporting staging database, dump your data there, join it and read from this reporting staging mart.
0
 
ValentinoVBI ConsultantCommented:
Here's another article explaining the lookup functions which huslayer mentioned: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_3433-Looking-Up-Data-On-Different-Sources.html

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".
0
 
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now