Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Using Excel and Creating a Pivot Table Report



We will first create a connection to the cube we have developed in the previous exercises. After connecting the cube we will use the calculated measures and a named set to create a very basic pivot table report. For the purpose of demonstration, Excel 2010 is used and is installed on the development machine, but you can also use Excel 2007 to connect to the cube.


Open Microsoft Excel and select the “Data” tab from the menu ribbon. Click on “From Other Sources” and select “From Analysis Services” option as shown in the below screenshot.

In the next step specify the SSAS server name and logon credentials. If you have everything on the local machine, you can also use “localhost” as the server name.

If you were able to successfully connect to the specified SSAS instance with the logon credentials specified, in the next step you should be able to select the SSAS “Sales” database and find the Sales Cube. Select the Sales Cube and proceed to the next step.

In the next step, specify the name of the connection file to save. This file will be saved as an .ODC file and you can reuse this connection file when you want to use the same connection in other workbooks.

After saving the file, you will be prompted with the option to select the kind of report you want to create. We will go with the default option and select “PivotTable Report”.

After selecting “PivotTable Report”, a designer will open with options to select dimension, attributes and measures to populate your pivot table. Select the values as shown in the below screenshot. Our intention is to display the hierarchy we created in the Sales Territory dimension on the columns axis, Internet Sales Top 25 named set on the rows axis, and the Total Sales calculated measure in the values area.

After making the above selections, your report should look like the below screenshot. Using the features available from the “Options” tab, you can format this report and give it a more professional look. You can try drilling down the hierarchy, but you will see that you need to develop the hierarchies. Users who frequently want to see sales of products to top customers, can pick up any named-set that we defined earlier. Instead of having users define formulas for adding internet sales and reseller sales, users can just select Total Sales.

Last Update: 5/3/2011

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools