Dataset 1 = reportable data
Dataset 2 = 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")
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.
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!
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")