In this lesson you will learn how to create and manage a pivot table in a report. Pivot tables are an interactive way to quickly summarize large amounts of data. They are particularly useful for quickly drilling down into data.
The data in pivot tables can be filtered using your panels of slicers or by manually filtering any of the fields in the data.
We will be using the AdventureWorksDW.ndb database in this tutorial.
Load the AdventureWorksDW.ndb
Open the Vizubi database called “Vizubi AdventureWorksDW.ndb” saved at the end of the Vizubi AdventureWorks tutorial 2: create a report. Or you can download it from here.
Open Excel.
(1) Click on the Vizubi tab.
(2) Click on the Open button in the Database group of the ribbon and open the .ndb file.
Create a new slicer and group
Suppose you want a report of sales by country in each quarter with details of product categories and subcategories.
Before creating the report, you need to built a new group:
1) Click on Edit to open the Vizubi Editor.
2) Click on Slicer in the Navigation pane.
3) Click on the New Slicer button. The New Slicer window opens.
4) Use ProducDetails for the name and Product Details for the Label.
5) Leave the flag on the Create Group box checked so that you create both a slicer and a group.
6) Click on the Add button to add fields to the slicer.
Add fields to the new group
1) CTRL+Click on EnglishProductCategoryName and EnglishProductSubcategoryName and EnglishProductName.
2) Click OK to confirm the selection.
3) Click on Save and Close to save the slicer.
You have created a new slicer and group to use in your report. For more details about slicers and groups see the following tutorials:
How to create a Slicer
How to use groups with your reports
4) Click on Switch to Workbook to go back to Excel.
1) Use the Move up and Move down buttons to order fields in the slicer as the following sequence:
- EnglishProductCategoryName
- EnglishProductSubcategoryName
- EnglishProductName
The fields appear in the group in this order creating a relationship “one to many”: showing data from a general level (Category) to a detailed level of information (Product).
Create a new report
1) Click on the Report button in the Reports group.
2) Select Create new report…
Choose where to save the report.
Insert fields in the report
Now you can create your pivot table:
1) Flag the box on the left of the EnglishCountryRegionName field to put it in the Row Labels pane.
2) Flag the box on the left of the SalesAmount field to put it in the Values pane.
3) Flag the box on the left of the Quarter field and then drag and drop it from the Row Labels pane to the Column Labels pane.
A pivot table appears showing sales by countries in the four quarters.
4) The Grand Total row shows the sum of sales for each quarter.
5) The Grand Total column shows the sum of sales each country.
Add a group to the report
To have more details about product sales in the countries you have to add the group you created previously:
1) Drag and drop the Product Details group in the Row Labels pane.
2) Product category sales are shown for each country; there are three categories Accessories, Bikes and Clothing.
3) Use the button on the left of the country name to collapse or expand the menu.
4) When the menu is collapsed, it shows the total sales for the country for each quarter.
Use the group in the pivot table to cycle and filter
If you double click on Bikes, Vizubi will filter your data by the value Bikes and, at the same time, cycle to the next field in the group, which in this case is product subcategory.
1) In our example, every country now shows sales for the Bikes category split out by subcategory: Mountain Bikes, Road Bikes and Touring Bikes.
2) Now double click on Mountain Bikes.
Cycle and filter again: more drill down
1) Just like when you double-clicked on Bikes, when you double-clicked on Mountain Bikes Vizubi filtered sales by this subcategory and cycled to the next field in the group which is products. Now the report shows sales of all Mountain Bikes split out by individual products (for every country in the report).
2) Click on Clear to remove the filter.
3) To go straight to a different field in the group without filtering, click on the bottom part of the Fields button and select one of the fields in the group. Try clicking on EnglishProductCategoryName.
Save the report
1) Click on Save As in the Reports group of the ribbon.
2) Insert SalesDetailbyCountryandProduct for the Name and Detail of sales by country and product for the Label.
3) Click on OK to confirm.
If you double click on Bikes Vizubi will filter your data by the value Bikes and at the same time cycle to the next field in the group which in this case is product subcategory.
1) In our example, every country now shows sales for the Bikes category split out by subcategory: Mountain Bikes, Road Bikes and Touring Bikes.
2) Now double click on Mountain Bikes.
Cycle and filter again: more drill down
1) Just like when you double clicked on Bikes, when you double clicked on Mountain Bikes Vizubi filtered sales by this subcategory and cycle to the next field in the group which is products. Now the report shows sales of all Mountain Bikes split out by individual products (for every country in the report).
2) Click on Clear to remove the filter.
3) To go straight to a different field in the group without filtering, click on the bottom part of the Fields button and select one of the fields in the group. Try clicking on EnglishProductCategoryName.
Save the report
1) Click on Save As in the Reports group of the ribbon.
2) Insert SalesDetailbyCountryandProduct for the Name and Detail of sales by country and product for the Label.
3) Click on OK to confirm.
Stay in Touch