Report designer

With the Report Designer tool you can quickly explore the datastreams, create views using data filters, and add custom metrics to the report.

Report toolbar

Report itself has few properties: Name, Start and End dates, optional Dynamic Context pickers, and View area. View area is paginated and you can sort and filter the data using any column.

Buttons:

• Refresh data.

• Report actions:

  • Download data to XLSX or CSV file. The file includes all rows not just visible ones and user-defined metrics. If you have selected more than one row in the report only the selection will be downloaded. 

  • Custom actions

• Toggle design mode. When design mode is activated you can make changes to row, column, filter and custom metric definitions.

In the design mode the following controls will be available in the Report Header section:

• • Rename the report

  • Save. If you have provided a name for the report it will be saved and subsequently accessible in the reports menu. If you make changes to a saved report and do not change its name the old report will be overwritten.

  • Share. You can share the reports across your organization. Shared reports are accessible to other members in your organization.

  • Export. Download report structure into a JSON file.

  • Import. Generate report structure from JSON. Open the exported JSON file in a text editor and copy-paste the contents to the JSON import field. 

  • Delete. Deletes the report. Only private reports can be deleted so make sure to unshare the report first.

Report designer

To use the report designer you need to have one or more datasstreams with data to explore. A datastream is similar to a database table or view. In addition to columns and data the datastream contains information about underlying data sources, dimensionality, data types, formatting and semantics such as column labels and predefined formats.

Each report consists of one or more related datastreams.  To add a datastream to report click ADD DATASTREAM button.

It is possible to define VISUALIZATION for a report. Once defined the report can be used in dashboards. Note that if TABLE visualization is used the number of visible rows is restricted so make sure the report is designed properly.

Report Layout

With report layout it is possible to display only fields that are informative and exclude from the view the fields that are required for calulations, relationships or filters. It is possible to define custom caption and number format for each field. To add field to the layout use the action available in field's popup. It is also possible the change the order of the layout.

In design mode it may be useful to use 'Auto update layout' option to quickly see the changes when adding fields to the report.

Datastream

Available fields

Each field includes an icon hint about its data type (text, numeric or temporal) and info button. Info button reveals options for this field:

• Technical name (alias)

• Customizable label in current report

• Formula (custom fields only)

• Data type (custom fields only)

• Aggregator function

• Number format

• Actions:

  • Add to fields - makes field available in the report for display or relationship

  • Add filter - allows to define data filter for this field

  • Add global field (see global fields)

  • Add to layout - add current field to report layout

  • Move to end - perform field sorting in layout 

Included Fields

When field is added to the report it can be used in layout and referenced in custom fields. When field is first added to the report the alias will be automatically generated for the field. It is possible to change the alias to something more meaningful in the current context. Custom alias must be unique across all fields and parts (datastreams) including the global part.

Custom fields

Custom fields can be used to add calculated metrics to the report. To create custom field click on the (+) button next to the section header.

Optionally define custom alias (make sure the alias is unique across all fields in the report).

Enter value expression. See formula reference for details. 

Data filters

Any available field can be added to data filters by dragging it to the filter area. In addition to the field you need to provide an operator and value. The following operators are available: is,=,>,<,>=,<=,<>,like,not like,is in,not in,between,in expression,in date range (for temporal fields only), in context, dimension (when using dynamic dimensions in the context; see Dynamic Context reference for details).

'IN DATE RANGE' includes all record where date value in the selected field is between the dates selected in the report header.

'IN EXPRESSION' allows to desfine a custom expression which produces a list of values e.g. context.API.range function. Refer the Context API for details.

'BETWEEN' includes records where the value in the field is in the specified range. Enter two values, comma sepearated. First value denotes lower threshhold, and second value is upper threshhold, e.g. 1,2 includes all values of x where 1<=x<=2.

Comparison operators =,>,<,>=,<=,<> make can be used with any value type.

'LIKE' and 'NOT LIKE' operators allow to perform wildcard text matching on the field using SQL syntax e.g. '%name fragment%'.

'IS' operator allows you to select a single value from a list. When you select this operator the value field turns into the autofill box and you can select value from the list or type to narrow it down towards desired values.

'IS IN' and 'NOT IN' operators allow you to select multiple value from a list to include only selected items or exclude them from report.

Relationships

When you have more than one datastreams included in the report you need to define relationships between them so that they can be fitted to the same report. For relationship to work both datastreams must have same values in some of the fields. For example, one datastream could be sales data with product IDs and the second datastream could be product catalog with productIDs and some additional fields; by matching the two ID fields it is possible to include additional information about products in the sales report.

To define a relationship:

• Select field from first datastream.

• Select matching field from current datastream.

• Select relation mode: left, right , full, or inner. The relationship can be defined only between first datastream and current datastream.

  • Left: include all records from the first datastream, and only matching records from current datastream. Some records from the first datastream may remain without matching value from the current.

  • Right: include all records from the current datastream and those matching from the first datastream. Some records from the current datastream may remain without matching value from the first.

  • Full: include all records from both datastreams. Some values in either datastream may be missing matching value from the other datastream.

  • Inner: include only records from both datastreams where the match is found.

• Optional TIME SHIFT. This is used to dynamically shift the start and end dates based on the report's current context. This useful for building variance reports where the second datastream is the same as the original but from the different time period. For advanced timeshift consider using expression filters with context API.

Global Fields

Global fields are taken from individual datastreams and can be used for further filtering and calculations. Once defined only the global fields can be added to report layout. You can add custom global fields as well.

Calculated metrics

Calculated metrics are available only in the current report.

Click the Add Metric button.

Enter metric name. This can be any string but it is not recommended to include special characters (see below) such as dollar sign ($) or percentage (%).

Enter formula for metric. To include an existing metric in the formula click on the arrow button inside the desired metric field (located in the metric drop area).  The appropriate variable will be inserted automatically to the most recently edited custom metric field. 

Aggregate formulas such as sum must be prefixed with Fn.

Examples:

• Simple calculation: 1+2

• Reference values of a column of the current row: row['sales_cost']/2

• Calculate an aggregate of a column (all rows,  not just visible): row['sales_cost']/Fn.sum('sales_cost')

The following arithmetic operators are available: +,-,*,/. Calculation precedence can be expressed using parentheses (). 

For more detailed instructions please refer the formula section.

As opposed to custom fields the calculated metrics can be referenced in subsequent calculations. In this case you must not use special characters in metric name. Special characters include percentage and dollar signs. To add these to the report use the format field.