Step 2: Create new Shared Data Source and Report Server Project
Create a new Report Server Project in the Business Intelligence Development Studio (BIDS). First step of configuring the SSRS report is creating the new shared data source. Select the SharePoint List as a data source type and specify the SharePoint Site URL hosting the lists for the connection string.
On the credentials tab, specify either windows authentication or no authentication if anonymous authentication is enabled on the SharePoint Site. Please do no select any other options. None of the other options would work for the SharePoint List Web Service.
Step 3: Create a new Report.
Next steps would be creating the report specific data source and data set to configure the data for the report. Make sure Report Data pane is available in the designer.
Step 4: Create a new Report Data Source.
Using the Report Data pane, create the new report data source. Specify the Shared Data Source as a report data source.
Step 5: Create a new Report Data Set.
Using the Report Data pane, create the new data set. Specify the Report Data Source as a data source. New Query designer support for the SharePoint Lists as a data source, allows the developers (from the BIDS tool) or end users (from the Report Builder tool), browse through the SharePoint lists, select the specific list, and define the filters and parameters without knowing detailed SOAP or CAML query language. As stated earlier, one of biggest limitations of this approach is SharePoint Lists as a data source doesn’t support selecting data from the multiple lists. One way you can avoid joining multiple lists is define the list relationships and bring the additional fields along with lookup column. For more complex joins, you can create the custom web service. Custom web service requires XML as a data source and specify the SOAP command to retrieve the data from the SharePoint Web Service in the query designer.
Step 6: Validate the fields returned by the Data Set
In the Query designer, you can verify if query is valid and returns the fields.
Optionally, you can click on the fields tab on the data set to see if query returns the data fields contained by the data set.
Step 7: Design the Report and Preview the Report in BIDS
Once you have properly configured the data source, data set, and fields, you can design the report by dragging and dropping the fields on the report designer. In this scenario, we will create a simple tabular report and preview the report to make sure reports renders fine in the BIDS before publishing to the SharePoint.
Step 8: Deploy the Report to the SharePoint
You can use the BIDS or upload the RDL files to the SharePoint Document Libraries directly.
Step 9: Verify the SSRS Report in the SharePoint
You can use Report Viewer or Report Explorer web parts if SSRS is installed in the native mode to consume the SSRS reports in the SharePoint. Alternatively, you can use the SharePoint SSRS integrated mode to create and consume SSRS reports more collaboratively by enabling the SSRS report content types in the document libraries. In this scenario, we have SharePoint Integrated environment and SharePoint document library is enabled to host the SSRS reports.
