Through this function you can access all your data of the datasets you have access to within Hublify.

Basically you are building and executing dataset-retrieval-query with these functions.

  • Behaves like a SQL's SELECT query
  • Provide fields to retrieve
  • It handles automatic connections to other datasets (SQL JOINs)
  • Provide filter & segments to apply
  • Returns a report-object containing all the data and head-meta informations, too.
  • The main differences to dataset(...)is ...
    • ... the return value is a rich result-report-object
    • ... that you can execute / combine / post-calculate formulas on multiple-reports.

 

Example

For quick dive, here an example

{# Getting data (fields "pcode","name" and "price") from dataset "product" #}
{% set myData = hublify.datareport('product').fields(['pcode','name','price']).all %}

 

Functions

datareport(...)

Creates a dataset-query-object.

Parameter: "<dataset>" A string containing the dataset-label.

Return: This functions returns dataset-query-object.

{# Creating a standalone query-object #}
{% set myQueryObj = hublify.datareport('product') %}

field(...)

Adds one field to the dataset-query.

Parameter: Pass a string containing the field you want to add.

{# Adding the field "firstname" to the query #}
{% set myData = hublify.dataset('person').field('firstname').all %}

You can also cascade multiple field(...) functions. But see also to directly add an array of fields, using the fields(...)-function.

{# Cascaded adding of single fields #}
{% set myData = hublify.dataset('person').field('firstname').field('lastname').field('gender').all %}

fields(...)

Adds multiple fields you want to read to the dataset-query.

Parameter: Pass an array of strings of fields you want to add.

{# Adding three fields as an array to the query #}
{% set myData = hublify.dataset('person').fields(['firstname','lastname','gender']).all %}

 

filter(...)

Adds a filter to your dataset-query.

{# Getting all persons "firstname" whos "lastname" is "skywalker" #}
{% set myData = hublify.dataset('person').field('firstname').filter('lastname','skywalker').all %}

 

filters(...)

Adds multiple filters to your dataset-query at once.

{# Getting all persons "firstname" filtered for the "firstname" and "lastname" #}
{% set myData = hublify.dataset('person').field('firstname').filters([ {'lastname':'skywalker'}, {'firstname':'luke'}]).all %}

segment(...)

Adds a filtering segment to your dataset-query.

{# Adding the segment "bday_next_within_7days" to the query #}
{% set myData = hublify.dataset('person').segment('bday_next_within_7days').all %} 

Of course you can combine segments with other filters!

segments(...)

Adds multiple segments for filtering to your dataset-query at once.

{# Adding multiple segments to the query #}
{% set myData = hublify.dataset('person').segments(['bday_next_within_7days','a-customer']).all %} 


orderBy(...)

Adds an ordering to your retrieved data.
This can be called multiple times. Given order-by-statements are then cascaded in the same order.

{# Enabling a ordering by field "name" in ascending order #}
{% set myData = hublify.dataset('product').fields(['pcode','name']).orderBy('name', 'ASC').all %}

Parameter: First parameter is the field name, the second the order-direction ("ASC|DESC").

limit(...)

To limit the number of returned dataset-records, use this function. It makes only really sense to use it when executing the dataset-query with all(...).

{# Limit the number to the first 5 products #}
{% set myData = hublify.dataset('product').fields(['pcode','name']).limit(5).all %}

offset(...)

To set a number of records to skip on when retrieving the record-list. Use this e.g. for pagination. It makes only really sense to use it when executing the dataset-query with all(...).

{# Get the next 5 records, starting a from position 10 #}
{% set myData = hublify.dataset('product') 
    .fields(['pcode','name'])
    .offset(10)
    .limit(5)
    .all
%}


resultIndex(...)

Configures a field which values should be used for a data-list's result index, instead of the typical numerical range 0..n.

{# Get result of products as object/assoc array (key = 'pcode' #}
{% set myData = hublify.dataset('product').fields(['name']).resultIndex('pcode').all() %}


param(...)

Add an own parameter to the query. This function can be called multiple times with same or different key/values.

Example

{# Add a parameter "viewingUser" = "kevin" to actual dataset-query. #}
{% set myData = hublify.dataset('product').param('viewingUser', 'kevin').fields(['pcode','name']).all %}
 

params(...)

Add any own multiple parameters to the query.

Example

{# Add a parameter "viewingUser" = "kevin" to actual dataset-query. #}
{% set myData = hublify.dataset('product')
    .params({
        "myParam1": 123,
        "anotherParam": "aloha!"
    })
    .fields(['pcode','name'])
    .all %}


mainCluster(...)

Sets the main cluster to be used for all report(s) contained in this report-query.

Use mainCluster() especially when executing multi-reports.

Syntax

mainCluster($clusterLabel)

Parameter

  • $clusterLabel (string)
    The label of cluster-configuration to load and use.

Example

{# Create Query-Object. Set mainCluster. Add actual Report-Configs #}
{% set reportQueryObj = hublify.datareport()
      .mainCluster('year_month')
      .reports( [ .... ] ) 
%}


 

reports(...)

Configures one or more reports by a given parameter-object.

Example

{# ... #}

report(...) 

This executes the previously configured report-query and returns the Report-Result-Object (see explanation below).

The Result-Object contains all actual data as well as lots of other helpful meta-data.

Put this always to the end of your twig-expression!


one(...)

EXECUTEs the data-query and returns only the first object.
(Like a: limit 1).

Put this always to the end of your twig-expression!

all(...)

EXECUTEs the data-query and returns an array of records.
Filters and sorting and limits are regarded.

Put this always to the end of your twig-expression!

 

Report Result Object

The executed hublify.datareport(...) returns an data-object (DTO) containing not only the raw database-values but also optionally a lot of useful extra data & infos necessary to render and display nice and rich charts, diagrams, tables, cards, ... basically any kind of visualization.

  • The raw data-values of the report-query(ies) is contained.
  • Meta Informations (units, colors, ...) on fields and values are contained too.
  • Is internally structured to handle principal-, measurement-values, data-series etc.
  • One Report Result Object can contain actually multiple Report Results at the same time (-> calculating formulas! hooray!)
  • Formulas / Calculations can be applied on-top of the generated Report Result (and create dynamic new columns)
  • Since the Report-Object is a "programmatic class's instance" it has own functions you can use on it!
    (Class: HfyDataReport | em_report)
  • Functions are optimized for preparing / transforming/ extracting data into special data-structures as needed for from simple tables up to complex tree structured eCharts.


Functions


getPrincipalLabel()

Gets the report's principal-label (field-dim-1).

Returns NULL, if no principal-field was set.


getPrincipalValues()

Gets the principal-values (X-Axis, field-dim-1) as a numeric array.


getMeasureLabels()

Returns a numeric array of measures (dimensions 2 / Y-Axis) in this report.


getMeasure(...)

Gets the complete definition-object for a measure.

This contains a lot meta information and the data for this measure, as well.

Syntax

getMeasure(?string $measureLabel = null): ?array

Parameter 

  • $measureLabel string|null 
    The measure-label for which to get the measure-defintion-object. If NULL, the first measure-label will be used.

Return

 Returns the definition-object with following properties.

{
    "dimension" => 2
    "field" => al_id/COUNT/ERROR

    "_data_field" => drvd_measure_0

    "is_measure" => 1

    "name" => "ERROR"
    "descShort" - string (optional)
    "name_i18n" - string (optional)

    "formula" - string (optional) If given then this field will be calculated by this formula.

    "aggregation" => COUNT

    "model" => actionlog
    "modelfield" => al_id

    "group" => GRP_drvd_measure_0
    "group_fieldName" => ERROR
    "groupfield" => al_type
    "groupvalue" => ERROR

    "type" - string (optional) "int|float|string|date|datetime"
    "unit" - string (optional) A unit-label, e.g. "EUR", "%", ...

    "listCfg" => 

    "color_schema" => color
    "color_code" => 1
    "color_shade" => 2
    "color" => #CE0002

    "value": {               // Associative Array / Object. Array-Key is "Principal Value" (X-Axis) and 
                             // value is "Measure Value" (Y-Axis).
        "2024-09-11" => 509, // Example Data
        "2024-09-12" => 229,
        "2024-09-13" => 116,
        ...

     "summary": {            // Associative Array / Object, that caches the calculated summary of "value"-analysis.
         "cnt" => 4,
         "cntnotempty" => 4,
         "cntempty" => 0,
         "min" => 9,
         "max" => 509,
         "sum" => 863,
         "avg" => 215.75,
     }


getMeasureValues(...)

Gets the measure-values (Y-Axis) as a numeric array.

Syntax

getMeasureValues(?string $measureLabel = null): ?array

Parameter 

  • $measureLabel string|null 
    The measure-label for which to get the values. If NULL, the first measure-label will be used.

Return

A numeric array of measure-values. The value-elements are in the same (numeric) array-order as the principal-labels (X-Axis). NULL in case of an error.

[... ]


getMeasureColor(...)

Gets the (auto-determined) HEX-color-code for this measure series.

Syntax

getMeasureColor(?string $measureLabel = null): ?string

Parameter 

  • $measureLabel string|null 
    The measure-label for which to get the color-code. If NULL, the first measure-label will be used.

Return

 Returns the color hex-code.

"#FF0000"


getMeasureDataSeries(...)

Gets the principal-values (X-Axis, Array-Keys) and measure-values (Y-Axis, Array-Values).

Syntax

getMeasureDataSeries(?string $measureLabel = null): ?array

Parameter 

  • $measureLabel string|null 
    The measure-label for which to get the color-code. If NULL, the first measure-label will be used.

Return

Returns an associative array of the data-series for the given measure-label. Returns NULL in case of an error.

Str

{
    "<principal value>": "<measure value>",
    ...
}

 


getFieldValue(...)

Returns  summary-variables value for a given field.

Syntax

getFieldValue(string $field, ?string $aggregation = null, ?string $grpValue = null, ?string $clusterValue = null): mixed

Parameter 

  • $field string (required)

  • $aggregation string (optional) 
    E.g. "SUM", "AVG", "MIN", "MAX", "CNT", "COUNTDISTINCT"

  • $grpValue string (optional)

  • $clusterValue string (optional)


Return

Returns the value. Returns NULL if value is NULL or not present.


getFieldSummary(...)

Returns  summary-variables value for a given field.

Syntax

getFieldSummary(string $field): ?array

Parameter 

  • $field string

Return

Returns an associative array / object with following properties, called the "summary vars":

{
    "cnt" - int The number of defined values for this field.
    "cntnotempty" - int The number of not empty values for this field.
    "cntempty" - int The total number of empty or not given values.
    "min" - int|float|null The minimum value.
    "max" - int|float|null The maximum value.
    "sum" - int|float|null The sum of all values.
    "avg" - int|float|null The average value.
}


getFieldSummaryVar(...)

Returns one summary-variable's value for a given field.

Syntax

getFieldSummaryVar(string $field, string $var): mixed

Parameter

  • $field - string The measure field from which summary you request the variable.

  • $var- string Name of summary-variable of which value you request. Options are:
    • "cnt"
    • "cntnotempty"
    • "cntempty"
    • "min"
    • "max"
    • "sum"
    • "avg"


getDataGroupLabels()

Returns an array of all data-group-labels in this report. These labels are usually auto-generated when a "grouped-by"-report is created.

Syntax

getDataGroupLabels(): ?array

Return

Returns an array of all data-group-labels in this report.

[ ... ]


getDataGroup(...)

 Returns an info-array for a data-group (group of measure-datas / -labels).

Syntax

getDataGroup(?string $dataGroupLabel): ?array

Parameter 

  • $dataGroupLabel string

Return

Returns an array of all data-group-labels in this report. Returns NULL if the given data-group-label does not exist.

{
    "measureLabels": [ "<measure-label>", ... ]
}


getDataList()

Returns the actual report-data as a 2-dimensional data-record-list.

@param array|null $fields (optional, default NULL) Array of field-labels to be included in the result. If NULL then all fields (principal and measurements) are returned.


getEChartSeriesObject(...)

Returns an eChart-compatible result object for a given measureLabel parameter.


getEChartSeriesTreeObject(...)

Returns eChart-specific series-object with data in tree-structure.

array|null $params
[valueField] - string (required?) The field which shall be the main value, grouped.
[field_groupby_arr] - array (optional, default NULL) If not given, then the internal field-groupby-array is used.


setDynFields(...)

Sets one or more dynamic fields (aka "formula-fields").

[dynFields] - array (required) Numerical array of dyn-field declarations, as described:
    [field] - string (required) The field-label to be declared.
    [formula] - string (required) The formula to be used for calculation.
    [name] - string (optional)
    [type] - string (optional)
    [unit] - string (optional)
    [descShort] - string (optional)
    [color] - string (optional)

     

Formulas

Formulas can be applied to a Report Result Object. The formulas are applied on each data-row / -record one-by-one. The calculated value of a formula is stored in own "materialized" new data field / column.

Formulas can only be executed after the actual database-report-query.



Syntax

Within a formula you can use:

  • operators such as:   + - * /
  • fixed numbers as:   123 or 999.99
    Use dot-notation for digits.
  • use field-labels as variables (Formula-Vars) from your Report Result Object.
    Regard: You can access even their min,max,sum, ... values for that colum, too! (see below: Var-Extensions).
  • You can use parenthesis:   ( ... )

Example:
Assuming you have Report Result with two columns / fields "a" and "b".
You can create a third column "c" by applying a formula:

c =  a + b 


Formula-Var Extensions

Within formulas you can append the following extensions to the field-label / column.
An extension is separated by a ":" to actual field-label. There must not be any whitespaces in between.

  • cnt - Number of data-rows for that field (=should be same for all fields in that report).
  • cntnotempty - Number data-rows for that field, where a value is present - not NULL. (A zero "0" is counted, here!)
  • cntempty - Number data-rows for that field, where no value is set, being NULL.
  • min - The minimum value over all data-rows for this field.
  • max - The maximum value over all data-rows for this field.
  • sum - The sum of all values within this field.
  • avg - The average value of the given values for this field ( =  "sum" / "cntnotempty").

Example:

c =  a:sum * b 


Some Hints

Here a list of some hopefully valuable things to consider ...

  • Performance
    Self speaking: The faster a report is displayed the better. Depending on several factors executing reports can range from some miliseconds to several minutes.
    • Less fields to query is faster.
      Select only what you need.
    • Less involved (JOINed) adjacent datasets is faster.
      Sometimes for grouping it sufficient to use the foreign-column (typically named: fk_...) instead of a JOINed primary field of other dataset.
    • Less data in the dataset is faster. Of course. :)
    • Try to use filter / segments to reduce amount database-rows to be processed.
      Use / Create Database-Table-INDEXes on those filter-fields.
  • Data Structure: "2D - Tabelized" vs. "Tree-/Nested Structure"
    Both is possible to be handled by the Report Result Object. Just keep in mind, that some charts and visulizations are easier to render either one of those structure types.
  • Formulas / Calculations
    You can execute multiple simple (and maybe very fast) Data Report Queries and combine them within the Report Result Object by applying a formula and generate a new column / field on-the-fly.

Examples


Report and render Data-Table

A simple structure example what it looks like to execute a report and print as a HTML-Table.

TWIG Template

{# --- 1.) Prepare Report Query   #}
{% set reportQueryObj = hublify.datareport()
            .mainCluster(<example cluster>)
            .reports(<example params>)
            .filters(<example filter>) 
%}

{# --- 2.) Execute the report-query and get the fundamental data from DB #}
{% set report = reportQueryObj.report() %}


{# --- 3.) Render the report-result as html-table, using hublify.datatable #}
{{ hublify.datatable()
    .fromReport(report)
    .params({
        "columnHeaders": true,
    })
    .setHtmlTableClass('table') }}


SUM, COUNT, ...

The Report Query

This shows how to setup the report-query and executes it.

In this example, we will query the model (aka dataset) "actionlog" and want to get the total number, more precisely the COUNT, of all actionlog-record's for today.

In order to execute something like a simple SQL-statement such as "SELECT COUNT(*) FROM ...." we can accomplish this by using in Hublify Reports as a  Measurement-Field with aggregation. 
This is then compiled into an according SQL-Statement. 

{% set reportResult = hublify.datareport('actionlog')
    .reports([
        {
            "dataset": "actionlog",

            'field_measure': [                  // (required)
                {
                    'field': 'al_id',           // The primary-key, here of model "actionlog"
                    'aggregation': 'COUNT',     // Group-By-Aggregation Type. Possible values are:
                                                // "SUM|COUNT|COUNTDISTINCT|MIN|MAX|AVG"
                }
            ],

            'filter': {                         // (optional) Filter.
                'al_time_insert': {             // Here: Regard only actionlog-entries, inserted "today".
                    'search': 'today',
                }
            },
        }
    ])
    .report()                                   // (optional) This functions directly executes the Query!
%}


The Report Result

Basically, it contains

  • The data
  • Field-Meta-Infos


GROUP-BY ... (simple)

The Report Query

This shows how to setup the report-query and executes it.

In this example, we will query the model (aka dataset) "actionlog" and want to get the total number, more precisely the COUNT, of each actionlog-record's type ("OK","ERROR", "WARNING", "NOTICE", ...) for today.

{% set reportResult = hublify.datareport('actionlog')
    .reports([
        {
            "dataset": "actionlog",

            'field_measure': [                  // (required)
                {
                    'field': 'al_id',           // The primary-key, here of model "actionlog"
                    'aggregation': 'COUNT',     // Group-By-Aggregation Type. Possible values are:
                                                // "SUM|COUNT|COUNTDISTINCT|MIN|MAX|AVG"
                }
            ],

            "field_groupby_arr": [              // (required, for grouping) One or more GROUP-BYs.
                {
                    "field": "al_type",         // We want to group all same "al_type"s into one row.
                }
            ],
            'filter': {                         // (optional) Filter.
                'al_time_insert': {             // Here: Regard only actionlog-entries, inserted "today".
                    'search': 'today',
                }
            },
        }
    ])
    .report()                                   // (optional) This functions directly executes the Query!
%}


The Report Result

Basically, it contains

  • The data
  • Field-Meta-Infos


... as KPI-Grid

To render the Report as multiple KPI Cards, simply include that Hublify-Twig-Component:


{% include '@hublify/hublify/app/components/report/_hfy-report-component--kpi-grid.html.twig' with {

    report: reportResult,             // (required) The Report-Result-Object carries all data and lots of meta-infos.

} %}