Joining two datasets to create a single tablix in report builder 3

Question

I am attempting to join two datasets in to one tablix for a report. The second dataset requires a personID from the first dataset as its parameter.

If i preview this report only the first dataset is shown. but for my final result what i would like to happen is for each row of a student there is a rowgrouping (?) of that one students modules with their month to month attendance. Can this be done in report builder?

Bret

Answer 1

The best practice here is to do the join within one dataset (i.e. joining in SQL)

---

But in cases that you need data from two separate cubes(SSAS) the only way is the following:

1. Select the main dataset for the Tablix

2. Use the lookup function to lookup values from the second dataset like this:

   =Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product")
   

   Note: The granularity of the second dataset must match the first one.

Prevocalic

can i make a group on the results of the lookup? i didn't do it in sql as i could only see it being achieved using dynamic SQL. – Bret

The lookup function picks ONE record with a matching criteria. To retrieve multiple values for a single name or key field where there is a 1-to-many relationship, use LookupSet Function technet.microsoft.com/en-us/library/ee240819.aspx – Prevocalic

I've marked this as the correct answer. What i actually did for my particular report was to use various row grouping and column grouping on a matrix tablix. This allowed me to combine my two datasets to one query with repeating rows but group the non-unique as the row grouping and unique as the column headings. – Bret

There is no precision in your post you should precise where you need to put this formula and how it suppose to work... And if you can put the solution if the datasets haven't the same granularity – Howlet

Answer 2

We had a similar issue and that can be resolved this way. First of All, ensure the first data set's query and second data set's query are working fine by executing separately on the Database client tool such as Datastudio.

Build two data sets on SSRS tool with the respective queries and make sure both the data sets have same key column (personID).

On the SSRS report design, create a table from tool box and add the required columns from the first data set along with the matching key column(personID). Add a new column and use look up function to get the required column from the other data set against the same key column (personID).

Essy