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.
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.
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.
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. 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:
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
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.
Product Category Jan Sales Feb Sales Mar Sales Electronics 1000 1200 1500 Clothing 500 600 - Home Goods 750 900 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:
Let's implement the above example while understanding to create a Pivot Report.
Create 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.
|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 depends 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 options 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||This option allows the you to specify the number of decimal places that should be displayed for all numbers in the pivot report. Even if a number has more decimal places than the defined format, it will still be displayed according to the configured number of decimal places. This ensures that all numbers in the report are consistently formatted and displayed in a standardized manner.|
|12||Save Report||This options 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.