Creating Summary Report Using Pivot Table

A pivot table is used to generate summary tables from raw data. This feature was introduced in Origin 8.5 but continues to be improved over the years. In this blog, we are going to show some examples of using the pivot table to visualize trends in some public data from the World Bank.

First you will need to download this Origin project that includes the data(world_econ_dev.zip). As you can see in the worksheet, 11 indicators are presented (health expenditure, GDP, population, etc.) and each country has also been classified by region and developing index. We will look at two aspects of the data using pivot tables:

1. Health expenditure by region: In this part we will first show you how to assign column/row category data to create a pivot table. Then we plot the result as a column graph to visualize the total health expenditure by region, from which we can see that Europe is the region that has the highest health expenditure per capita. Next we will examine what happens if we restrict the summary to include developed countries only. To do this, we will apply a data to the developing index column to remove developing countries. Then you will see that actually The Americas has the highest health expenditure per captia.
2. GDP by regions.  In this example, we will show you how to use Change Parameters to modify the settings of a pivot table after it has been created. We will first make a new pivot table to show GDP by regions, then we will go back to enable the Combine Smaller Values feature to list the top 3 regions that have the largest GDP.

Assign Column/Row Category

To look at global health expenditures per capita. we will assign column category as Region, row category as Year, data source as Health expenditure per capita, and summarize by the mean of these values, as illustrated below:

1. Activate the raw data worksheet and click Worksheet: Pivot Table.
2. Assign row category as Year, column category Region, summarize by Mean, and data source as Health: Health expenditure per capita (current US$). Note: To change column/row category, you can click the triangle button of the Column/Row Source in the pivot table dialog and select the desired column/row).
3. In order to automatically update the pivot table when making changes to the data, we should change the Recalculate mode to Auto. You can do so from the dialog, or by right-clicking on the green lock in the result  sheet and choosing Recalculate as Auto.from the popup menu.

Add Graph into Workbook

Next we will plot the resulting pivot table as a column plot, and we will also move the graph into the source data sheet as a floating graph so that when we later add data filters to the input data, we can visualize the changes in the graph more easily.

1. To create the graph, select the whole pivot table worksheet and click Plot:Column/Bar/Pie: Column.
   
2. We want this graph to auto rescale when input data is changed, so double click the graph Y axis to enter the Axis dialog. On the Scale tab, Set Rescale as Auto.
3. To add the column graph onto the raw data sheet, go to Sheet 1, right-click on the workbook window and select Add Graph.
4. Select the column graph in the Graph Browser, click OK to add the graph.

Now the graph has been added into the input data sheet.

Add Data Filters in Input Dataset

This graph we just made showed that Europe has the biggest health expenditure per capita and the fastest growing tendency. Now lets make a better comparison by looking at developed countries only. To do so, we can add a data filter in column Developing Index and the pivot table will only calculate the rows (countries) which fit the filter condition.

To add filters, highlight this column and click Column: Filter: Add or Remove Filter to add the filter
or
click the filter icon on worksheet data toolbar.

To customize the filter, click the filter icon on the column Developing Index, then customize the filter as Developed Country. Click OK to close the filter dialog.
The pivot table will automatically recalculate based on the new filter condition and the graph will be updated. It can now be seen that after stripping out developing countries, The Americas have the highest health expenditure per capita and fastest growing tendency. See the graph comparison before and after adding the filter below:

Combine Smaller Values

In this section, we will look at the GDP data by regions, and show you how to list the top 3 regions that have the largest GDP.

1. We need to first clear the filter we added before, as in this section we need to include all the countries. Go back to Sheet 1 and click the filter icon on the column Developing Index and select Clear Filter.
2. Use Sheet 1 to create a new pivot table. Set row category as Year, column category as Region, data source as Finance:GDP(current US$) , Summarize by Sum, Click OK and Sheet Pivot2 will be created.
   
   A new pivot table is created in Sheet Pivot2.
3. Highlight Sheet Pivot2 and create a column graph, as shown below.
4. The graph above shows all regions of the world, so if we want show just the top 3 regions, we can use the Combine Smaller Values feature. Origin supports Recalculate and allows you to go back to change the settings on a given analysis, in this case the pivot table feature. To open the pivot table settings dialog again, click the green lock and select Change Parameter. This popup menu also allows setting Recalculate to Mode: Auto. 
5. Customize the Combine Direction as Column, Mode as Top N of Grand Total, Top N as 3, as shown below:
   Graph2 will change as a result. The pivot table shows that the Americas, Europe and Asia are the top 3 regions that have the largest combined GDP figure.