[SIMS] SIMS.net Reporting

[MikeW]

I'm setting up scheduled reports in SIMS.net and want to run reports which will show behaviour incidents for the previous week/month. I can't find any mechanism within the SIMS reporting tool to do this. All I can specify is a range of dates which would have to be changed everytime the report was run - defeating the object of report scheduling. Am I missing someting obvious ?
TIA

[LosOjos]

I'm setting up scheduled reports in SIMS.net and want to run reports which will show behaviour incidents for the previous week/month. I can't find any mechanism within the SIMS reporting tool to do this. All I can specify is a range of dates which would have to be changed everytime the report was run - defeating the object of report scheduling. Am I missing someting obvious ?
TIA

Unfortunately, I don't think there is a way of dynamically setting date ranges from within SIMS, ruling out scheduled reporting for this purpose.

There is a way to do it though: use CommandReporter and set up a script to dynamically create a parameter file containing the date range you want, this can then be passed as a parameter on the CLI to run the report you want, and you can set the script to run in windows scheduled tasks.

CommandReporter can be found in your "\Program Files\SIMS\SIMS .net" folder, and the format is

CommandReporter /USER:username [/PASSWORD:password | /TRUSTED] /REPORT:"report name in SIMS .net" /PARAMFILE:"path to your parameter XML file" /OUTPUT:"path to output to including file name"

NOTE: you must use either /PASSWORD or /TRUSTED: /PASSWORD is used when you log in to SIMS using an SQL password, /TRUSTED is when SIMS logs in using Windows authentication

IN SIMS, set up the report as you want it, and make sure that the date range is set up so it can be selected at run time. Then the easiest way I find to work out what format your param file needs to be in is to run CommandReporter as described above, but add the /PARAMDEF option to it: that will make the output the default param file, so you can see what you need your script to write to be able to do what you want.

EDIT: as an alternative to writing a param file, you can just pass the full XML to an option called /PARAMS, I personally find the param file method easier to debug though, that's why I favour it.

[LosOjos]

Just in case it's any use to anybody looking to write their own scripts, here is one I came up with for Mike to help solve his problem. It'll run a pre-made SIMS report (Behaviour Incidents) and automatically set the filter to be from one week previous up until the day it is run.

Set WshShell = WScript.CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
Set ParamFile = fso.CreateTextFile("params.xml", true)

Dim strParams
'Create start of XML parameters
strParams = "<ReportParameters><Parameter id=""N+WhVUXPCJ3JfZcRyTsVEw=="" subreportfilter=""True""><Name>Date</Name><Type>DateRange</Type><PromptText>Date is between</PromptText><Values><DateRange><Start>"

'Calculate start date
strDate = DateAdd("d",-7,Date)
strStart = Year(strDate) & "-" & Month(strDate) & "-" & Day(strDate) & "T00:00:00"

strParams = strParams & strStart
strParams = strParams & "</Start><End>"

'Calculate end date
strEnd = Year(Date) & "-" & Month(Date) & "-" & Day(Date) & "T23:59:59"

strParams = strParams & strEnd
strParams = strParams & "</End></DateRange></Values></Parameter></ReportParameters>"

'execute command reporter
ParamFile.Write(strParams)
ParamFile.Close
WshShell.Run "commandreporter /user:<username> /password:<password> /report:""Behaviour Incidents"" /output:output.csv /paramfile:params.xml"

One irritation I've found with SIMS reports is that the Parameter ID value changes when the report is imported in to a different database, so that needs to be reflected in your scripts if you share them with anybody else, otherwise the filter is ignored and the report ran as though you ticked the "Accept All" option.

The bulk of the script is just writing the params.xml file, the structure of which can be found by initially running your report from CommandReporter with the '/PARAMDEF' option.

Also, as you can see above, your username and password are stored as plain text (unless you're using Windows authenication, in which case you replace the '/PASSWORD' option with '/TRUSTED') so it's probably a good idea from a security point of view to create a user in SIMS specifically for running the reports you need to with the minimum set of permissions required to run them.