Local API - Command Reporter Guide

Introduction

This document is fairly lengthy but covers a most useful tool - Command Reporter - which can save a lot of development time if used wisely. We have compiled a list of tips to help you get the most out of Command Reporter.

Optimisation Of Output

ID Fields 

In order to access ID fields the user must be a member of the Third Party Reporter permission group. Failure to grant this permission often does not stop reports from running but it does suppress the identity fields which can make the output useless.

Two sorts of ID fields usually exist for most entities:

• Internal ID - Integer which may have various names such as 'Base_Group_ID in classes' or 'Person_ID in Person'.
• External ID - GUID and did usually called external ID. Please note that there is a property called external ID for an 'Aspect' which is the exception to the rule because it is not a GUID.

Unique Tag Names

Our sample report extracts two columns with the same default name 'Surname' because it lists students and their parents.  The 'Caption' must be set to a unique value for each item in the report. Failure to do so will create a run time error from command reporter.

CLR 2

Spaces/Punctuation in Tag Names

If we use an XML tag such as ‘My Name’, which has a space in it, it will be renamed to ‘My_X0020Name’ which may well be unexpected. The conclusion is that it is simplest to avoid spaces in Tag Names. Other punctuation signs such as ", ', @, > and < are also inappropriate for tag names.

Multiple record sets

Command reporter will return a row for the cross product of each row in a multiple record set.  This can cause speed and data volume problems.

If an application requires

• Student Details (Average 1 per student / 750 per secondary school)
• Exam Results (Average 10 per student)
• Classes (Average 10 per student per year)
• Achievements (Average 10 per student per year)

Then 5 reports would be recommended.

• Student Details (750 rows)
• Student Exam Results (7500 rows)
• Student Class Memberships (7500 rows(per year)) (class id and student id)
• Classes (300 rows (per year) assuming 25 pupils per class)
• Achievements (7500 rows)

Using a single report would generate 750 (students) x 10 (exams) x 10 (classes) x 10 (achievements), or 750,000 rows, most of which is duplication

Exporting a Report

Open a report via the Report Designer either by pressing the Open button or choosing the menu ‘Design Reports’

Tip: This Window allows users to drag and drop reports into 'Favourites'.  This technique can be used to select a set of reports that are for a different focus and export them in a single file.

Select one or more reports and either right-click or press Tools | Export. NB: You can only multi select within the sub-tree and hence the tip above.

Press Open and select a file name and location for your export.

e.g.   My First Report #2.RptDef

Then press Export to complete the task.             

Tip: The output from a report export is a .RptDef file.  These are encrypted files which effectively prevents them being changed in transit.

Changing the Supplier of a Report

The Partner Support Team will supply contracted integrators with a utility, ReportManager.exe. This utility opens a ’.RptDef’ file and allows the reports within to have their ‘Supplier’ changed.

ESS insist that Partners follow this protocol and change the supplier name for Partner reports to reflect the origin of the report. By default the supplier would be the name of the training data school. Names cannot include ‘ESS’ or ‘SIMS’. 

If Your Company is ‘School Inc PLC’ then we recommend that you change the supplier to ‘School Inc PLC’. Alternatively you may use the name of the product such as ‘SIMS’ or ‘SIMS Discovery’ (in ESS’s case). Take care to ensure that the 'Supplier Name' given is not too generic such as ‘Visitor Log’ because many vendors might choose to create a product of the same name. In in our case ‘Discovery’ without ‘SIMS’ in the name would not be a great choice.

The reason for the requirement is so that schools can identify report providers and this helps with a school’s GDPR commitments.

ESS will offer a chargeable service to change the supplier of other reports if Integrators or non-partners fall outside of the criteria.

Process

• Locate Report Manager.exe
• Run it
• File Open the Report File (.rptdef) that you wish to import into a school.
• Select all of the Reports
• Enter the Supplier Name
• Press Change
• Save the file
• Don’t forget to use the new file (or overwrite the old one).

Distribution

Report definitions created by SIMS and who have had their supplier changed to clearly identify the provider may be distributed to schools free of charge if they are intended for manual execution within SIMS by their customers and would not attract a per-site integrator charge.

Report definitions provided by integrators to customers which are then executed automatically using some form of application, batch file, scheduled task or similar would incur the per-site charge as per integrator contracts with ESS regardless of how the automation process works.

File Types

• Report Definition files ('.rptdef') are obfuscated binary files 
• Parameter Definition files ('.paramdef') are text files containing xml

Integrators could choose to:

• Make files available on a web site for download on a web page
• Ship files with their installations alongside their own binary files
• Use their own applications to download files from the web

Please note that the appropriate MIME type needs to be set for the files. We have seen that bug appear in our own code and it would manifest itself as the corruption of the .rptdef file (which is encrypted). The clue comes from a different file size after download!.

Security of the report definition files

There is no personal information in most Rptdef files generated by integrators. It is however possible for a report definition to contain names and filters which could in theory make them contain personal data, for example.

Filter:

• Surname = Smith
• Forename = Jane
• Pregnant = True

However this type of report definition is typically not useful for integrators.

Importing Reports Programmatically

Pre-Requisites

If we assume that the integrator’s software is installed on a machine, that machine also must have SIMS installed and running on it. If this criteria is not met, then please refer the customer to their SIMS Support Unit who should be able to get SIMS and SIMS Reporting running on the machine independently of the integrator’s software.

Command Report Importer

This is installed with every SIMS 7 installation and is typically found in the c:\Program Files (X86)\SIMS\SIMS .Net folder.

The file <WinDir>\SIMS.INI file should contain an entry SIMSDotNetDirectory which is the source of truth as to the location of SIMS on that machine.

ESS makes sample wrapper code available for contracted integrators which makes the execution of the import a single line of code (plus error handling) e.g:

SIMSReportingEngine.Reports.Load(<.rptdef file name>,<Server>,<Database>,<User>,<Password>)

Alternatively the executable can be called directly in a similar way.

When to Import Report Definitions

Integrators should assume that:

• Reports imported may be deleted by users at any time
• Report import is a relatively fast process – a few seconds
• Report import on a daily basis is probably inconsequential to the school (but it may not be if it were to be run every few seconds).

As such we would recommend that reports are always imported just before they are run.

Execution of Reports Programmatically

Pre-Requisites

If we assume that the integrator’s software is installed on a machine then that machine also must have SIMS installed and running on it. If this criteria is not met, then please refer the customer to their SIMS Support Unit who should be able to get SIMS and SIMS Reporting running on the machine independently of the integrator’s software.

Command Reporter

This is installed with every SIMS 7 installation and is typically found in the c:\Program Files (X86)\SIMS\SIMS .Net folder.

The file <WinDir>\SIMS.INI file should contain an entry SIMSDotNetDirectory which is the source of truth as to the location of SIMS on that machine.

Prior to executing the report, the report must be either imported manually or via command report importer. Obviously if the report does not exist then an error will occur.

Report Execution

ESS makes sample wrapper code available for contracted integrators which makes the execution of the import a single line of code (plus error handling) e.g:

SIMSReportingEngine.Reports.Run(<report name>,<Server>,<Database>,<User>,<Password>)

SIMSReportingEngine.Reports.Run(<report name>,<Server>,<Database>,<User> ,<Password>,<paramdef file name>)

Alternatively the executable can be called directly in a similar way.

It is far simpler for integrators to use the code provided because it handles various scenarios for them, however code samples include all of the exemplar code which can, if needed be modified to meet their specific requirements.

Report Names

We recommend that unique names such as ‘Partner Product <Student> v1.00’ for example so as not to get confused with a report created by the school themselves. There have been a number of reported conflicts which are typically resolved by deleting the reports manually in SIMS; but the ‘versioned’ and ‘unlikely to copy’ model does save on support costs.

Processing Data from SIMS Reporting

If integrators use the sample code provided (or modify it to meet their needs) then an XML document is returned to the executing program.

Typically, most developers should be able to transform the data returned in to the format / model needed by their application.

Whilst reports can be controlled to a considerable extent, it is possible that the report will return more information than the integrator’s application needs.  In this case, we would recommend that the data is post processed before the data leaves the school’s SIMS server.  For example, filtering may not allow you to extract pupils born on a Monday, but if that was the requirement, the student data would be extracted via the command reporter (or other means) and then processed by the application to ensure that only those students born on a Monday are passed to the integrator’s application.

Checking Data Returned

Please note that some reports can still execute in part if the user does not have the full permissions required to run the report.  Please check the response to make sure that all required elements are returned.