SharePoint 2013: How to Configure Data Connection with SQL Server using Excel PowerPivot Plugin

As promised in my previous article on “SharePoint 2013 : How to enable PowerPivot Excel Plugin for Excel 2013” I am here with a new article that shows a demonstration on leveraging Excel PowerPivot Plugin in order to prepare the reports consuming data from SQL Server.

• Launch Excel 2013
• Select Blank Workbook

• Select POWERPIVOT Tab
• Select Manage

• Click on “Get External Data” Dropdown Ribbon Button

There we can see all the Data Sources that can be used to connect with

• Select “From Database” to setup a connection with SQL Server

• Select SQL Server Instance Name

Wait for further processing

• Select the Database Name

• Test the connection
• If the connection Succeed, Click Next

In the next Step you can choose either of the two options

• Select Tables or View from the Look List
• Write down your own Query to execute directly and fetch the result set in the form of table

Though as a part of good practice it would always be a good idea to proceed with option 1 as we are going to do it here after

• Select Option 1 as shown below:

• Select the required Tables from the Look up Window to participate in the read operation
• Then Click Finish

• If all goes fine we can see the Success Screen
• Click Close Button

 

And we can get the selected Tables exported to the Excel, presenting data to be consumed as needed then after.

• Now Choose “PivotTable” from “PivotTable” Dropdown Ribbon Menu

Provide the Data Range to be consumed by Pivot Table

And sure enough you will see the Pivot Table with Fields available for the selection

We can include different Operations as needed afterwards

For instance we can provide Data Slicers to the Users to allow them getting filtered Views of the data as needed

• In order to Add Slicer, click on Analyze Tab
• Then Click Insert Slicer

• Choose a relevant Filter Parameter or Slicer based on which you like to allow Users to Filter the data.

Here we are choosing “StateProvinceName” as Slicer

• Go to Slicer Settings to provide an appropriate name to the Slicer
• Right Click on Slicer
• Select Slicer Settings from the Context Menu

• Specify the Caption for the Slicer

• Once this is all done we would be having a fully functional PowerPivot Report to be published for the Users
• Go To File Menu
• Select Save As
• Click on Browse Button

• Specify the Url of Document Library where you want to Publish this file

• Browse the Document Library
• Specify the Name of the File to be Published

And Save it

• Browse the Document Library from the Browser and see if the file is published properly

• Click on the Report and let it run in Browser

And sure enough we will see the List of Cities with a “State Province Name” Filter (Slicer) in action.

This is no code implementation of quite an effective Reporting Solution backed up by rich PowerPivot Functionality.

Hope you find it helpful.

by Prashant Bansal via Everyone's Blog Posts - SharePoint Community