SSRS: Using tablix with multiple Dataset

Question

I have two datasets:

My First dataset (Students) looks like this:

Student_Name| ID
Jack Luis   | 1
Adam Bob    | 2

And my second dataset (Exam) looks like this:

Student_ID  | Exam               | Note
1           | Java               | 15
1           | Php                | 14
2           | Java               | 12
2           | Php                | 13

I want to get this in the same Tablix:

Student Name | ID
Jack Luis | 1
Adam Bob | 2

Student_ID | Student Name | Exam   | Note
1          |Jack Luis     | Java   | 15
1          |Jack Luis     | Php    | 14
2          |Adam Bob      | Java   | 12
2          |Adam Bob      | Php    | 13

Possible duplicate of [Reporting services: Join all field on a dataset](http://stackoverflow.com/questions/10997770/reporting-services-join-all-field-on-a-dataset) — Farside, Apr 25 '16 at 13:16
It is best practice to use a stored procedure a dataset and let SQL do this more efficiently than a lookup. So just use one dataset which calls a stored procedure which puts the two tables together. — J Greene, Apr 25 '16 at 19:23

alejandro zuleta · Answer 1 · 2016-04-26T15:24:17.183

I think this can be solved using LOOKUP function. LOOKUP function joins multiple dataset using a common field in the involved datasets.

Create a tablix and set the DataSetName property to your second dataset.

Drag and drop the fields to the columns you want to show. For the Student Name column use the following expression:

=Lookup(Fields!Student_id.Value,Fields!Student_id.Value,Fields!StudentName.Value,"DataSet21")

In the above expression replace DataSet21 by the actual name of your first dataset.

It will preview something like this:

UPDATE: Grouping by a header row.

Add a tablix and set your second dataset in DataSetName property. Add Exam and Note fields to the corresponding columns.

Add a Parent Row Group.

In the Tablix group window select the Add group header checkbox and use the following expression:

=Lookup(Fields!Student_id.Value,
Fields!Student_id.Value,Fields!StudentName.Value,"DataSet21")

Delete the first column created by the previous grouping setting.

In the cell above Exam use the following expression:

="Student Name: " &
Lookup(Fields!Student_id.Value,Fields!Student_id.Value,Fields!StudentName.Value,"FirstDataSet")

Now select Exam and Note row and add a row above outside the group.

Type Exam and Note in the corresponding cell above [Exam] and [Note] fields.

Select the three cells in the first row, right click it and select Merge Cells.

It will preview something like this:

If you want to delete the first blank row, you can do it smoothly.

Let me know if this helps.

@YounesAKIL , if my answer solved your issue you can select it as correct by clicking on the left side in the correct symbol.[How does accepting an answer works](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) — alejandro zuleta, May 03 '16 at 12:36

score 0 · Answer 2 · answered Apr 26 '16 at 08:59

0

Thank You Mr alejandro zuleta

but i want the result like this in the same Tablix (using groupin By Name)

Studant Name:Jack Luis

Exam   | Note
Java   | 15
Php    | 14


Studant Name:Adam Bob

Exam   | Note
Java   | 12
Php    | 13

answered Apr 26 '16 at 08:59

Younes AKIL

11
1
3

This is not an answer you should delete it and edit your question to include this. Check my update. – alejandro zuleta Apr 26 '16 at 15:01

SSRS: Using tablix with multiple Dataset

2 Answers2