SSRS - One tablix with 2 Datasets

Question

I am trying to build a report to show projects and subprojects status updates with data for projects and subprojects coming from 2 separate sharepoint lists i.e. datasets in the same data source.

The problem is

That I do not know how to build a lookup function for tablix to pull data from different sharepoint lists.
Tablix needs to have a rule for exceptions: 3 projects have subprojects but the other do not have subprojects.

The expected result is for tablix to display status report in the following way:

status update   next steps
project1            
project2            
project3            
    subproject 3.1      
    subproject 3.2      
    subproject 3.3

I have linked the projects to subprojects using a key.

Advice much appreciated.

Deepak Bhise Deepak Bhise · Accepted Answer · 2017-12-08T17:52:55

In current release of Reporting Services, each tablix can contains only one dataset. So in your scenario, the easiest way to achieve your requirement is combining the two datasets into one using Join in the query if it is possible. If the datasets cannot be combined, there are two ways you can give a try

a. Subreport - create a subreport to display in another report

1.Create another report as the subreport and insert the child row data.

2.Create a parameter named ID in the subreport.

3.In the main report, right-click to insert a subreport in the child row.

4.Right-click the subpeort to open the Subreport Properties, and select the subreport name in the drop-down list.

5.In the left panel of the Subreport Properties dialog box, click Parameters.

6.Select Name in the drop-down list of ID, and select [ID] in the drop-down list of Value.

b. Use 'lookupset' function (more on lookupset function Lookupset Function

SSRS - One tablix with 2 Datasets

1 Answers