Driving Dashboards from Formulas

We have 3 main dashboarding controls:

  • ReportingBarChart
  • ReportingPieChart
  • ReportingTable

Each of this can be used to create useful reports off of the records of a single eForm. Unfortunately at this stage they can only report off one eForm at a time when configuring using the properties panel.

To allow more comprehensive reporting, we have enabled the user to provide data for these charts from formulas. Using the DB_REPORT function you can retrieve data from all over the platform, and combine or reconcile them together to give you the exact resulting chart you are looking for.

Below are some explanation of how to write the formulas to drive these controls

Bar Chart

Example

HASH(
    "series", ARRAY(
        HASH("label", "1", /*optional*/"color", "red"),
        HASH("label", "2", "color", "green"),
        HASH("label", "3", "color", "blue")
    ),
    "panes", ARRAY(
        HASH(
            "title",  "Title 1",
            "labels", ARRAY("A", "B", "C"),
            "layers", ARRAY(
                HASH("type", "bar",  "data", ARRAY(1, 2, 3)),
                HASH("type", "bar",  "data", ARRAY(4, 5, 6)),
                HASH("type", "line", "data", ARRAY(7, 8, 9))
            )
        ),
        HASH(
            "title", "Title 2",
            "labels", ARRAY("A", "B", "C"),
            "layers", ARRAY(
                HASH("type", "bar",  "data", ARRAY(1, 2, 3)),
                HASH("type", "bar",  "data", ARRAY(4, 5, 6)),
                HASH("type", "line", "data", ARRAY(7, 8, 9))
            )
        )
    )
)

Pie Chart

Example

HASH(
    "series", ARRAY(
        HASH("label", "1", /*optional*/"color", "red"),
        HASH("label", "2", "color", "green"),
        HASH("label", "3", "color", "blue")
    ),
    "panes", ARRAY(
        HASH(
            "title",  "Title 1",
            "data",   ARRAY(1, 2, 3)
        ),
        HASH(
            "title",  "Title 2",
            "data",   ARRAY(4, 5, 6)
        )
    )
)

Table

Example

HASH(
    "columns", ARRAY(
        HASH("heading", 1, /*optional*/"width", 200, /*optional*/"category", "Category1"),
        HASH("heading", 1),
        HASH("heading", 1)
    ),
    "rows", ARRAY(
        ARRAY("Banana", 1, 2),
        ARRAY("Apple",  1, 2),
        ARRAY("Orange", 1, 2)
    ),
    /*optional*/
    "totals", ARRAY(
        1, 2, 3
    )
)

Any table cell may be a HASH structure with any of the following keys:

Key Value
value (required) a value to display in that cell.
style Any combination of ‘bold’, ‘italic’ and ‘underline’ separated by space.
colour A CSS colour code in which to display value text.
backcolour A CSS colour code controlling the background colour of the cell.
url A http://style hyperlink, which will be applied to all contents in the cell.
format A .NET format string to apply to the supplied value.
excelFormat An Excel style format string to be used when exporting to xlsx.

Related Articles