How Do I Make a Report Table?

How Do I Make a Report Table?

Background

A report is a seldom-seen VQL custom widget, sometimes called, “dollar report” ($report). We recently developed a form-based Builder version called, Actor/Object Table. Unlike a chart, which shows a value for one category, a report shows a value for two, intersecting categories. A report is a table with rows (usually actors), columns (usually objects), and cells (usually results). For some customers, we also offer tailor-made reports, with many intersecting categories per actor, called Analytics Reports.

The $report stage includes two sub-processes: rowGenerator and columnGenerator. The row generator populates the records. The column generator populates the header (the corner cell), the resulting text for the context of the row (the row header). and the template for the column. The template includes the text for the context of the column (the column header) and the process and text for all the cells in that column.

You can add separate filters to the report stage and the row generator, column generator, and template cell processes.

You can also set the default text string to render (e.g., a hyphen or a zero) if the template cell query fails.

Notes
Because the $report is a single stage, you can’t use the Analytics Debugger to trace program flow in the rowGenerator or columnGenerator sub-processes.

In the columnGenerator, you can add CSS properties as JSON key/value pairs to the header and cell in the columns and the template.

The template cell includes a process, which expects stages. Here, you can add logical, mathematical, and other MongoDB stages. The final stage is the $addFields with the text value to render in each cell of the body of the table.

Example VQL Code

Here's the minimum valid $report code, to show its structure…
{"title": "Statement Count",
 "subtitle": "Per object, and per student ",
 "filter": {}, 
 "process": [{     
     "$report": { 
         "rowGenerator": { 
             "query": { 
                 "filter": {}, 
                 "process": [{ 
                     "$frequentValues": {"path": "actor.id"}}, {
                     "$toCanonicalDisplay": {}}]}}, 
         "columnGenerator": { 
             "query": { 
                 "filter": {}, 
                 "process": [{ 
                     "$frequentValues": {"path": "object.id"}}, { 
                     "$toCanonicalDisplay": {}}]}, 
             "columns": [{
                 "header": {"text": ""},
                 "cell": {"result": {"text": {"$context": "row.canonical.display"}}}}],
             "template": {
                 "header": {"text": {"$context": "column.canonical.display"}},
                 "cell": {
                     "default": {"text": "-"},
                     "query": {
                         "filter": {
                             "actor.id": {"$context": "row._id"},    
                             "object.id": {"$context": "column._id"}},
                         "process": [{
                             "$count": "count"}, {
                             "$addFields": {"text": "$count"}}]}}}}}}]}
Notes
We've collapsed some of the whitespace in the JSON in this article, to make it easier to read.

VQL Cookbook

Now that you know a little about how to make a report, here are some tricks you can use to extend it.

Sort Rows

When setting-up rows, you can easily sort them alphabetically with a MongoDB $sort stage:
"process": [{
    "$frequentValues": {
        "path": "actor.id",
        "limit": 500}}, {
    "$toCanonicalDisplay": {}}, {
    "$sort": {"canonical.display": 1}}]
In the $sort stage, a value of 1 tells the stage to sort in ascending order.

Rotate column headers

Because objects often have long names, you can get more columns onscreen by rotating the column headers. 
"template": {
    "header": {
        "writing-mode": "vertical-lr",
        "transform": "rotate(180deg)",
        "columnName": {"$context": "column.canonical.display"},
        "text": "${value.columnName}"},
The first style rule sets the headers sideways, like the titles on the spines of books on a shelf. But, when set on the top of a table, they look better when spun 180°.

Freeze column headers

To let users always see the column headers, conventional report tables often have the top row “frozen”. You can do this in $report too using the position “sticky” style property, 
"header": {
    "position": "sticky",
    "top": "0px",
    "columnName": {"$context": "column.canonical.display"},
    "text": "${value.columnName}"},
The first style rule sets the position scheme of the container: the header row. You must include the second style rule, to set a value for the position.

Style headers and cells

The header and cell objects inherit style properties directly as child objects. You can add HTML and CSS markup in the content of the text child too. 
"header": {
    "background-color": "#F2F2F2",
    "color": "black",
    "font-weight": "normal",
    "padding": "5px",
    "vertical-align": "top",
    "columnName": {"$context": "column.canonical.display"},
    "text": "${value.columnName}"},
The default header background color is black, default text color is white, and default font weight is bold.

Values and formats you can put in cells in the table body

Count, with decimal formatting:
{"$count": "count"}, {
 "$addFields": {"text": "${value.count.toFixed(0)}"}}
Total duration in minutes, to one decimal place:
{"$sum": {"path": "meta.duration"}}, {
 "$addFields": {"value": {"$divide": ["$value",60000]}}}, {
 "$addFields": {"text": "${value.value.toFixed(1)}"}}
Total duration in seconds, as an integer with thousands separator:
{"$sum": {"path": "meta.duration"}}, {
 "$addFields": {"value": {"$divide": ["$value", 1000]}}}, {
 "$addFields": {"value": {"$trunc": ["$value", 0]}}}, {
 "$addFields": {"text": "${value.value.toLocaleString()}"}
These are just examples. You can use anything as a value: e.g., attempts, progress, score, etc.

Example Reports

The suggestions included in the "cookbook" section are just the beginning! Using those ideas, and more, here's what you can do…

 
Before — the MVP code from before   After — using the "cookbook recipes"



    • Related Articles

    • How Do I Tag Groups of Students?

      Our Veracity Launch LMS has a Learner Profile Field feature that lets you “tag” or label a student as belonging to a group that you designate. Then, you can use these metadata tags to streamline later operations on those students and groups in the ...

    • How Do I Edit Canonical Activities?

      What are canonical activities? As xAPI statements flow into the Veracity LRS, it builds a “canon” of names for activities. For example, the LRS can extract the names and hierarchy of courses and their nested lessons and interactions from the IDs, ...

    • What Types of Charts Can I Make in the LRS?

      In general, you can make charts in a custom dashboard using two methods: using our Chart Builder, or by programming our Veracity Query Language (VQL). Chart Builder Charts The Chart Builder and similar form-based wizards in the Veracity Learning LRS ...

    • How Do I Add ‘Storytelling’ to a Dashboard?

      If you only use the out-of-the-box dashboards, or have a custom dashboard with one widget, then you’re all set. But, if you decide to build a custom dashboard (or more) with many widgets, then you must consider the overall design. “Widgets” include ...

    • What can I ask an AI agent using the LRS MCP?

      Think of an AI agent with the MCP as a conversational interface to your LRS. Unlike the Query Builder or Chart Builder in the LRS, you can ask vague and general questions to an AI agent configured with the LRS MCP. However, if you use terms carefully ...