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

MongoDb pivot table reports

SeekTable can be used as MongoDb reporting tool with built-in connector; this connector may be used with protocol-compatible databases that support aggregation pipeline, like Azure Cosmos DB or Amazon DocumentDB.

You can configure 'live' connection to your Mongo database and use it as a data source for pivot tables (charts, grid-views) reports. Data is not imported: SeekTable uses MongoDb aggregate pipeline query ($group) to retrieve the necessary data for the report.

Looking for embedded solution? Try PivotData microservice which can be seamlessly integrated into any web application for pivot tables generation by MongoDb with simple web API.

There are no any limitations on the dataset size, but your MongoDb should be able to execute aggregate queries fast enough (in seconds). If you have really huge collections that cannot be aggregated in the real-time you can apply database-level filtering with report parameters or you can hardcode some filter in Filter JSON to limit number of documents to process. Alternatively you can use pre-aggregated collections for the low-granularity reports.

How to configure MongoDb data source

  1. Click on "Connect to Database" item at "Cubes" view, or just open this link (ensure that you're logged in).
  2. Select "MongoDb" in Data Source Type selector: MongoDb connection settings form
  3. Fill all required fields:
    Cube Name
    short title that describes this data source
    Connection String
    Valid connection string for MongoDb driver. Example:
    mongodb://user:password@host:port/database[?options]
    Notes:
    • "database" is required if user has access only to specific databases.
    • if you get an error A timeout occured after 30000ms selecting a server:
      • ensure that your MongoDb server/cloud service allows connections from SeekTable server (SeekTable.com IP is included into whitelist)
      • if you use cloud MongoDb service (Atlas, MongoLab) try to add these options: connect=replicaSet, replicaSet=yourSet, authSource=yourAuthDB
    Database Name
    the name of the database to use.
    Collection Name
    the name of collection to use; document and sub-document fields can be used as dimensions or measures in a pivot table reports.
    Filter JSON
    here you can define custom JSON for $match stage. For report-specific filtering you can use report parameters.
    Unwinds for sub-collections
    If documents have sub-collections you can unwind them to use fields from these sub-documents. Unwind may be applied conditionally, only when some specific fields are used in the report.
    Custom Aggregate Pipeline Stages
    Additionally you can specify any number of custom MongoDb aggregate pipeline stages (with JSON) that are applied before or after $group. Custom stages may be used for lookups resolution or calculations that are performed on MongoDb side. Custom stages also may be applied conditionally only when some specified fields are used in the report.
  4. Infer dimensions and measures by columns option: keep it checked to determine dimensions and measures by first N documents - in this case you don't need to define Dimensions and Measures by yourself. You can edit configuration later and remove excessive elements or customize automatically determined ones.
  5. Click on "Save" button.

If everything is fine you should see a new cube dashboard with the list of available dimensions. In case of connection error you'll see an orange box with the error message.

Dimensions setup

Dimensions determine what document fields may be used as grouping criteria. It is possible to project dimension by specifying calculation as 1-st "Parameter".

Cube dimensions form
Type
Field: dimension value is a document field or result of $project specification (can be provided as first "Parameter").
Expression: dimension is defined as calculated field with custom formula that uses another dimension(s) as argument(s); formula and arguments are specified in "Parameters".
Name
Unique dimension identifier. For Type=Field this is document or sub-document field specifier like field.sub_field.
Label
User-friendly dimension title (optional).
Format
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

Measures setup

SQL cube measures setup
Type
Count: the number of aggregated documents.
Sum: the total sum of a numeric field.
Average: the average value of a numeric field.
Min: the minimal value of a column.
Max: the maximum value of a column.
FirstValue: custom acummulator aggregation pipeline expression (not supported yet for MongoDb).
Expression: measure defined as formula expression that is calculated by SeekTable reporting engine.
Parameters
For Type=Count: no parameters needed.
For Type=Sum/Average/Min/Max: document field or field path to aggregate.
For Type=FirstValue: collects field value of the first document.
For Type=Expression: first parameter is formula expression, and next parameters are names of measures used as arguments in the expression.
Name
Explicit unique measure identifier. You can leave it blank (for any measure types except "Expression") to generate the name automatically.
Label
User-friendly measure caption (optional).
Format
Custom format string (.NET String.Format) for measure values (optional). Example:
  • ${0:0.##} → $10.25

Report parameters setup

Report parameters are used when you need to declare user-defined variable and use it in the Mongo aggegate pipeline stages; typical usage is database-level filtering with Filter JSON, but parameter placeholders may be used in Custom Stage entries as well.

Name
Unique (for cube) parameter identifier.
Label
User-friendly parameter caption for UI (optional).
Data Type
String: text-based value.
Int32: 32-bit integer (max value is 2,147,483,647).
Int64: 64-bit integer (max value is 9,223,372,036,854,775,807).
Decimal: Fixed-point number with max 28 significant digits. Decimal point is '.' character.
DateTime: date or datetime value (in case of date this will be datetime value with 0:00:00 time). Date value should be specified as string in YYYY-MM-DD format.
Boolean: accepts only 'True' or 'False' value.
Multivalue
If checked parameter can accept several values (as array, in UI user can enter them as comma-separated string). Multivalue parameter can be used only with SQL IN condition.
Default Value
Defines default value of this parameter. Empty means 'not defined'.
Expression
Custom expression to evaluate final parameter value. Expression syntax is the same as in calculated cube members; you can access user-entered values with Parameter["param_name"].

When parameter is defined it can be used in Filter JSON as following:

{ 
  $and: [ 
    {"_id":{$exists:true}},
    @borough[ {{ "borough" : {0} }}, ]
    @cuisine[ {{ "cuisine" : {{ $regex : {0} }} }}, ]
  ]
}
where:

Parameter placeholder syntax notes:

@
identifies that this is a placeholder for the parameter
orderDate
parameter Name
[ ]
expression between square brackets is added to SQL command when parameter is defined.
{0}
placeholder for the parameter value. Value is provided to database as typed command parameter; SQL injections are impossible. This also means that parameter value cannot contain SQL expression or used to provide part of SQL command.
If you're not sure how to configure MongoDb as a data source feel free to contact us and ask for assistance.