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

    • 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 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, ...

    • 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 ...

    • Will LRS Reporting Work with a SCORM Package?

      No. Although an authoring tool may offer the option to enable “Report to an external LRS” for packages other than xAPI and cmi5, those other SCORM or AICC packages do not send xAPI statements to an LRS. The courseware player in each package matches ...

    • Will Students See Errors in an xAPI Course with No LRS?

      If you develop an xAPI course package and don't yet have an LRS, or the LRS is down, then the student should not see any error messages. It will depend on the authoring tool you use. To be sure, you should test it. We've gotten more questions about ...