{"id":14029,"date":"2018-05-08T09:58:15","date_gmt":"2018-05-08T09:58:15","guid":{"rendered":"https:\/\/kb.sambapos.com\/?p=14029"},"modified":"2020-09-30T13:26:49","modified_gmt":"2020-09-30T13:26:49","slug":"9-1-6-custom-report-pivot-table-views","status":"publish","type":"post","link":"https:\/\/kb.sambapos.com\/en\/9-1-6-custom-report-pivot-table-views\/","title":{"rendered":"9.1.6. Custom Report Pivot Table Views"},"content":{"rendered":"
\n

Pivot tables are useful to create different views of data easily for better analyzing data.<\/p>\n<\/div>\n

Preparing a Sample Report<\/h2>\n
\n

You can create a simple\u00a0Orders<\/code>\u00a0report by using this custom report template.<\/p>\n\n\n\n
[Orders:1,2,1,1]
\n>Item Group|Item|Quantity|Total
\n{REPORT ORDER DETAILS:O.ItemGroup,O.MenuItemName,O.Quantity,O.TotalPrice}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This report will list all orders we’ve placed inside selected date range.<\/p>\n

\"\"<\/p>\n

Converting Report to a Pivot Table<\/h2>\n
\n

Pivot Table is useful to create summary reports. You can convert this report to a\u00a0Pivot Table<\/code>\u00a0by adding\u00a0!<\/code>\u00a0char in front of table name. So\u00a0[Orders:1,2,1,1]<\/code>\u00a0part of the template becomes\u00a0[!Orders:1,2,1,1]<\/code>. When you save your report Custom Report converts to a\u00a0Pivot Table<\/code>.<\/p>\n

Report will display nothing as you need to configure row and value fields.<\/p>\n

\"\"<\/p>\n

A pivot table contains three areas.<\/p>\n

    \n
  • \n
    Rows Area<\/code>: Move fields here which you want to list in the table. In this sample it’ll be listed\u00a0Menu Items.<\/code><\/div>\n<\/li>\n
  • \n
    Values Area<\/code>: Move numeric fields here which you want to see totals. In this sample it is desired to see sum of\u00a0Total<\/code>\u00a0field for each menu item (for values that has been added to rows.)<\/div>\n<\/li>\n
  • \n
    Columns Area<\/code>: Move fields here which you want to see totals in intervals. For example you can move\u00a0Date<\/code>\u00a0values here to display periodic totals.<\/div>\n<\/li>\n<\/ul>\n

    If you want to list\u00a0Menu Item Names<\/code>\u00a0on rows and see the sum of\u00a0Total<\/code>\u00a0Values. You need to drag & drop\u00a0Item Column<\/code>\u00a0to the area labeled as\u00a0Drop Row Fields Here<\/code>. You should also drag & drop\u00a0Total<\/code>\u00a0inside table.<\/p>\n

    \"\"<\/p>\n

    Defining Value Types<\/h2>\n
    \n

    Report displays\u00a00<\/code>\u00a0as Totals. This is because report processes these values as alphanumeric values. You can set column formats by using column type identifiers. On header row you need to use\u00a0[N]<\/code>\u00a0(number) for Quantity and\u00a0[C]<\/code>\u00a0(currency) for Total. Before doing it right click on value area and click\u00a0Save Changes<\/code>\u00a0to store your changes.<\/p>\n\n\n\n
    [Orders:1,2,1,1]
    \n>Item Group|Item|[N]Quantity|[C]Total {REPORT ORDER DETAILS:O.ItemGroup,O.MenuItemName,O.Quantity,O.TotalPrice}<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    After this change you’ll see totals for each Menu Item.<\/p>\n

    \"\"<\/p>\n

    \n

    Tip;<\/span>\u00a0If you need to see periodical totals (eg.daily) you can include\u00a0O.Date<\/code>\u00a0field inside report template, configure column type as\u00a0[D]<\/code>\u00a0and drag this value to\u00a0Columns<\/code>\u00a0area.<\/p>\n<\/div>\n

    Adding More Fields<\/h2>\n
    \n

    On this step\u00a0Item Group<\/code>\u00a0will be moved to the\u00a0Rows<\/code>\u00a0area in front of\u00a0Item<\/code>\u00a0field. Also\u00a0Quantity<\/code>\u00a0field will be moved to\u00a0Values<\/code>\u00a0area.<\/p>\n

    \"\"<\/p>\n

    \n

    Pivot Table beautifully displays Items grouped by Item Groups, display separate totals for each group and also displays quantity totals.<\/p>\n

    Tip;<\/span>\u00a0Don’t forget to click\u00a0Save Changes<\/code>\u00a0to store your changes.<\/p>\n<\/div>\n

    Advanced Configuration<\/h2>\n
    \n

    You can right click on value area and click\u00a0Design Mode<\/code>\u00a0to see some advanced settings.<\/p>\n

    \"\"<\/p>\n

    \n

    You’ll see a lot of settings here. For easier finding settings you can use Search editor to search for settings by it’s name.<\/p>\n

    Changes you’ll make on pivot grid will saved to database so these changes will be available to all terminals.<\/p>\n<\/div>\n

    Header Visibility Settings<\/h3>\n
    \n

    When you complete designing your report, you can hide field dropping areas for better view.<\/p>\n\n\n\n\n\n\n\n\n
    ShowColumnHeaders<\/td>\nDisable to hide column header dropping area.<\/td>\n<\/tr>\n
    ShowDataHeaders<\/td>\nDisable to hide value dropping area.<\/td>\n<\/tr>\n
    ShowFilterHeaders<\/td>\nDisable to hide unused fields area.<\/td>\n<\/tr>\n
    ShowRowHeaders<\/td>\nDisable to hide row dropping area.<\/td>\n<\/tr>\n
    ShowColumnGrandTotalHeader<\/td>\nDisable to hide\u00a0Grand Total<\/code>\u00a0header from columns.<\/td>\n<\/tr>\n
    ShowRowGrandTotalHeader<\/td>\nDisable to hide\u00a0Grand Total<\/code>\u00a0header from rows.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    You can have a simpler view by disabling these settings.<\/p>\n

    \"\"<\/p>\n

    Tip;<\/span>\u00a0If you need to make changes on fields you also use\u00a0Show Field List<\/code>\u00a0Context Menu item. By using field selection window you can make changes without the need of enabling these fields.<\/p>\n

    \"\"<\/p>\n

    Field Specific Settings<\/h3>\n
    \n

    Each fields have some additional settings that can be used for changing formatting or calculation. You can access field specific settings through\u00a0Design Mode > Data > Fields<\/code>.<\/p>\n

    \"\"<\/p>\n

    To change settings of multiple fields easily you can type setting name on top.<\/p>\n

    \"\"<\/p>\n

    Here are some useful settings.<\/p>\n\n\n\n\n\n
    Width<\/td>\nFor setting a width for a field. Changes made by dragging column boundaries won’t saved.<\/td>\n<\/tr>\n
    Cell Format<\/td>\nFor changing display format of values. You can use standard formatting strings here. For example not to display decimal places you can use\u00a0#<\/code>\u00a0format.<\/td>\n<\/tr>\n
    Running Total<\/td>\nDisplays running totals.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Changing Summary Type<\/h4>\n
    \n

    By changing\u00a0Summary Type<\/code>\u00a0setting of a field you can change how values calculates. Possible values are:<\/p>\n

      \n
    • \n
      Count<\/code>: Displays total count of items.<\/div>\n<\/li>\n
    • \n
      Sum<\/code>: Displays sum of values.<\/div>\n<\/li>\n
    • \n
      Min<\/code>: Displays minimum value.<\/div>\n<\/li>\n
    • \n
      Max<\/code>: Displays maximum value.<\/div>\n<\/li>\n
    • \n
      Average<\/code>:Displays average value.<\/div>\n<\/li>\n
    • \n
      Std Dev<\/code>:A statistical function that displays standard deviation.<\/div>\n<\/li>\n
    • \n
      Var<\/code>: A statistical function that displays variation.<\/div>\n<\/li>\n<\/ul>\n<\/div>\n

      Changing Summary Display Type<\/h4>\n
      \n

      You can change how summaries displays. For example instead of displaying sum of total value we can display percent value for sum.<\/p>\n

      \"\"<\/p>\n

      \n

      In this sample it will be displayed percent of column value instead of totals.<\/p>\n

      Tip;<\/span>\u00a0For displaying both total and percent values you can add multiple total fields from report template.\u00a0<\/strong>You can also use\u00a0..Variation<\/code>\u00a0settings to display how (+,- or %) data changes between intervals.<\/p>\n

      Possible values for summary display type setting are:\u00a0Absolute Variation<\/code>,\u00a0Percent Variation<\/code>,\u00a0Percent of Column<\/code>,\u00a0Percent of Row<\/code>,\u00a0Percent of Row\/Column Grand Total<\/code>,\u00a0Rank in Column\/Row from Smallest to Largest<\/code>.<\/p>\n<\/div>\n

      Changing Group Interval<\/h4>\n
      \n

      By changing group interval setting of a field you can change how data displays in intervals. For example for date fields you can change summary intervals as\u00a0day<\/code>,\u00a0week<\/code>,\u00a0month<\/code>\u00a0to analyze daily, weekly, monthly totals. Possible values are.\u00a0Date<\/code>,\u00a0Date Day<\/code>,\u00a0Date Day of Week<\/code>,\u00a0Date Day of Year<\/code>,\u00a0Date Week of Month<\/code>,\u00a0Date Week of Year<\/code>,\u00a0Date Month<\/code>,\u00a0Date Quarter<\/code>,\u00a0Date Year<\/code>,\u00a0Year Age<\/code>,\u00a0Month Age<\/code>,\u00a0Week Age<\/code>,\u00a0Day Age<\/code>,\u00a0Numeric<\/code>,\u00a0Hour<\/code>,\u00a0Minute<\/code>,\u00a0Second<\/code>\u00a0and\u00a0Alphabetical<\/code>.<\/p>\n

      By using\u00a0Alphabetical<\/code>\u00a0interval you can group items Alphabetically.<\/p>\n

      This sample displays Date interval by Week Days.<\/p>\n

      \"\"<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

      Pivot tables are useful to create different views of data easily for better analyzing data. Preparing a Sample Report You can create a simple\u00a0Orders\u00a0report by using this custom report template. [Orders:1,2,1,1] >Item Group|Item|Quantity|Total {REPORT ORDER DETAILS:O.ItemGroup,O.MenuItemName,O.Quantity,O.TotalPrice} This report will list all orders we’ve placed inside selected date range. Converting Report to a Pivot Table Pivot […]<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-14029","post","type-post","status-publish","format-standard","hentry","category-reports"],"_links":{"self":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts\/14029","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/comments?post=14029"}],"version-history":[{"count":9,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts\/14029\/revisions"}],"predecessor-version":[{"id":25077,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/posts\/14029\/revisions\/25077"}],"wp:attachment":[{"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/media?parent=14029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/categories?post=14029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.sambapos.com\/en\/wp-json\/wp\/v2\/tags?post=14029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}