This documentation page assumes that you already have a SeekTable account. You can create free account by signing up.

Pivot table by SQL-compatible database

You can configure 'live' connection to your SQL database and use it as a data source for pivot table reports. In this case data is not imported and SeekTable will query underlying data source to retrieve the necessary data. This also means that your database should be able to execute typical aggregate queries fast enough (in seconds).

  1. Click on "Connect to Database" item at "Cubes" view, or just open this link (ensure that you're logged in).
  2. Select "SQL-compatible database" in Data Source Type selector: SQL database connection settings
  3. In Cube Name enter short title that describes this data source.
  4. Choose your database in Database Connector and configure its Connection String:
  5. Specify Select Query: this is SQL SELECT command that determines possible columns for dimensions or measures. In simplest case this might be something like:
    SELECT * FROM some_table_or_dataview
    You can specify complex SQL query if needed (with JOINs, WHERE).
  6. Keep Infer dimensions and measures by columns checked to determine dimensions and measures automatically by the first N rows. You can modify suggested configuration later.
  7. Click on "Save" button.

If everything is fine you should see a new cube dashboard with the list of available dimensions and measures.

In case of connection error you'll see an orange box with an error message; you may click on "Edit Configuration" and apply necessary changes.

Dimensions setup

SQL cube dimensions setup
Field: dimension name refers to table column or result of SQL expression (can be provided as first "Parameter").
Expression: dimension is defined as calculated field with custom formula that uses another dimensions as arguments (formula and arguments should be specified in "Parameters").
Unique dimension identifier. For Type=Field this is column name specifier (possibly with table alias prefix).
User-friendly dimension title (optional).
Custom format string (.NET String.Format) for dimension values (optional). Examples:
  • for number values: ${0:0.##} → $10.25
  • for date values: {0:yyyy-MM-dd} → 2017-05-25
For Type=Field: you can specify custom SQL expression for this dimension, or dimension ID column for "Conditional JOIN rule".
For Type=Expression: you can specify custom formula (1-st parameter) and dimension names for the arguments (2-nd, 3-rd etc parameter).

Measures setup

SQL cube measures setup
Count: the number of aggregated rows.
Sum: the total sum of a numeric column.
Average: the average value of a numeric column.
Min: the minimal value of a column.
Max: the maximum value of a column.
FirstValue: custom SQL aggregate expression like 'COUNT(DISTINCT some_column)'.
Expression: measure defined as calculated field.
Explicit unique measure identifier. You can leave it blank (for any measure types except "Expression") to generate the name automatically.
User-friendly measure caption (optional).
Custom format string (.NET String.Format) for measure values (optional). Example:
  • ${0:0.##} → $10.25
For Type=Count: no parameters needed.
For Type=Sum/Average/Min/Max: column name to aggregate.
For Type=FirstValue: custom database-level SQL aggregate expression.
For Type=Expression: first parameter is an expression, and next parameters are names of measures used as arguments in the expression.