[sims] Command Reporter & Parameters

[svelez]

Hello! I'm just wondering if anyone has any documentation for command reporter. I've searched online and there doesn't seem to be anything anywhere.

 

I've seen vague references to being able to pass report parameters to command reporter, but I haven't seen anywhere that explains how to do this.

 

Also, I'm not even sure command reporter is the best way to do what I am trying to do, so I will explain here. Would love any feedback! I'm trying to run a report everyday at 3:00pm that outputs any student who has recieved a negative on that day. I can't just ask command reporter to run the report, because it needs to input the current date for the 'Recorded On' for the 'Behaviour' sub-report. So I need to know how to pass the parameter to command reporter, and I also need to know if there is a way to automatically make that parameter the current date.

 

I plan to output it to my onedrive, use powerautomate to parse the csv into a sharepoint list, and then use the list data to automatically email the form tutor a list of the students. I have everything working just fine. If I manually upload the report to OneDrive, the process works fine, but I'd really like to automate it.

[3s-gtech]

/PARAMS:"Detention DateDateDate %yyyy%-%mm%-%dd%T01:00:00?"

 

Here's a pull from the batch file that runs one of my reports. Does this help? The Parameter id is an unknown - I'm not sure how you get that out.

[svelez]

This is a great start, thank you!

 

I see that there is a tag for Prompt Text. Does that mean I should set the subreport filter to prompt at runtime? Hoping someone else would know how to pull the parameter id. Would you mind sharing the whole batch file so I can take a look (minus usernames and passwords of course)?

[Boredguy]

I normally generate a "Default" paramdef xml file via the CommandReporter for the required report.

I edit it to replace the date to be XXXX-XX-XX and save it.

Finally I have a PowerShell scheduled task that reads the default xml, replaces the XXXX-XX-XX with the current date and saves the file as another copy which the script then subsequently calls.

 

eg my powershell code looks like....

$curDate = Get-Date -Format "yyyy-MM-dd"
((get-content -path C:\Scripts\MSTeamsClassExport_default.xml -Raw) -replace 'XXXX-XX-XX',$curDate) | Set-Content -Path C:\Scripts\MSTeamsClassExport.xml
$CommandReporter = "C:\progra~2\SIMS\SIMS~1.net\commandReporter.exe"
&$CommandReporter /USER:YourUserHere /PASSWORD:YourPasswordHere /OUTPUT:C:\Scripts\Output.csv /REPORT:'MS Teams Class Export' /QUIET /PARAMFILE:C:\Scripts\MSTeamsClassExport.xml

[3s-gtech]

This is a great start, thank you!

 

I see that there is a tag for Prompt Text. Does that mean I should set the subreport filter to prompt at runtime? Hoping someone else would know how to pull the parameter id. Would you mind sharing the whole batch file so I can take a look (minus usernames and passwords of course)?

 

 

for /f "tokens=1-4 delims=/ " %%f in ('date /t') do (
set dd=%%f
set mm=%%g
set yyyy=%%h
)
"C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe" /USER:username /PASSWORD:password /SERVERNAME:simsconnectedIP /DATABASENAME:schoolnameDFEnumber /REPORT:"SIMS_report_name" /PARAMS:"Detention DateDateDate %yyyy%-%mm%-%dd%T01:00:00?"  /OUTPUT:"\\servername\outputfolder\report.xml"

 

Should have included my current date code first time!

[RLR]

My method uses powershell but is fairly similar to the others except I edit the xml file

 

$XmlDocument = [xml](Get-Content "C:\Location\Of\ParamDef\File.xml")
$XmlDocument.ReportParameters.Parameter.Values.DateRange.Start = (Get-Date).AddDays(-1).ToString("yyyy-MM-ddTHH:mm:ss")
$XmlDocument.ReportParameters.Parameter.Values.DateRange.End = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ss")

Edited March 17, 2023 by RLR

[svelez]

My method uses powershell but is fairly similar to the others except I edit the xml file

 

$XmlDocument = [xml](Get-Content "C:\Location\Of\ParamDef\File.xml")
$XmlDocument.ReportParameters.Parameter.Values.DateRange.Start = (Get-Date).AddDays(-1).ToString("yyyy-MM-ddTHH:mm:ss")
$XmlDocument.ReportParameters.Parameter.Values.DateRange.End = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ss")

 

Edit: Nevermind. I ticked the prompt at run time button on the report and it's not generating the xml correctly. Still having trouble but making progress. Thanks again everyone!

 

I've generated the xml file, and I can see that the property "Start" is in it, but when I try to change the parameter using your code in PowerShell, it gives the error "The property 'Start' canno tbe found on this object." Any ideas?

Edited March 20, 2023 by svelez

[Esteban_Child_of_the_Sun]

Here is the documentation for it SIMS.net Third Party Reporting SDK.chm.zip

[RLR]

I've generated the xml file, and I can see that the property "Start" is in it, but when I try to change the parameter using your code in PowerShell, it gives the error "The property 'Start' canno tbe found on this object." Any ideas?

 

You might have to change the $XmlDocument.ReportParameters.Parameter.Values.DateRange to match your params file as the nodes might be named differently in your xml file. Each section in this command references the node level in the xml file.

 

Report Parameters is one node

Parameter is the next node

Values is the next node

 

Etc.

 

If you're able to share your xml I can possibly take a look but I have no way of testing it as we don't have SIMS anymore.

[svelez]

Thanks so much for taking a look. I now have the XML generating properly (I didn't have prompt at run time ticked at first). I'm now trying $xmldocument.reportparameters.parameters.values.date but it's also saying that does not exist.

 

params.xml

 

Edit: Okay, making silly typos. It's parameter not parameters. Working now. Thanks so much.

Edited March 20, 2023 by svelez

[svelez]

I just want to make one more post to first say THANK YOU to all who helped and to also outline the process in case anyone with my problem stumbles on this thread. Syncing SIMS reports to OneDrive is SUPER powerful and will significantly change the way the school runs, hopefully easing workload for both teachers and support staff (I know I sound a bit too excited about this, but I feel eduGEEK is the best place to geek out about automated SIMS reports).

 

Step 1:

Create a report. My goal is to get a report of all negatives given on a day to generate a detention list and inform form tutors. The tricky part of all of this is that I need negatives given on the day the report is run. When I first set up the report, I added a sub filter on the behaviour (Recorded on = 16/03/2023). Then I ran the report once (this is important). After running it one time and making sure everything looked as it should, I went back to the sub-filter and selected Prompt at Runtime.

 

Step 2 - Get the parameters for your report:

Create a .bat file (you can do this in notepad) and add the following:

start /d "C:\Program Files\SIMS\SIMS .net" CommandReporter.exe /USER:USERNAME /PASSWORD:PASSWORD /SERVERNAME:SERVERNAME /DATABASENAME:DATABASE /REPORT:"Report Name in SIMS" /PARAMDEF /OUTPUT:"C:\PATH\params.xml" /QUIET

 

You'll need to add in your SIMS username, password, server name and database name. Server name and database name can be found by opening SIMS and going to Help -> System Information.

 

Once you have everything in, save the .bat file and run it by double clicking on it. This will generate an xml file. Open it in notepad and you'll be able to find the parameters you've set to Prompt at Runtime. Mine look like this:

 

 

  Recorded On  

 Date  

 Recorded On  

     

  2023-03-16T11:25:29    

  

 

My goal is to replace the value between the tags with the current date. For this, I need to run a powershell script.

 

Step 3 - Powershell

 

The following script will edit the xml file with the current date:

 

[color=#000000][font=Calibri]$xmldocument = [xml](Get-Content "C:\PATH\Params.xml")

[/font][/color][color=#000000][font=Calibri]$xmldocument.reportparameters.parameter.values.date = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ss")[/font][/color]
[color=#000000][font=Calibri]$xmldocument.save("C:\PATH\Params.xml")[/font][/color]

 

The first line loads the Parameter file you generated in step 1. The second line plugs in today's date. The third saves the file.

 

Step 4 - Back to the bat file

 

Go back to your bat file. Now you want to alter it so that it runs the report using the parameters file that you generated and that has been updated with the powershell script. This is what my bat file looks like (with username, password, servername and databasename redacted:

 

start /d "C:\Program Files\SIMS\SIMS .net" CommandReporter.exe /USER:USERNAME /PASSWORD:PASSWORD /SERVERNAME:server /DATABASENAME:database /REPORT:"Daily Negatives Report (For Detentions)" /PARAMFILE:"C:\PATH\Params.xml" /OUTPUT:"O:\SIMS Reports\DailyNegatives\detentions.csv" /QUIET

 

The O:\ drive is mapped to my OneDrive account. This is important because I am going to interface with this report using PowerAutomate

 

Step 5 - Windows Task Scheduler

 

I can't actually give a step-by-step on this because IT has all of us locked out of Windows Task Scheduler. I am (in)patiently waiting for IT to either give me access to task scheduler or do it for me. It looks very straight forward. Just do some googling about how to schedule running a .bat file and a powershell script.

 

What I want to do is schedule a run of the powershell script first everyday at 14:50. Then I want to schedule a run of the .bat file everyday at 15:00.

 

Step 6 - Power Automate

 

When the .bat file runs at 15:00, a csv file will automatically appear in one of my onedrive folders. In power automate, you can create a new flow that is triggered whenever a file is created in a specific folder. The Encodian connector allows you to parse csv files so they can be added to a sharepoint list (see here: https://www.encodian.com/blog/parse-a-csv-file-and-add-content-to-a-sharepoint-list/). Once you have the information in an SP list, you can do whatever you want with it. In my case, I'll be emailing form tutors a list of their students who have detention so they can inform them during PM registration.

 

I will also add the detentions to a shared spreadsheet so year teams can track it. The person who gave the detention will also get an Approval that will allow them to indicate whether or not the detention has been attended. Year teams the following morning will be emailed a list of students who did not attend.

 

I hope this is helpful to somebody. One more thanks to everyone who helped me.

[Boredguy]

You actually don't have to have the 2 Powershell and Batch files.

 

You can call CommandReporter directly from a Powershell script as in my example earlier.

 

We run all our SIMS Scheduled Tasks on the server with a specific account instead of running it on staff workstations. Might make it little harder to get the file into OneDrive, but could have it e-mailed to you and have the PowerAutomate save the file into your OneDrive that way and complete the processing from there.