In this article, I will explain

How to show a SharePoint Document Library files with folders and subfolders in SSRS recursively

 

---

Scenario

I have a SharePoint Document Library with multiple folders and subfolders as shown below:

I have tried to retrieve all files, folders, and subfolders from this Document Library in Reporting Service (SSRS) report, but unfortunately, it doesn’t recursively read all the subfolders and files, it only read the root folder and files as shown below:

Document Library

SSRS report

---

Solution

To show all folders and subfolders as well as the files that these folders contain from SharePoint document library in SSRS report, you should follow the below steps:

Steps

• Modify the Document Library view to show all items without folders.
• Create a Data source for a SharePoint list/library in SSRS.
• Create a Dataset for a SharePoint data source in SSRS.
• Add a Calculated field to get the full file path without the file name in SSRS.
• Build a SharePoint Library Report in SSRS.
• Group by a “folder path” field in SSRS.
• Display the report for all files in document library grouped by folders and subfolders.

---

Modify the Document Library view to Show all items without folders.

• Open the SharePoint Library in the browser.
• From the above ribbon > Library Tab > Manage Views > Modify View.
• Below Folders section > Check “Show all items without folders”.

---

Create a data source for a SharePoint list/library in SSRS.

• Open the SSRS report via Report Builder.
• Add new Data Source > provide an appropriate name as you prefer.
• In “General” Tab > check “Use a connection embedded in my report“.
• In “Select connection type” > Select “Microsoft SharePoint List“.
• In “Connection string” > Provide the SharePoint site URL that holds the Document Library.
• Check “Use single transaction when processing the queries“.
• In “Credential” Tab > provide the correct credential options as you prefer.
• Click on Ok button.

---

Create a Dataset for a SharePoint data source in SSRS

• Add new dataset > Provide an appropriate name as you prefer.
• Check “Use a dataset embedded in my report“.
• Select the newly created data source.
• Click on “Query Designer” button.
• From the left side “SharePoint Lists” > Select the SharePoint list/library required.
• Click on the “Show Hidden Fields” button to show the hidden fields.
• Go back to the fields list to Select the “Server Relative URL” field.

---

Add a Calculated field to get the full file path without the file name in SSRS.

• Right-click on the newly created dataset > “Dataset properties“.
• Go to “Fields” Tab > Click on Add to add anew “Calculated field“.
• Provide an appropriate name > Add the below formula.

[code language=”HTML”]
=Fields!Server_Relative_URL.Value.Substring(1,Fields!Server_Relative_URL.Value.LastIndexOf("/"))
[/code]

---

Build a SharePoint Library Report in SSRS.

• Insert a new table > From the “Properties‘, set its Dataset name to the current dataset.
• Add the required fields as you prefer. (Note: Don’t add the new calculated field)

---

Group by a folder path field in SSRS

• After adding the required fields, try to group by the new calculated field (“RootPath“) as the following:
  • In “Row Groups” > Click on the arrow of the “Details” section > Add “Parent Group“.
  • Select the “RootPath” field > Check “Add Group header” > Ok.
  • Again, Click on the arrow of the “Details” section > Select “Group properties“.
  • Go to a “Visibility” tab > Select “Hide‘.
  • Check “Display can be toggled by this item” > select the “RootPath” field.

---

Display the report for all files in document library grouped by folders and subfolders

Finally, click on Run button to show all the document library files with folders and subfolders as shown below:

---

Applies To

• SharePoint 2016.
• SharePoint 2013.
• Reporting Service Native and Integrated Mode.

Conclusion

In this article, we have explained How to show a SharePoint Document Library files with folders and subfolders in SSRS recursively?