Pivot tables define the dataset and fields that may be pulled into a pivot report. After you define the pivot table, you can create one or more pivot report layouts and share them others in your company. .
In the pivot report, you can reorganize and summarize that data in specific ways, enabling you to drill into and extract important analysis from large data sets. For example, you can easily drill down to the top selling products by product line and vendor over a single year. With a flat report, you would have to scan pages of reports to manually gather than information.
All pivot reports are made up of specific pieces of data:
- Measures: Sales, profits, units, etc, are all measurable, numerical data. This data always moves into the Values area of a pivot report. The measure will always be in a column.
- Categories: Categories are groups of data that are listed by row. This data represents how you want to Measures broken down. For example, you can look at categories and subcategories should as Products by Product Line by specifying them in the Rows area by broadest category (Product Line) first, and then working down (Product).
- Filter data: Filter data is optional. If you want to filter by date, you might drag a date field into the Columns area. You an also drag a filter field directly into Filters area and you will be prompted to filter the data.
The remainder of this procedure describes how to use an existing pivot table to create a pivot report
- Go to Analytics > Pivot Views > Pivot Table Explorer.
- Click the + to expand the type of report you want to work with, and then click the table name.
- On the Warning message that appears, click OK.
- The Table opens in the Editor view, where you can modify the layout and build the report. If you have already built a report layout or you are opening a saved layout, this layout will open by default.
Available fields populate in the Cube Structure. To create your report layout, you will drag the data from this structure into the Values, Rows area, and Columns area panels.
-
First add the numeric data that you want to measure to the report. in the Cube Structure, expand Measures. Click and drag a data value such as Extended Sales down to the Values panel.
Tip: Regardless of what you are measuring, the Values panel should always contain fields from the Measures group of numerical data.
The measure you added appears in the Columns area of the report.
- If you have multiple measures, you can rearrange them by clicking and dragging them to a new position in the Columns area.
- If you want to filter the value, for example, Sales more than 100,000, click the arrow to the right of the field, and then specify your condition.
-
Now drill down to get more specific. Any data that you want to group on goes into the Rows area, broadest categories first. For example, from the Cube Structure, drag Product Line Code to the Rows area.
- Your report still shows the column with Extended Sales, but now it is broken down by individual Product Lines in each row.
- To further drill down into sales, you can view Sales for Product by Product Line. Drag Product from the Cube Structure into the Rows area, beneath Product Line. Rearrange your rows by clicking and dragging them up and down.
- Your report still shows the column with Extended Sales, but now it is broken down by individual Product Lines in each row.
- Your report still shows the column with Extended Sales, but now it is broken down by individual Product Lines in each row.
-
You can also break down the report by a time frame. For example, from the Cube Structure, expand a date field such as Invoice Date and then drag Invoice Year into the Columns area .
All the data displayed is for current year plus the previous three years. To see only last year's data, you'll need to filter it.
Click the arrow beside Invoice Year. In the Hierarchy Editor that appears, clear the check boxes beside the years you want to hide.
Notice that the Filters area at the top of the report shows your displays your Invoice Year filter.
- To filter data, you can click the arrow on the data field, as described in the steps above, or you can drag a data field in the Filters area at the top of the report and be prompted immediately to select your filters.
- To sort the data, click the column headers.
- To remove data from the report layout, right-click the data field and choose Remove to tree.
- To save the report layout, go to Layout > Save as, and type a name into the Save As pop up window that appears. Anyone with adequate permission in your organization will now be able to open this report layout from the Report Explorer.
