This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

How to create a Pivot Table report

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 perform comparisons, explore trends and get answers on questions like "how many", "what is happened" and "why". Pivot tables remain most useful way to explore data both by non-IT users and professionals.

With SeekTable you can create pivot tables just in a few clicks:

  1. Connect to your data: upload a CSV file or a setup database connection.
  2. Add a 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"
Pivot table configuration form Pivot table example

Sort Pivot Table

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

Filter pivot table

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

Pivot table filter

Filter pivot table with parameters

Another way to filter report's data are report parameters. Unlike table's "Filter" these parameters are always applied on a data source level; for instance, for SQL-based cubes these report parameters are converted to query's WHERE conditions. With report parameters it is possible to filter by dataset's columns that are not used in the report.

Report parameters may be configured only explicitly in the cube configuration form; please check your data source type help page for guidance. Params tab appears when at least one report parameter is configured for the cube.

Cells Formatting

You can specify custom format for dimension's labels (table's rows & columns headers) and measures (cells with values) in this way:

  1. Click on "gear" icon near Rows, Columns, Values.
  2. Specify a custom format for an appropriate item with this pattern:
    <prefix> {value_format_specifier} <suffix>
    where value_format_specifier can be:
    # → display only integer part
    $#.## → display numbers as "$50.01" (empty if zero)
    $0.00 → display numbers as "$50.01" ("$0.00" if zero)
    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 an 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.
  3. Also in this dialog you can specify custom labels if needed.

Pivot table values formatting

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:

Pivot table drill-down on click by value

Cell's drill-down menu gives you 2 alternatives:

Pivot table drill-down by dimension

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

Pivot table groups expand/collapse

SeekTable supports pagination for collapsed pivot tables. To show all rows and/or columns you may specify explicit Limits ("Format" tab) in the same way as for non-collapsed pivots.

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 percentage of total

Data bars

It is possible to display percentage calculation as data bars that can improve table's readability a lot.

  1. open Format tab
  2. ensure that in Value Calculations a percentage option is selected ("% of ...")
  3. Data bars checkbox appear to control whether percents should be visualized as data bars.
  4. Click on "gear" icon to configure options like color, display value and which cells should have bars (all, values and subtotals, or only values).

Pivot table data bars

Data bars are present PDF and Excel exports.

Pivot Table Top 10 show only top N (last 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:
  3. click on Apply

Pivot table top 10

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 .

Alternatively, it is possible to apply "top-N" filter per grouped items separately in the same way as in Excel PivotTables. This is useful when you want to show, say, top-10 'best' product categories and then top-5 'best' products in each category; to do that it is enough to add this into Filter textbox: category:top(10), product:top(5) (assuming that the report has "category" and "product" dimensions on the same axis).

Repeat Labels in a Pivot Table

It is possible to repeat labels of grouped items in the same way as you can do that in Excel PivotTable:

  1. open Format tab
  2. check either Repeat labels for Rows or Repeat labels for Columns (or both)
  3. click on Apply

Pivot table repeat labels


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. In Excel exports SeekTable tries to preserve all formatting (excluding custom HTML; colors/links for cells are preserved in Excel export). If SeekTable's pivot report has a chart it is also exported as an Excel Chart in a separate worksheet (this is a rather unique capability).

Additionally, a special "Excel PivotTable" export is possible; it works in this way:

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