There is a huge demand for report development and deployment on the Microsoft platform. Here is a quick summary on how to add a shared data source in SharePoint and share it to develop reports. The steps mentioned below are for Microsoft SQL Server, but the same process can be used for multiple data sources.
- Platform: Microsoft
- Collaboration Software: SharePoint
- Report Development Application: Report Builder
Step 1. Open Report Builder
a) Right click on “Data Source” in the left column and select “Add a Data Source” (as shown in Fig 1)
Step 2: Add Data Source Properties by (as in Fig 2)
a. Name: Use a connection embedded in my report
b. Select connection type: “Microsoft SQL Server”
Step 3: Add the connection properties to get the connection string by selecting the “Build” tab and fill in (as in Fig 3)
a) Enter the server name hosting the database.
b) Log on to the server, select – “Use Windows Authentication” or “Use SQL Server Authentication” based on your setup.
c) Connect to the database, select – “Select or enter a database name:”
d) Select “Test Connection” and it should prompt a “Connection created successfully” message (Fig 4)
e) Select “OK”
This will create the “Connection string” in the “Data Source Properties” screen (Fig 5)
Now we have successfully created the “Connection String.” Now, copy the “Connection String” so we can use it in the next step.
Step 4: Adding Data Source to Share Point (as in Fig 6)
a) Go to the location on SharePoint where you would like to create the shared data source
b) Click on the drop-down menu near “New”
c) Select “Report Data Source”
Step 5: Updating the properties in the new data source
Fill in the data source details (Fig. 7). This is where you will paste the Connection String copied in step 3 above.
- Name – type in the data source name
- Data Source Type – select “Microsoft SQL Server”
- Connection String – this has to be copied from the Report Builder Step 3(as shown in Fig. 5)
- Credential – select “Stored credential” or “Windows Authentication (integrated) or SharePoint User” or “Credentials are not required” based on your setup.
- If you select “Stored credential” then
- “Fill in User Name and Password.
- Select “Test Connection” tab.
- If the credentials are correct then the message will says “Connection created successfully.”
- Availability – select check box for “Enable this data source”
- Click “OK”
The benefits of using shared data source are following:
- Sharing the same data source across all reports instead of embedding them individually in each report.
- Changing of data source details (such as server name, login credentials etc.) at one place instead of going to each report and changing manually.
- Creating reports that use multiple data source (eg. Reconciliation reports using data from multiple applications.)
Optimus provides report development services in Vancouver, Canada. We have expertise developing cross-platform and cross-application reports. We specialize in Mircosoft SSRS, Crystal Reports and other open source tools.