SSRS 2008:  How to group across multiple data sets?

Jim Horn
Jim Horn used Ask the Experts™
on
Hi All

I have a bunch of SSRS reports that have a couple of stored procs as data sets:

ds_rsp_report_name -->  The detail section of my report, with a PK and at least 1k rows.

ds_rsp_report_name_header -->  One record with the PK and a bunch of columns that will appear only once in a header section.   I'm trying to avoid including them in the detail to cut down on network traffic.

Q:  How can I include both of these data sets into a single grouping of PK, with a page break at the end, so I can generate reports that have the header section + detail for each PK with a page break?

My research (Google) up until now always speaks to grouping within a single tablix control, but never across multiple data sets.  

Thanks in advance.
Jim
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
BI Consultant
Most Valuable Expert 2011
Commented:
SSRS does not have functionality to group across different datasets.  You can't even join two datasets unless you do it in the query.

But perhaps the new lookup functions can be of assistance here? (I hope you're on R2?)
In your tablix's header section you can  use the lookup functions to fetch the other columns from the other dataset, based on the PK (which should be available in both datasets).

Here's how they work: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_3433-Looking-Up-Data-On-Different-Sources.html
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
I used the lookup method and it worked great.  Thanks.  -Jim
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Cool, well done! :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial