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

SharePoint 2013 : How to enable PowerPivot Excel Plugin for Excel 2013

While working with SharePoint 2013 BI Stuff you must have come across an essential component called “PowerPivot Reports”.

On the same lines it is worth to know about an Excel Plugin called “PowerPivot Excel Plugin” that further complements the designing of PowerPivot Style Reports using diversified range of Data Sources.

In this article we will see how can we enable “PowerPivot Excel Plugin” for Excel 2013 and in subsequent articles we will also see how to leverage this Plugin while designing reports based on different data sources like SSAS Cube, SQL Server and so on.

  • Launch Excel 2013
  • Select Blank Workbook

1

  • Select Options

2

  • Select Add-Ins
  • Select COM Add-Ins
  • Click in on the Go… Button to look up all the available plugins

3

  •  Out of the available plugins look for “Microsoft Office PowerPivot for Excel 2013” and Select it
  • Click OK

4

Once we have done with all the steps above, we can see a new Tab added to the Excel Client by the name “POWERPIVOT”

5

When you click on this Tab, you can see extended Functions offered by this Plugin.

6

We will revisit this Plugin again in one of my upcoming articles and see it in action.

Hope you find it helpful.

SharePoint 2013 : How to Configure Data Connection with SSAS Cubes using Excel Services

In this article we will explore the process of consuming data present in Data Cube hosted on SQL Server Analysis Server using Excel Services.

Before we move any further we need to verify the following prerequisites to support this demo:

  • Secure Store Service Application should be created and configured properly
  • Required Application ID should be created in Secure Store Service that can allow authentication for Excel Service over SQL Server Databases
  • Excel Service Application should be created and configured properly

There must be atleast one Data Cube hosted on SQL Server Analysis Server which is having data to consume

Before proceeding any further we need to ensure that all the prerequisites are in place-

  • Validate Secure Store Service & Application ID

 Go to Central Admin => Manage Service Application

1

Click on “Secure Store Service”

2

Check for Application ID that we make use of for Excel Services.

3

In this case we have created Application ID by the name “Excel Services”

  • Validate Excel Service Application

Now Go to Central Admin => Manage Services on Server

4

Look for “Excel Calculation Services” and make sure it is also in started mode

5

Again Go to Central Admin = > Manage Service Application

6

Look for “Excel Services” and make sure that it is in Started mode

Click on Excel Services Application

7

Then click on Global Settings

8

Scroll Down to the bottom and look for Target Application ID, it should be set to the same Secure Store Application ID (Excel Services) that we created in earlier steps.

 9

  • Validate SQL Server Analysis Server

 Login to SQL Server Analysis Server instance and make sure that we have atleast one Data Cube hosted on SQL Server Analysis Server that can serve the data to Excel Service.

A valid Data Cube should look like as shown below:

10

Demo:

Launch Excel Client Application and choose Blank Workbook

11

12

Under Data Tab, Select “From Other Sources” then select “From Analysis Service”

13

On the Data Connection Wizard Dialog, Enter SQL Server Analysis Server Instance Name

Click Next

14

On the next screen of Data Connection Wizard,

Choose the required Cube

Click Next

15

On the next screen of Data Connection Wizard,

Click on Authentication Settings

16

On Excel Services Authentication Settings Dialog,

Choose None and Click OK

17

On the Data Connection Wizard,

Click Finish to end the Connection Wizard

18

On the Import Data Dialog,

Choose any option of your liking, here we are choosing “PivotTable Report”

19

Choose Pivot Table Fields that suits the report you needed

20

In this report we are presenting Internet Sales that can be filtered for State Wise

So Select “Internet Sales Amount” as aggregator that shows the sum of Values in “Internet Sales Amount” Column

21

In order to filter the values we can add Slicers that can help to filter out the records based on the Column which is selected as Slicer

From the Top Ribbon, click on Insert Slicer

22

On the Insert Slicer Dialog, select State Province and click OK

23

Now we can select any value of State Province from Slicer and can see the filtered value of Internet Sales Amount corresponding to the selected State Province

24

25

Now let’s save the Sheet to SharePoint

Go to File Menu => Save As

Choose SharePoint Document Library

Specify the name of the File and click Save

26

Now Browse SharePoint Document Library

27

Click on file to render it in Browser using Excel Services

Click Yes on “Query and Refresh Data” Pop Up, this option allows the sheet to re-query the updated data based on the connection we set-up with in the Excel and ensure data freshness all the time

28

Hope you find this helpful.

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

In this article we will explore the steps of consuming data present in SQL Server Database using Excel Services.

To ensure smooth execution of this demo we should ensure the following prerequisites to be in place-

Prerequisites:

  • Secure Store Service Application should be created and configured properly
  • Required Application ID should be created in Secure Store Service that can allow authentication for Excel Service over SQL Server Databases
  • Excel Service Application should be created and configured properly
  • There must be atleast one database present in SQL Server which is having data to consume

Before proceeding any further we need to ensure that all the prerequisites are in place-

  • Validate Secure Store Service & Application ID

 Go to Central Admin => Manage Service Application

1

Click on “Secure Store Service”

2

Check for Application ID that we make use of for Excel Services.

In this case we have created Application ID by the name “Excel Services”

3

  • Validate Excel Service Application

Now Go to Central Admin => Manage Services on Server

4

Look for “Excel Calculation Services” and make sure it is also in started mode

5

Again Go to Central Admin = > Manage Service Application

6

Look for “Excel Services Application” and make sure that it is in Started mode

Click on Excel Services Application

7

Then click on Global Settings

8

Scroll Down to the bottom and look for Target Application ID, it should be set to the same Secure Store Application ID (Excel Services) that we created in earlier steps.

9

  • Validate SQL Server Database

Login to SQL Server Database instance and make sure that we have atleast one SQL Database that can serve the data to Excel Service. It is better to prefer any database is not in use by SharePoint for Content or Service Applications or Configuration

10

Once we are done with the Validation of Prerequisites we can proceed with the demo on SQL Data Access via Excel Services.

Demo:

Launch Excel Client Application

11

Choose Blank Workbook

12

Under Data Tab, Click on Connections

13

In the Workbook Connection Dialog, Click on Add

14

In the Existing Connections Dialog, Click on Browse for More…

15

In the Select Data Source Dialog, Select +NewSQLServerConnection and click on Open

16

In the Data Connection Wizard, enter SQL Server Instance name and click Next

17

Select Database and the corresponding list from the database and click Next

18

Click on Authentication Settings…

19

In the Excel Services Authentication Settings Dialog, Select None and click Ok

20

In Data Connection Wizard click Finish

21

In Workbook Connections Dialog, Click Close

22

In the Data Tab, click on Existing Connection

23

In the Existing Connections choose connection that we have just created and click Open

24

In the Import Data Dialog, choose any option of your liking to render the data, here we are selecting Table as mode of Data Rendering in Excel

25

And if connection is successful, we can see the data imported into the Excel Workbook from SQL Server

26

Now save this Excel Sheet to SharePoint

Go To File Menu => Save As => Choose SharePoint

27

Select any Document Library of your choice to save the file to and click Save

28

Now Launch the Browser

Browse the Document Library to see the document we just saved

29

Click on the document to get it rendered with in Web Browser using Excel Services

30

Select Yes if as asked for Data Refresh

31

32

And sure enough we will get the updated data from SQL Server directly landing into this Excel Workbook

33

Similarly we can render the Chart View for the same data and get it refreshed from SQL Database using Excel Services as shown below

34

So we can see how simple it is to connect Excel Workbooks with SQL Data Source and get them refreshed from within the Web Browser using Excel Services.

Hope you find it helpful.

SharePoint 2010 References

SharePoint 2010 Installation

  1. Managing and Implementing Microsoft SharePoint 2010 Projects
  2. Practical SharePoint 2010 Information Architecture

SharePoint 2010 Architecture & Governance

  1. Pro SharePoint 2010 Disaster Recovery and High Availability
  2. Pro SharePoint 2010 Governance

SharePoint 2010 Project Management

  1. SharePoint 2010 for Project Management, 2nd Edition
  2. Pro Project Management with SharePoint 2010

SharePoint 2010 Development

  1. Using Microsoft InfoPath 2010 with Microsoft SharePoint 2010 Step by Step
  2. Professional SharePoint 2010 Development
  3. SharePoint 2010 Development with Silverlight
  4. SharePoint 2010 Web Parts in Action
  5. Beginning SharePoint 2010
  6. Microsoft SharePoint 2010 Enterprise Applications on Windows Phone 7
  7. Inside Microsoft SharePoint 2010
  8. Microsoft SharePoint Foundation 2010 Inside Out

SharePoint 2010 Designer

  1. Pro SharePoint Designer 2010
  2. Beginning SharePoint Designer 2010
  3. Microsoft SharePoint Designer 2010 Step by Step

SharePoint 2010 Cloud Based Solution

  1. Developing Microsoft SharePoint Applications Using Windows Azure
  2. Professional SharePoint 2010 Cloud-Based Solutions
  3. Pro SharePoint 2010 Development for Office 365

SharePoint 2010 Search

  1. Pro SharePoint 2010 Search
  2. Working with Microsoft FAST Search Server 2010 for SharePoint

SharePoint 2010 PowerShell

  1. Microsoft SharePoint 2010 and Windows PowerShell 2.0

SharePoint Best Practices

  1. Expert SharePoint 2010 Practices

SharePoint 2010 Business Intelligence

  1. Professional Business Connectivity Services in SharePoint 2010
  2. Microsoft SharePoint 2010 Business Application Blueprints
  3. Microsoft SharePoint 2010 Business Intelligence Unleashed
  4. Microsoft SharePoint 2010 PerformancePoint Services Unleashed

SharePoint 2010 Branding

  1. Professional SharePoint 2010 Branding and User Interface Design
  2. Microsoft SharePoint 2010 Administration Cookbook

SharePoint 2010 Administration

  1. Pro SharePoint 2010 Administration
  2. SharePoint Server 2010 Administration 24 Hour Trainer

SharePoint 2010 Enterprise Content Management

  1. SharePoint Server 2010 Enterprise Content Management

SharePoint 2013 User Guides

  1. SharePoint 2010 at Work
  2. SharePoint 2010 Field Guide
  3. SharePoint 2010 Six-in-One
  4. SharePoint 2010 How-To
  5. Microsoft SharePoint Foundation 2010 Step by Step
  6. SharePoint 2010 All-in-One For Dummies
  7. Microsoft SharePoint Server 2010 Bible

SharePoint 2013 References

SharePoint 2013 Installation

  1. SharePoint 2013 Developer’s Installation Guide – LinkTech
  2. SharePoint 2013 Deployment Guide

SharePoint 2013 Architecture & Governance

  1. Microsoft SharePoint 2013: Designing and Architecting Solutions
  2. Microsoft SharePoint 2013: Planning for Adoption and Governance

SharePoint 2013 Development

  1. Beginning SharePoint 2013 Development
  2. Professional SharePoint 2013 Development
  3. SharePoint 2013 For Dummies
  4. Exploring Microsoft SharePoint 2013
  5. Microsoft SharePoint 2013 Developer Reference
  6. Microsoft SharePoint 2013 Inside Out
  7. Inside Microsoft SharePoint 2013
  8. Exam Ref 70-332: Advanced Solutions of Microsoft SharePoint Server 2013
  9. Exam Ref 70-331: Core Solutions of Microsoft SharePoint Server 2013
  10. QuickBooks 2013: The Missing Manual
  11. Custom SharePoint Solutions with HTML and JavaScript
  12. Microsoft SQL Server 2014 Business Intelligence Development
  13. Professional Visual Studio 2013
  14. QuickBooks 2013: The Missing Manual

SharePoint 2013 App Development

  1. Pro SharePoint 2013 App Development
  2. Microsoft SharePoint 2013 App Development

SharePoint 2013 PowerShell

  1. Beginning PowerShell for SharePoint 2013

SharePoint 2013 Business Intelligence

  1. Business Intelligence in Microsoft SharePoint 2013
  2. Pro SharePoint 2013 Business Intelligence Solutions

SharePoint 2013 Branding

  1. Pro SharePoint 2013 Branding and Responsive Web Development
  2. SharePoint 2013 Branding and User Interface Design

SharePoint 2013 Administration

  1. Professional SharePoint 2013 Administration
  2. Pro SharePoint 2013 Administration, 2nd Edition
  3. Microsoft SharePoint 2013 Disaster Recovery Guide
  4. Microsoft SharePoint 2013 Administration Inside Out

SharePoint 2013 Enterprise Content Management

  1. SharePoint 2013 WCM Advanced Cookbook
  2. Practical SharePoint 2013 Enterprise Content Management

SharePoint 2013 User Guides

  1. SharePoint 2013 User’s Guide, 4th Edition