Build a SQL Report from SharePoint Data
Posted by Mike Giuffre on October 10, 2016
Did you know that if you use Microsoft SharePoint, it’s possible to create reports from your SharePoint data? For example, I use SharePoint to keep track of everything related to different projects I’m working on. Maybe I need to pull specific pieces of project data into a nice report for my manager.
I can easily do that with a SQL Report. SQL Reports now includes a Microsoft SharePoint List connection type that you can use as the starting point for any type of report you can think of that requires or is based on data in SharePoint.
First, you need to set up the Data Source of your report. Here, the connection string points to the address of the SharePoint List I want to create my report from. It’s pointing to my Project site in SharePoint. The connection string URL is typically seen in your browser when you are looking at your SharePoint data.
Second, you need to create a Dataset. I had some trouble with this at first but I was incorrect in trying to use a “Table” query type. Use the default “Text” query type instead as below.
You will want to name your dataset and then click the Query Designer button to create the list of fields you’re going to report on.
Next, in the Query Designer window, select any fields you want to report on. This window should show all the Lists available in the site you pointed to in your Data Source. You just need to expand the areas you want the fields available from by clicking on the plus sign next to the list name.
Expanding any List shown should show fields from which you can select to create your report.
Now your Data Source is available to build reports from. We hope this helps unlock your Sharepoint data to extract the value you’ve built into your solution. If you need some assistance getting started, we have expert report writers on staff. Just send us an email to Marketing@Crestwood.com and we can discuss your options.