How to Add a Shared Data Source in SharePoint

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.

Requirements:

  • 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)

Fig-1 How to Add a Shared Data Source in SharePoint

Fig 1: Report Builder

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”

Fig-2 How to Add a Shared Data Source in SharePoint

Fig 2: Data Source Properties

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

SharePoint-Shared-Data-Source-Connection How to Add a Shared Data Source in SharePoint

d)     Select “Test Connection” and it should prompt a “Connection created successfully” message (Fig 4)

Fig-4 How to Add a Shared Data Source in SharePoint

Fig 4: Successful Connection

e)      Select “OK”

This will create the “Connection string” in the “Data Source Properties” screen (Fig 5)

Connection-String How to Add a Shared Data Source in SharePoint

Fig 5: Connection String

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”

SSRS-in-SharePoint How to Add a Shared Data Source in SharePoint

Fig 6: Adding new 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.

SSRS-Connection-Details How to Add a Shared Data Source in SharePoint

Fig 7: Data source properties on Share Point

  • 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”
 This is how a shared dataset will appear in SharePoint (as shown in Fig 8 )
Shared-Data-Source-in-SharePoint How to Add a Shared Data Source in SharePoint

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.

If you would like to learn more about our report development process and experience, please feel free to contact us. We will be happy to sit down and discuss your needs.


0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *