How to create a Pivot Table

Pivot Tables (also known as a "crosstab", "cross-tabulation", "matrix") allow you to summarize and analyze data in terms of groups; with pivot tables you can see comparisons, explore trends and get answers on questions like "what is happened" and "why".

In SeekTable you can easily create pivot tables:

1. Connect to your data: upload a CSV file or a setup database connection.
2. Add new report: click on "Create Report" from cube, or choose "+" next to cube's name in the left menu.
   
3. Keep "Report Type" option at "Pivot table"
4. Choose grouping criteria: what columns to use for "Rows" and/or "Columns".
   Hold CTRL to select multiple items at once.
5. Choose a measure (aggregate function) to display in the pivot table: this may be count of rows, sum or average of the column. SeekTable will calculate aggregates for each row x column intersection. You can choose several measures at once.
6. Click on "Apply"

→

Sort Pivot Table

You can order table rows and columns in the following ways:

• By labels: order by values of the columns that are selected for "Rows" or "Columns". This is default sort option. To change order direction just click on the appropriate header at the top-left corner of the table:
  • ← indicates A-Z order (ascending)
  • → indicates Z-A order (descending)
• By values: order by the table row (or column) values - that are calculatation results of the measure. To sort by concrete row or column values click on the last-level label (or measure name if present).

  It is possible to keep A-Z order of groups, and order by values only last-level labels with help of "Sort by value only inside group" checkbox ("Format" tab).

Filter pivot table

You can exclude some rows or columns (or show only concrete rows/columns) with very simple keyword-based "Filter":

• Keywords for the same axis (rows or columns) are combined with OR. To force AND use "+" to combine conditions.
• To exclude use "-" before the keyword, for example: -Venezuela.
• It is possible to filter by values: >5 (if only one measure is used) or sum>5 (to filter by measure name that contains "sum").

One more way to filter report data is usage of report parameters. This kind of filter is applied on the row level; in case of database filtering condition is included into the query. This means that with report parameters it is possible to filter even by columns that are not used in the report.

Params tab appears when at least one report parameter is configured for the cube.

Formatting

You can specify custom format for both dimensions (table headers) and measures (cells with values) in this way:

1. Press on "gear" icon near Rows, Columns, Values.
2. Specify a custom format for an appropriate item with this pattern:

   <prefix> {0:<value_format_specifier>} <suffix>

   where <value_format_specifier> can be:

   # → display only integer part
   $#.## → display numbers as $50.01
   yyyy-MM-dd → display dates as 2019-07-15
   MMM → format a month number (1-12) as a short month name (Jan, Feb etc)
   MMMM → format a month number (1-12) as a full month name (January, February etc)
   ddd → format a day-of-week number (0-6) as a short day-of-week name (Mon, Tue etc)
   dddd → format a day-of-week number (0-6) as a full day-of-week name (Monday, Tuesday etc)
   0.#|k → if number>1000 shorten it with "k" suffix
   0,.0# → show number in thousands (value divided by 1000)
   0.#|kMB → shorten large number with appropriate "k"/"M"/"B" suffix
   ifempty=STR → if value is empty or null show "STR"
   

   For numbers formatting meaning of #, 0, ., , is the same as in Excel. For complete reference on format syntax see .NET String.Format documentation.

Drill Down

SeekTable offers great data exploration capabilities with a drill-down function: you can click on any pivot table cell with value and get more detailed view for this concrete group:

Drill-down menu gives you 2 alternatives:

• Show rows: opens 'flat table' report pre-configured to display records of this group. Use browser's "Back" to return to the original pivot table. You can open this view in a new tab if you like (this is normal link).
  NOTE: "Show rows" option is not available if your pivot table uses expression-type dimensions or dimensions that are not allowed for 'flat table' report type.
• Drill down by dimension: you can choose which dimension to use to get more detailed view of the group. As result this dimension will be added to rows (or columns) and "Filter" will be changed to limit pivot table by the rows/columns that correspond to the group:

If you like Excel-like drill down by hierarchy with groups expand/collapse this is also possible:

• choose dimensions that you want to use as hierarchy for rows and/or columns. Hiearchy for dates might be: "Year", "Month", "Day". For location this could be "Country", "State", "City" and so on.
• Use "Enable expand/collapse" ("Format" tab) to activate this mode.
• Click on "+" and "-" to expand/collapse individual groups:

Pivot Table Percentage

To show values as percentages such as "% of Grand Total" or "% of Row Total" or "% of Column Total":

1. open Format tab
2. in the Value Calculations dropdown choose an appropriate option:

Pivot Table Top 10 show only top N rows and/or columns

To show only first top 10 (or top 5, top 100 etc) results in a pivot table:

1. open Format tab
2. use appropriate Limits dropdown to apply a top-N filter for either rows or columns or both:

All results that are beyond the limit are included into special "..." group. To hide this group you can uncheck Show "..." group in case of limit .

Exports

SeekTable can export pivot tables to all popular export formats: CSV, PDF, Excel, HTML. For PDF/Excel exports the layout is identical to a web view. For Excel exports SeekTable tries to preserve all formatting (excluding custom HTML; however, colors/links for cells are preserved in Excel export). Also, a special "Excel PivotTable" export is possible; it works in this way:

• report's aggregated dataset is exported to a worksheet
• in another worksheet Excel's pivot table (based on the aggregated dataset) is created automatically. SeekTable tries to make it similar to report's web view, however in Excel some calculations work differently (in particular, "differences" and "percentages") + for SeekTable-calculated measures totals may show wrong numbers.

By default exports include only data that you see in a web view (current page only, if pagination is used). If you want to export all rows/columns you may check Export all data option, and in this case export will include as much rows as possible (up to 50,000 on cloud SeekTable; this limit can be increased on self-hosted SeekTable).

Limitations

• Max number of value cells in the table (rows x columns): 1,000,000
  This limit can be increased in a self-hosted SeekTable.
• Max number of rows (unpaginated, expanded): 50,000
• Max number of columns (unpaginated, expanded): 1,000