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

1

  • Select POWERPIVOT Tab
  • Select Manage

2

  • Click on “Get External Data” Dropdown Ribbon Button

3

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

4

  • Select SQL Server Instance Name

Wait for further processing

5

  • Select the Database Name

6

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

7

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:

8

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

9

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

10

 

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

11

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

12

Provide the Data Range to be consumed by Pivot Table

13

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

We can include different Operations as needed afterwards

14

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

15

  • 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

16

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

17

  • Specify the Caption for the Slicer

18

  • 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

19

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

20

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

And Save it

21

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

22

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

23

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

Hope you find it helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s