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.
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.
Click on the From Database button.
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.
Select the option as shown below and click Next.
You will get a box to enter a SQL Query as shown below and click Finish.
s.Name AS Store,
ct.Name AS Title,
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
A dialog will appear which shows the number of rows imported. Click on the Close button.
After clicking the close button, the data model will be created on the sheet.
Select Pivot Table from the Ribbon.
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.
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”:
Select each column one by one and click on Field Option from the ribbon and set the radio button to None as below.
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.
Upload the Excel work book to a SharePoint Online document library.
Create a new page where you want to have your report displayed. Add the Excel Web Access WebPart.
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.
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:
Anjani Ranjan is a Senior Consultant, Microsoft Technologies at PSC. He has more than two decades of experience in IT and over 7 years of experience in collaboration and business productivity solutions, making him an expert at Microsoft SharePoint (both on-premise and cloud versions). He has worked extensively with Microsoft’s data management and business intelligence products, including SQL Server 2014, SQL Server Reporting Services, SQL Server Integration Services, and PowerPivot and Power Query. Before diving into the SharePoint & Office 365 world, he was a .Net Developer.
Can the data source be refreshed automatically on opening the webpage?
Yes, the data-source can be updated automatically when opening the web page. When creating the data source, there is an option to auto refresh data.