• Part 1 – Introduction & Building the Back-End Reporting Database
• Part 2 – Developing SSIS Package to Import Data from Sharepoint List to Reporting Database
• Part 3 – Setup SQL Server Agent Job to periodically execute the SSIS Package
• Part 4 – Building Reports and Publish on Sharepoint using Excel Services

This part of the blog series will build a sample report using Excel PivotTable/Charts and publish the report on sharepoint using Excel Services.

Firstly, we need create an Office Data Connection File which defines the properties of the connection to the reporting database. We can create the connection file using ‘From Data Connection Wizard’ in Excel.

The option ‘Always attempt to use this file to refresh data’ need to be ticked. Also we need set the Authentication properties for Excel Services. If you have not configured a Secure Store Service (SSS) application for the connection, you need create one in the Central Administration site. You can find more details on how to create SSS application in this Blog.

Also, we need configure the Connection Properties. On the ‘Usage’ tab, enable ‘Refresh data when opening the file’.

And on the ‘Definition’ tab, change the Command type as SQL‘, and set the Command Text as the SQL statements for retrieving the data from reporting database.

When all the required properties have been configured, we can export the connection file to a Sharepoint Connection Library. If you have not created this library, you need create one and set the library as Trusted on the Central Administration site. After you export the connection file to the Sharepoint connection library, the path of the ‘Connection File’ property on ‘Definition’ tab would automatically updated.

After the data connection file has been created and deployed on a Sharepoint Connection Library, we can then create reports which will consume the data in the reporting database through the data connection file.

We could insert a PivotTable/Chart into a spreadsheet and set it to use an external data source (points to the data connection file we have created)

Below is a sample report built using PivotTable/Charts with the data from reporting database.

Also we need set the excel file to refresh data from reporting database whenever the file is opened.

After we have published the excel report onto sharepoint site, we then can browse it through web browser.

• SSIS/SSAS/SSRS/MDX