Pivot Report
  • 4 Minutes to read
  • Contributors

    Pivot Report


      Article summary

      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:

      DateProduct CategorySales
      01-01-2022Electronics1000
      01-01-2022Clothing500
      01-01-2022Home Goods750
      01-02-2022Electronics1200
      01-02-2022Clothing600
      01-02-2022Home Goods900
      01-03-2022Electronics1500
      01-03-2022Home Goods1100

      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 CategoryJan SalesFeb SalesMar Sales
      Electronics100012001500
      Clothing500600-
      Home Goods7509001100
      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.

      1 GIF(2)

      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.

      2 GIF

      • 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.

      3

      • Platform will navigate you to the Pivot Report configuration page.

      4(1)

      S.NoOptionDescription
      1Report Type drop-downDisplays the list of report types available in Quixy.
      2Field ListThis is where you arrange the data fields into Rows, Columns, Filters, and Values (define the aggregate).
      5 GIF(2)
      3Show Grand TotalsThese 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).
      6 GIF(2)
      4Expand AllThis 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.
      Pivot Report Pre-Expand GIF
      5Data AccessThis 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.
      6Export SettingsThis option lets you (admin) to download the report in PDF, Excel, CSV files.
      7FiltersThis option lets you filter the data in the pivot report 
      8ChartsThis option provides the options to represent the raw data into various charts to get a visual understanding of the patterns.
      9Back to TablesThis options brings you back from the chart view to table view.
      10Number FormattingThis option allows you to define the decimal places for the numbers  
      11Export OptionsThis 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.
      12Save ReportThis options allows you to save the report.
      • Once the configuration is in place, save the report.

      7 GIF(2)

      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.











      Was this article helpful?

      What's Next