Show SharePoint Document Library files with folders and subfolders in SSRS

In this article, I will explain

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

 

Show Recursive Document Library files with folders and subfolders in SSRS


Scenario

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

Show Recursive SharePoint Document Library files with folders and subfolders in SSRS.gif

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

Show Recursive Document Library files with folders and subfolders in Reporting Service

SSRS report

Show Recursive Document Library files with folders and subfolders in SSRS


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”.

Recursive SharePoint Document Library in SSRS.gif


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.

RecursiveAll SharePoint Document Library files in SSRS


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.

Show SharePoint Document Library in Reporting Service


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.
=Fields!Server_Relative_URL.Value.Substring(1,Fields!Server_Relative_URL.Value.LastIndexOf("/"))

Get File Path without filename in Reporting Service


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)

Show SharePoint Folders and SubFolders and Files in SSRS


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.

Show SharePoint Folders and SubFolders and Files in SSRS 1


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:

Show SharePoint Folders and SubFolders and Files from Document Library in SSRS


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?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s