Scorpion Ad-Hoc Reporting

The Ad-Hoc Reporting page allows authorized users to build, save, run, and export custom reports using data available within their Scorpion organization.  Ad-Hoc requires that an organization be a Premium enabled organization and that you are an admin of your organization to access it.  This location is where saved custom reports from Caveon will appear for you to be able to run, as well as where you can create and save your own reports.

To Access the engine choose Orgs from the main Scorpion Menu

From your Org menu select Ad-Hoc Reporting

On the reporting screen, you can see saved reports from the dropdown menu, and load it for editing, run it for reporting, or delete the report if it is no longer needed, or create a new report.  Note that Caveon created custom reports cannot be deleted and cannot be edited, those reports are labeled with a (Run Only) flag next to the report name.

Creating a report

To create a report, you need to first add a table that you want to work with.  We typically recommend starting with the Organizations table or the exams table.  To add a table, click the choose table from the drop down and choose Add Table

Once you have added a table, you can add additional tables that will appear by clicking on the choose tables again.

Once you have added tables, the various columns within a table will appear, click on the + at the end of the table row to add it to your report.  Note the reporting tool notes what column data types they are.

As you add columns to your report they will disappear from the available columns table with the exception of JSONB columns as they may contain nested data that you may want later.

As you scroll down you will see areas for Selected columns Joins, filter conditions and at the bottom a sample of the data that you have. As you change your columns, the results sample area dynamically updates to show you what your report looks like.

In your Selected Columns area you can reorder how the columns are presented, add filters, rename a column, parse out JSONB objects, or remove a column.  

To reorder columns click and hold on the three lines next to the column name to move it up or down and drag it to the desired location.

To Rename a column click on the "rename" label at the end of the column.

In the pop-up message enter the desired name and click Save Name.

The renamed column will show in parenthesis the new defined name 

And will also reflect in the preview area.

To filter on a column click on the funnel icon on the columns 

A filter column screen comes up allowing you to select operators to filter on 

Choose the appropriate operator.

If you are unsure of what your values look like you can click Load Sample Values or if you are certain of your data shape, enter the value down in typed values.  If you click on a value in the sample area it will bring it down to the main area.  Click Save Filter to apply your changes.

Rows with filters will have a colored in funnel next to them, the filter will also appear in the filter conditions.

if a filter is bad then you can click the x on filter conditions to remove it. 

For JSON B columns you will see a { } listed next to the name indicating that there is additional ways to separate this out.  Clicking on the { } will bring up the choose JSONB Element page

Select a column and then click Add Element Column

Note that it now adds another JSONB column.  You can repeat this on the parent object for as many elements as you want to extract

Once you have all the columns that you want from the extracted JSONB column you can simply remove the parent object.

For example the Examinees Info column would look this without parsing

However, when parsed out you can have it formatted exactly how you would like. 

Once your query is constructed how you would like, set your row limit and then choose Run Query to see the full results.

You can then choose to export using CSV or XLSX formats 

If you want to save your query for future use Click Save Query, enter a name and hit save.