Using SSIS and Excel Services to Build a Lightweight Reporting Solution for SharePoint-Based Applications (Part 4)

Using SSIS and Excel Services to Build a Lightweight Reporting Solution for SharePoint-Based Applications (Part 4)

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.

clip_image001

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.

clip_image002

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

clip_image003

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.

clip_image004

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.

clip_image005

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)

clip_image006

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

clip_image008

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

clip_image009

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

clip_image011

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s