In this tutorial, you will learn how to create a pivot report that shows your top selling products over the last by year.
- Go to Analytics > Pivot Views > Pivot Table Editor
. - Click New.
- In the Pivot Table box, type a name for your table: Top Selling Products. Press the Tab key.
- From the Dataset list, choose Sales Analysis. This dataset includes sales information for the last 4 years.
- Now choose the data that you want to include in the report. All pivot reports include numeric data, which is grouped under the Measures heading, as well as categorical data. For this report, you will choose to Measure Extended Sales and then group on the Product Line and Products by Invoice Date.
- Under Sales Analysis Fields, expand Measures.
- Drag Extended Sales to the Top Selling Products Fields list. You will know when to drop it because the list will be outlined in red.
- Repeat this step for Extended Profit,
- To see the fields you added, expand Measures in the Top Selling Products List.
- Now add Products: In Sales Analysis Fields, expand Product, and drag Product and Product Line Code over to the Top Selling Fields list.
- Finally, to view the report by year, expand Invoice Summary and drag Invoice Date to the Top Selling Products Fields.
Tip: You could also filter dates from the check boxes at the bottom of the page.
- Click Save.
- You can now build the report layout from your Pivot Table Data. Click Pivot Table.
- On the Warning message that appears, click OK.
- First add the numeric data that you want to measure to the report. in the Cube Structure, expand Measures. Click and drag 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.
Total Extended Sales appears in the Columns area of the report.
-
Now drill down to get more specific. Any data that you want to group on goes into the Rows area, broadest categories first. 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.
- You won't see your Product rows right away. To see them, hover over a Product Line, and you'll see the + to indicate there is more data. Click the + to expand and see products.
- Your final piece of data is time frame. From the Cube Structure, expand 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 sort the data for your top sellers, click the Invoice Year column header.
- 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.
- If you want to perform further manipulations, such as filter to the Top 25, you can export the report,
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.
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.
Go to File > Export > Export, and then choose the format and the location you want to save from the Save As pop-up.
You can export to Excel, PDF, or Image File (TIFF). If you want to make changes, choose Excel. If you want to share the report, select PDF. If you want to embed the report in another document, choose TIFF.
See also