SSRS 2012 merge datasets with grouping

0
votes

Dataset 1 = reportable data

Reportable Data

Dataset 2 = lookup info

Lookup Info

I am able to use Lookup for in order to "merge" their data into one table. (from here: Joining two datasets to create a single tablix in report builder 3). With the tablix pointed at Dataset1, the formula to get Location is:

=Lookup(Fields!PersonId.Value, Fields!PersonId.Value, Fields!Location.Value, "Dataset2")

Can Get This Table

My problem comes from trying to take that one step further and applying a Row Group that relies on the data found from Dataset 2.

Want to Get This Table

I am unable to re-design and somehow come up with a combined T-SQL query. Dataset 1 is sourced from an SSAS MDX expression that is greater than 8,000 characters, and thus trying to manipulate that into SQL through OpenQuery fails because the command length is too large.

I am also unable to flip around the datasets and have Dataset 2 be the driver for the report layout and do lookups on Dataset 1. Dataset 2 will not have context to the list of PersonId's from Dataset 1 and thus has many more rows than would be necessary. The lookup must come from Dataset 1.

Thank you for your time and advice!

1
reporting-servicesreporting-services-2012

1 Answers

0
votes

Turns out there is no problem at all when applying grouping via Lookup on Dataset 2. My issue was user error; I forgot to add ".Value" to the first field.

Bad: =Lookup(Fields!PersonId, Fields!PersonId.Value, Fields!Location.Value, "Dataset2")