This is a guest post by Anjani Ranjan, one of my coworkers. He and I have been working on a data management solution for a customer for the last six months, and I’m pleased to have him share this process.  Anjani will be demonstrating this technique as part of a presentation, he, Bill Lee, and I are giving at Cloud Saturday on 1/24/2015 on building a SQL Cluster in Azure.

---

Step 1.

Open Excel 2013 and click on the PowerPivot button and click on Manage button on the ribbon. If you don’t see PowerPivot on your Ribbon, read this.

 

Step 2.

Click on the From Database button.

 

Step 3.

Enter the SQL Server details and test the connection. Make sure you select the checkbox to Save Password.

Consider using an encrypted connection – see this TechNet Article.

 

Step 4.

Select the option as shown below and click Next.

 

Step 5

You will get a box to enter a SQL Query as shown below and click Finish.

 

SELECT s.BusinessEntityID,

s.Name AS Store,

ct.Name AS Title,

p.FirstName,

p.LastName,

ph.PhoneNumber

FROM Sales.Store AS s

INNER JOIN    Person.BusinessEntityContact AS bec

ON bec.BusinessEntityID = s.BusinessEntityID

INNER JOIN    Person.ContactType AS ct

ON ct.ContactTypeID = bec.ContactTypeID

INNER JOIN  Person.Person AS p

ON p.BusinessEntityID = bec.PersonID

INNER JOIN    Person.PersonPhone AS ph

ON p.BusinessEntityID = ph.BusinessEntityID

 

Step 6

A dialog will appear which shows the number of rows imported. Click on the Close button.

Step 7

After clicking the close button, the data model will be created on the sheet.

 Step 8

Select Pivot Table from the Ribbon.

 Step 9

You can select existing sheet or new sheet. I have selected the new sheet option. Once selected you will see a Pivot table design view with the Query window on the right.

Select all the required columns you want to show in the report.

Step 10

Right click on the area and select the Pivot Table Options and select/unselect the options as shown below.

Also, note the Pivot Table name. We are going to use this in the Excel Web Access WebPart.

Here it is “PivotTable2”:

 

Step 11

Select each column one by one and click on Field Option from the ribbon and set the radio button to None as below.

 

 

Step 12

Now you have the PowerPivot report as below.

You can add a filter to the report. I will just drag the Title column to the filter.

 

Step 13

Upload the Excel work book to a SharePoint Online document library.

 

Step 14

Create a new page where you want to have your report displayed. Add the Excel Web Access WebPart.

 

Step 15

Edit the Web Part and point to the Excel Work Book uploaded previously.

Also, enter the name of the Pivot Table so that only the Pivot Table is displayed in the report instead of the whole excel sheet.

 

 

Step 16.

Click ok, and click on the Stop Editing button on the top left of the page.

The report should display as shown below.

---

About the guest author: