A Pivot Report is a way to organize and summarize large amounts of data in a way that makes it easy to understand. It allows you to take a lot of data and rearrange it so that you can see patterns and trends that might not be immediately apparent.
Think of it like rearranging a bunch of puzzle pieces to make a clearer picture. You can group the data by different categories, such as by date, location, or product, and then display the information in a way that makes it easy to analyze.
Example
If you have a large spreadsheet with sales data for a company, you can use a Pivot Report to group the data by month and product type to see which products are selling the most each month.
While configuring the Pivot report, the platform will ask you to arrange the data fields present in the database in four different sections (i.e., Rows, Columns, Filters, and Summation Values) according to which the report will be generated for your ready reference. Let's understand the sections in detail.
1. Filters in Pivot report:
This section allows you to filter the data you want to analyze based on specific criteria. For example, you can filter sales data by date range, region, or product type. This section is useful for narrowing down the data you want to analyze and focusing on specific subsets of the data.
2. Columns:
This section allows you to group and organize data along the vertical axis. For example, you can group sales data by product category or customer type. This section is useful for analyzing trends and patterns within specific groups of data.
3. Rows:
This section allows you to group and organize data along the horizontal axis. For example, you can group sales data by month or year. This section is useful for analyzing trends and patterns over time.
4. Summation values:
This section allows you to analyze the numerical values in your data. For example, you can calculate the sum, average, or count of sales data. This section is useful for summarizing and comparing numerical data.
Example
Here's an example of sales data that we can use to create a Pivot Report:
Date | Product Category | Sales |
01-01-2022 | Electronics | 1000 |
01-01-2022 | Clothing | 500 |
01-01-2022 | Home Goods | 750 |
01-02-2022 | Electronics | 1200 |
01-02-2022 | Clothing | 600 |
01-02-2022 | Home Goods | 900 |
01-03-2022 | Electronics | 1500 |
01-03-2022 | Home Goods | 1100 |
Using this data, we can create a Pivot Report that groups the data by month and product category to see which products are selling the most each month. We can also calculate the total sales for each month and product category.
The Pivot Report might look something like this:
Product Category | Jan Sales | Feb Sales | Mar Sales |
Electronics | 1000 | 1200 | 1500 |
Clothing | 500 | 600 | - |
Home Goods | 750 | 900 | 1100 |
In this example, we can see that Electronics is the highest-selling product category in each month, followed by Home Goods and Clothing. By using the Pivot Report to group and summarize the data, we can quickly identify these trends and patterns.
Let's implement the above example while understanding to create a Pivot Report.
Create Pivot report
Note
It is mandatory to have a data source to create a pivot report.
There are two approaches that you can take to start creating a report.
1. From Dashboard: Go to Admin Menu -> Reports -> Create Report. The platform will ask you to select the data source first and take you to the report creation page where you need to select type (in this case, it will be Pivot), give a name, etc. Follow the below steps to configure the report.
2. From Data Source page: Select the Data source -> Actions -> Create Report. The platform will navigate you to report creation page where you need to select report type (in this case, it will be Pivot) and give a name, etc. Follow the below steps to configure the report.
Once the report type is set to Pivot, give a name to the report. Select the Add-On to attach external Static Columns & Rules on top the report. Click Next.
Platform will navigate you to the Pivot Report configuration page.
S.No | Option | Description |
1 | Report Type drop-down | Displays the list of report types available in Quixy. |
2 | Field List | This is where you arrange the data fields into Rows, Columns, Filters, and Values (define the aggregate). |
3 | Show Grand Totals | These checkboxes when enabled calculates the total of the values available in the rows & columns and provides the result at the end of respective column or row. These calculations depend on the aggregate which is set in the Fieldslist -> Values section (i.e., Sum, Count, Product, Min, Max, etc.). |
4 | Expand All | This option allows the admins who wish to present their end-users with an expanded view of the grouped columns by default. The expanded view will allow end users to absorb data and make informed decisions quickly. |
5 | Data Access | This option lets admins give a specific user access to their own data and restrict access to the data that other users have added to the grid report. Employee Email, Employee Code, and Company Name are My Profile -> User Detail fields. |
6 | Export Settings | This option lets you (admin) to download the report in PDF, Excel, CSV files. |
7 | Filters | This option lets you filter the data in the pivot report |
8 | Charts | This option provides the options to represent the raw data into various charts to get a visual understanding of the patterns. |
9 | Back to Tables | This option brings you back from the chart view to table view. |
10 | Number Formatting | This option allows you to define the decimal places for the numbers |
11 | Export Options | You can export pivot reports in two formats based on your needs:
|
12 | Save Report | This option allows you to save the report. |
Once the configuration is in place, save the report.
If you observe the above GIF, you will notice that we have got the expected result as discussed in the example in the beginning of the article.