Create Master-Detail SSRS report from 2 SharePoint Lists using Report Builder

In this post, I will explain two different approaches to create SSRS report to display data from 2 SharePoint lists. Creating similar report from two tables in SQL database may not be very tricky as one can write a SQL query that fetches relational data from two tables which can be consumed in a single DataSet and then, it’s not hard to create report from single dataSet. However, as the report builder doesn’t allow you to store two SharePoint lists in single dataset and also as using join in CAML query in SharePoint is very cumbersome and not flexible, one would prefer to have two different datasets, one for each SharePoint List. Now the challenge is to display data from two different datasets which we will try to accomplish using the following approaches. Let’s jump straight in and look at the data structure first. For this post, consider the two SharePoint lists as shown below.

Orders                                                                                                             

screen1

Products

screen2

Approach 1 – Use LookupSet function to get data from another DataSet

This approach is easy to implement but it doesn’t provide you enough control to display the child items. You can display child elements which are comma separated, in different lines or a tab space between them to make them look more readable but when you export the report to excel, this formatting is not of much help as with this approach you cant display each child item in different cell. If it’s not in a different cell in excel then it doesn’t let you do any calculation in excel. This approach can still be helpful if you just want to display child items and not to do any calculation on them in excel.

Step 1 – Create two different DataSets in Report, one each for “Orders” and “Products” list. Please note in this post, I won’t go in details about how to create a DataSet in SSSRS report or add table or tablix etc. just to make sure we just stick to the purpose of this post.

screen3

Step 2 – Add a textbox in table or tablix and set the following expression as a value of the text box.
                      =Join(LookupSet(Fields!ID.Value,Fields!OrderId.Value,Fields!Product_Name.Value,”Products”),VbCrLf)

Here,
Fields!ID.Value –  a primary key field in the “Orders” list
Fields!OrderId.Value- it’s the field in “Products” list that stores the “ID” field value from “Orders” list.
Fields!Product_Name.Value – it’s the field in the “Products” list to be displayed in the report.
“Products”- Name of the dataset that is storing child items which is “Products” in this case.
Please note for this function to work, ID field in Orders list and OrderId field in Products SharePoint lists should have the same field type i.e. number.

screen4

Finally, run the report and it should look like this –

screen5

Approach 2 – Use SubReport to display child items

This approach needs a little bit of more work than approach 1 but it is more flexible in terms of allowing you to display child elements the way you want which allows you to display them in different cells to enable calculation on them after exported to excel.

Briefly summarizing, we first need to create a separate report for Products that should be able to display products based on a parameter OrderId. We will then use this report as a subreport in a main Orders report. Subreport would then be configured to use it’s OrderId parameter which it will get from Main report. Let’s start !

Step 1 – Create Products report
Create a report to display Products. This report should display products based on the OrderId passed to it. So, create a parameter named “OrderId” of type integer as shown below –

screen6

Next step is to add a filter on the “Products” dataset and make it looks like this – here [@OrderId] is the parameter “OrderId”.
screen7

Save and run the report. It should look like this. Just manually pass some OrderId in the input box to see the outcome.
screen8

Step 2- Create Main Report to display Orders and corresponding Products
   Now, let’s create a main report that will display Orders and its corresponding products. In this report we will use Products report (created in the above step) as a subreport in the table cell.

  1. Firstly, make sure you create 3 DataSets i.e. Orders and Products from Orders and Products SharePoint lists respectively. Create a third DataSet name “LookupOrders” from Orders SharePoint list which we will use as a lookup in the “Id” parameter to be created in next step.
    screen9
  2. Create a multi-value parameter named “Id” of type integer. This will allow user to select the one or more Orders to be displayed in the report. It will use “LookupOrders” DataSet to populate the parameter dropdown with Orders.
    screen10screen11
  3. Next, we need to add a filter on the ‘”Orders” DataSet so that main report displays only the Orders selected by the user from the “Id” parameter dropdown.
    screen16
  4. Make sure report have a table with one row and one column. To add a Products sub report, right click on the textbox in the cell and select Insert Row -> Inside Group below. This will add another row with one cell. In this cell, insert a control “SubReport”. It is important to select “Inside Group below” so that subreport added in the row displays product corresponding to the Order in the row above. But just this is not enough, we also need to add a parameter to the subreport on which the subreport will filter Products as shown in next step.

screen12screen13

5. Right click on the subreport and click on Subreport Properties. Browse to the Products report created earlier. From the Subreport properties dialog, add a parameter to this subreport as shown below. The value of this parameter would be =Fields!ID.Value which is the ID field from “Orders” dataset.
screen14

screen15

Finally, just Save and run the report which should look like this –
screen17

screen18

That’s all!

If you liked this post or it helped you then please don’t forget to leave your comments.

Leave a Comment

Your email address will not be published. Required fields are marked *