How to Create Excel Reports > Working with PivotCharts

Working with PivotCharts

PivotCharts and PivotTables are similar in that they both allow you to manipulate how your data is presented and analyzed. PivotTables can be the foundation of PivotCharts. If you are not familiar with PivotTables, see Working with PivotTables first.

Without SoftArtisans OfficeWriter, Microsoft SQL Server Reporting Services does not support embedding a PivotChart in a report that can be exported from its Report Manager. Therefore, everytime you export your report from Reporting Services to an Excel format, you need to recreate the PivotChart. If you have many reports, keeping track of how you originally set up the PivotChart can be an intimidating task.

Using OfficeWriter Reporting Services Integration, you can put a PivotChart in your report definition once, and the PivotChart will be refreshed with new data each time the report is executed.

Let's take a look at how you can integrate PivotCharts into your OfficeWriter Designer reports. This example will use the AdventureWorks sample database shipped with SQL Server Reporting Services 2005. We assume you already know how to create a basic report in Excel using Officewriter Designer. If you don't know how to do this, first read Create Your First Excel Report.

Our sample will use a simple query to compare average salary among job titles, gender, and departments, using a chart.

Step 1. Add Data Markers to Your Workbook

1. Open Microsoft Excel and create a new blank workbook.
   
   
2. Click Add Query on the OfficeWriter toolbar.
   
   
3. Follow steps 2-11 of Create a Database Query in Create Your First Excel Report.
   
   
4. In the Add Tables dialog box, find the Employee table in the list and select Add. Click Close.
   
   
5. In Microsoft Query, add the following AdventureWorks tables to the query. All of these tables are part of the HumanResources schema: Employee, EmployeePayHistory, EmployeeDepartmentHistory, and Department.
   
   
6. Add the following columns to the query: Employee.Gender, Employee.Title, Employee.EmployeeID, EmployeePayHistory.Rate, and Department.GroupName



7. In the grid displaying the data, double-click on the Rate column header. In the Column Heading textbox, enter BaseRate, and then click OK. Your query should look like this:
   
   



8. From the File menu, select Return to OfficeWriter Designer.
   
   
9. Using the Insert Field button located on the OfficeWriter toolbar, place all the fields in the worksheet along with a header for each.
   
   
   
   
10. Publish the report.
    
    
11. Click View on the OfficeWriter toolbar to see the populated report.
    
    
12. Click Close Report View to return to the report template.
    
    

Step 2. Create a PivotChart

1. With your mouse, highlight all the data markers and headers that you inserted in the worksheet.
   
   
2. From Excel's Data menu, select PivotTable and PivotChart Report to open the PivotTable and PivotChart Wizard.
   
   
3. Select Microsoft Office Excel list or database from the top section, PivotChart from the bottom section, and click Next.
   
   
4. Since you already highlighted the header and field rows of your report, the Step 2 screen should already contain the cell range to use. Click Next.
   
   
5. Select New Worksheet and click Finish. You should now have an empty PivotChart in your report as shown.
   
   
   
   
6. Right-click in the upper left part of the gray area where it says Drop Data Items Here, and select Chart Type.
   
   
7. Select the chart type Clustered column with a 3-D visual effect.
   
   
   
   
8. From the PivotTable Field List, drag the Title field to the area labeled Drop Category Fields Here.
   
   
9. Drag the Gender field to the Drop Series Fields Here area.
   
   
10. Drag the Department field to Drop Page Fields Here.
    
    
11. Drag the BaseRate field to Drop Data Items Here. Your PivotChart should look like this:
    
    
    
    

For our example, we want to see the average salary, or base rate. However, the default for the PivotChart made the base rate a count, so we must change that.

1. Right-click the label Count of BaseRate and select Format PivotChart Field.
   
   
2. Change Count to Average in the list box and click Ok. It should now say Average of BaseRate.

Step 3. Create Data Placeholders

When the report is executed, the data markers that you created in Step 1 will be replaced with values from the database. To ensure that the PivotChart is constructed properly, you must replace the data markers with placeholder data - any data that will be of the same format as the real output data. For example, if the field is numeric, such as the EmployeeID field, we may use a zero or any other number. If the field is a character field, such as the Title field, we need to use a character placeholder. For our example, we will use the word 'none' for the Title and Gender fields.

1. When Excel created your PivotChart, it also created a PivotTable corresponding to the PivotChart. Find the worksheet containing the PivotTable. It should look like this:
   
   
   
   
2. Under the Title label, replace '=%%Query1.Title' with 'none.'
   
   
3. Under the Gender label, replace '=%%Query1.Gender' with '0'.
   
   

When you insert placeholder data, never use a real value. E.g. if you are displaying a city name, don't use 'Boston' for the placeholder data. The results returned for 'Boston', in that case, may not behave as expected. The same holds true for numeric data. Try to find a value that will never actually be in the query's result set.

Refreshing the Data

There is one more thing to do before trying our PivotChart. Right-click on the pivot table and select Table Options from the list. Near the bottom of the left column, make sure Refresh on Open is checked. If you do not check this, when the report is viewed, your PivotChart and PivotTable will be empty.

Viewing the PivotChart

Publish, and View your report. Notice that on the first sheet, your data columns display the correct data.

Now look at the sheet containing your PivotChart. You should have the Titles across the bottom of the graph but it is not very pretty. It contains all the data. Open the drop-down list of departments and select one. Now you get a clear graphical picture of the data that visually presents the wage differences between males and females by position across departments.

Now, go to the worksheet containing the chart's PivotTable. What do you see? The selections on the PivotTable match those of the chart. If you selected a department on the PivotChart, that same department was selected on the PivotTable. The PivotChart and the PivotTable work hand-in-hand.

Viewing the Report in Reporting Services Report Manager

In your browser, type the path to the Report Manager (Usually http://<YourReportServer>/Reports). Navigate to the report and view it.

To see the report as you designed it with OfficeWriter:

1. From the Select a format drop-down list, choose Excel (.xls) designed by Officewriter.
   
   
2. Click Export.
   
   
3. You will be prompted to save or open. If your report contains a chart or a PivotTable, you MUST select Save. After it saves to disk, then you may select Open to view it.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.