Using CommandReporter with SIMS .net
Introduction to CommandReporter with SIMS .net
SIMS .net offers powerful reporting features within the GUI, but sometimes you require scheduled report runs to export SIMS data. This is where CommandReporter can be of assistance.
CommandReporter.exe serves as a command-line tool accessible through the command prompt, batch files, or as part of a scheduled task. To utilize it, open a command prompt and navigate to the C:\Program Files (x86)\SIMS\SIMS .net folder. For a list of accepted program arguments, execute commandreporter.exe with a /? to display the help documentation.
When you run CommandReporter, you will need a SIMS .net account with the appropriate permissions to read and execute reports from SIMS .net. Typically, it is advisable to opt for a non-Windows Integrated logon, selecting a SIMS .net logon directly from System Manager.
Running a Basic Report
Here is an example of running the basic report using the Staff Car List as an example, this a simple report.
commandreporter.exe /USER:simsuser /PASSWORD:password /SERVERNAME:SIMSDB\SIMS /DATABASE:SIMS /REPORT:"Staff Car List" /OUTPUT:"Staff Car List.csv"
When you run this command, CommandReporter will login to SIMS, locate and run the report, then save the result to a file called “Staff Car List.csv”. If you do not add the /OUTPUT argument, the report data will display on screen as XML data. This can be useful for more advanced users, although CSV or XML (based on the filename) will be enough for most purposes.
Advanced Reporting
In SIMS .net, reports can also include parameters, such as a Start Date or On Roll, enhancing the dynamism of reporting by enabling data exports for specific date ranges or capturing changes since the last report run.
To specify report parameters, you must first gain an understanding of the available options. CommandReporter facilitates this by exporting and displaying parameter information when you execute commandreporter.exe with the /PARAMDEF argument. This action generates an XML document that comprehensively describes adjustable parameters. While you can disregard a significant portion of the XML output as it may not hold much relevance, you’ll notice entries commencing at the bottom of the XML document as follows: <Parameter id=”N2QdcDwgiBXcMrKXgxRBZQ==”>. These parameters are unique to the specific report you are running.
Given the potential complexity of reading XML, we recommend two approaches for improved readability. You can either utilize an online XML formatter or leverage Notepad++ with the XML Tools Plugin. Utilizing the XML Tools plugin within Notepad++ allows for neatly formatted XML with line breaks. It also offers the flexibility to revert the XML to a linearized format, suitable for use with the CommandReporter tool.
Parameters Example
Here is an example of Report Parameters that we have ‘pretty formatted’
<Parameter id="N2QdcDwgiBXcMrKXgxRBZQ==" subreportfilter="False"> <Name>Leaving Date</Name> <Type>Date</Type> <PromptText>Leaving Date is after</PromptText> <Values> <Date>2020-07-30T15:11:33</Date> </Values> </Parameter> <Parameter id="44Mm2cLgmdrX6L5HX0HWkQ==" subreportfilter="False"> <Name>Student Status</Name> <Type>List</Type> <PromptText>Student Status </PromptText> <Values> <ValidValue> <Id>On Roll</Id> <Code>1</Code> <Description>On Roll</Description> </ValidValue> <ValidValue> <Id>Pre-admission</Id> <Code>1</Code> <Description>Pre-admission</Description> </ValidValue> </Values> </Parameter> <Parameter id="EffectiveDate" subreportfilter="FALSE" bypass="TRUE"> <Name>EffectiveDate</Name> <Type>Date</Type> <Values> <Date>26/09/2023 00:00:00</Date> </Values> </Parameter> </ReportParameters>
Please note that we intentionally omitted the opening XML tag from the example because we need to discard a significant portion of the XML generated by CommandReporter for our purposes.
Within the XML content, you will find numerous user-definable parameters, each distinguished by attributes within their respective tags. Attributes are values enclosed within the tags, such as id=”N2QdcDwgiBXcMrKXgxRBZQ==”.
In the provided example, two specific parameters, “Leaving Date” and “Student Status,” can be customized to suit your requirements. You have the flexibility to modify the values within these tags as needed. It’s important to note that you are not obligated to specify all available parameters. If you happen to make an error during this process, CommandReporter will promptly alert you to any XML-related issues.
Complete XML Sample
Below is an updated XML illustration in which certain parameters have been adjusted. Additionally, we’ve included the necessary opening tag to ensure the XML’s validity.
<ReportParameters> <Parameter id="44Mm2cLgmdrX6L5HX0HWkQ==" subreportfilter="False"> <Name>Student Status</Name> <Type>List</Type> <PromptText>Student Status </PromptText> <Values> <ValidValue> <Id>On Roll</Id> <Code>1</Code> <Description>On Roll</Description> </ValidValue> </Values> </Parameter> <Parameter id="N2QdcDwgiBXcMrKXgxRBZQ==" subreportfilter="False"> <Name>Leaving Date</Name> <Type>Date</Type> <PromptText>Leaving Date is after</PromptText> <Values> <Date>2021-09-26 12:05:42</Date> </Values> </Parameter> <Parameter bypass="TRUE" id="EffectiveDate" subreportfilter="FALSE"/> </ReportParameters>
Once you are happy with your choices, use an Online XML tool or the XML Tools in Notepad++ to reformat the line into a single line. e.g.
<ReportParameters><Parameter id="44Mm2cLgmdrX6L5HX0HWkQ==" subreportfilter="False"><Name>Student Status</Name><Type>List</Type><PromptText>Student Status </PromptText><Values><ValidValue><Id>On Roll</Id><Code>1</Code><Description>On Roll</Description></ValidValue></Values></Parameter><Parameter id="N2QdcDwgiBXcMrKXgxRBZQ==" subreportfilter="False"><Name>Leaving Date</Name><Type>Date</Type><PromptText>Leaving Date is after</PromptText><Values><Date>2021-09-26 12:05:42</Date></Values></Parameter><Parameter bypass="TRUE" id="EffectiveDate" subreportfilter="FALSE"/></ReportParameters>
Formatting for CommandReporter
Before incorporating this parameter into CommandReporter, one more crucial step remains. Since we intend to use this line as an argument within CommandReporter, you must enclose it in double quotation marks (“). However, it’s worth noting that certain values within our line already employ double quotes for encapsulation. Consequently, we must escape these double quotes by substituting each instance with a backslash followed by a double quote (e.g., id=”somevalue” becomes id=\”somevalue\”).
Running the CommandReporter with Report Parameters
With formatting completed, we are now ready to construct the command line that will execute the report, accommodating the parameters accepted by the SIMS report system.
commandreporter.exe /USER:simsuser /PASSWORD:password /SERVERNAME:SIMSDB\SIMS /DATABASE:SIMS /REPORT:"Students" /OUTPUT:"LeftStudents.csv" /PARAMS:"<ReportParameters><Parameter id=\"44Mm2cLgmdrX6L5HX0HWkQ==\" subreportfilter=\"False\"><Name>Student Status</Name><Type>List</Type><PromptText>Student Status </PromptText><Values><ValidValue><Id>On Roll</Id><Code>1</Code><Description>On Roll</Description></ValidValue></Values></Parameter><Parameter id=\"N2QdcDwgiBXcMrKXgxRBZQ==\" subreportfilter=\"False\"><Name>Leaving Date</Name><Type>Date</Type><PromptText>Leaving Date is after</PromptText><Values><Date>2021-09-26 12:05:42</Date></Values></Parameter><Parameter bypass=\"TRUE\" id=\"EffectiveDate\" subreportfilter=\"FALSE\"/></ReportParameters>"
Keep in mind that every report will come with its unique set of parameters. Understanding parameter definitions provided when you used the /PARAMDEF argument in the CommandReporter tool is essential. Consequently, you may need to engage in some experimentation with the /PARAMS to ensure that you’ve included all the essential parameters that the report demands.
While a basic understanding of XML is advantageous in this context, employing Online XML tools or the XML Tools plugin within Notepad++ proves highly beneficial. These tools not only enhance readability but also assist in syntax validation, ensuring the integrity of your XML configurations.