piromanas

Build a Report with Reporting Services (SSRS) from SharePoint list which uses Nintex Form with repeating section

Discussion created by piromanas on Oct 12, 2015

Hi everyone,

 

Not so long ago i was asked to build a report from List which used Nintex Froms with repeating section. I thought that it is almost impossible until i found few blog posts about using XML as sub-report  data source, so i will try to show you all how simple and how powerful it can be.

 

1. Create a SharePoint list with Nintex Forms with repeating section. In my case i was creating Welding certificates list with information about welder and his techniques.

As you see the table below which i call WeldingProcessTable is repeating section.

2. Create a Report with Reporting Services (SSRS) i use 2008 version.

There is data source to sharepoint list and you can see a subreport in last column of the table. This subreport will point to the repeating section report which we will build in next step.

 

3. Now i am building a subreport called WeldingProcessTable (Just add new report item to the project):

Now for the subreport create Data source with type XML: and save it.

Now we need to add a dateset. The question which i asked my self in first place was How do i create those fields in dataset. There are 2 ways.

a) Doing it by adding all fields one by one:

Or

b) Doing it in good IT lazy fashion Copy XML data from your repeating section column. But to be able to do that your Repeating section must be connected with list column of type Multiple lines of text. Then add this column to your view and you will be able to see XML of your repeating section:

So when you copy this XML of your repeating section add it to that data set query (<Query><XmlData><Root>{Your Repeating section XML}</Root></XmlData></Query>) and fields will be auto populated.

Next click fx button nect to the query input and replace XML with this text:

="<Query><XmlData><Root>"&Parameters!XMLData.Value &"</Root></XmlData></Query>"

 

Now create Parameter with name "XMLData", and you almost there.

 

What have you done is you created subreport with dynamic data source which will get data from parameter XMLData.

 

4. Edit subreport properties in main report:

Give a name and set a report as subreport like in picture above.

Then go to Parameters tab and add parameter.

In the value field select your Multiple line text field which is connected to your repeating section.

And you almost Done.

 

5. Last thing which took me some time to realize is when you will publish your project to SharePoint you will need to go to subreport edit his embeded datasource and set custom windows user for authentication.

Done. I hope this will help you saving some time

Outcomes