[sims] CommandReporter param files and parameter IDs

[DoctorPolski]

First post incoming....

 

I have a SIMS report that runs successfully from within SIMS and I can run it from the command line and generate a params file for it. My intention is to change the dates for a certain bounding parameter dependant on values entered into another script. This parameter is a filter clause and is set to "Prompt at runtime" for just this reason but even so the report requires me to enter 2 "default" date values for 'Start' and 'End' dates.

 

The generated paramsfile has 2 issues for me:

 

1. The XML is not fully fleshed out by including the "default" start and end dates and therefore requires checking of the schema and/or guesswork in order to create the required XML.
   
2. The parameter ID seems to be dependant on the information entered into the clause in the report and I have no way of generating a new one. For example changing either of the "default" dates or the prompt text causes the ID to change.
   

 

Here is the relevant part of the paramsfile as generated using CommandReporter with the /PARAMDEF switch:

Active
DateRangeRange
Active during
[color="#FF0000"][/color]

You can see the blank element is where I hoped the "default" dates would be.

 

Using the inline schema I reckon the fully fleshed out version with example dates should look something like this:

Active
DateRangeRange
Active during
[color="#0000FF"]	
	
		2011-09-01T00:00:00
		2011-09-20T23:59:59
	
[/color]

 

However, with the completed params file sorted and saved as above and read back in by the CommandReporter this section seems to get ignored and the report runs with the date values which are stored in the actual report. Either the format of the element is wrong or the parameter ID is now wrong.

 

If the element is wrong how do I find out what the correct syntax should be? If the parameter ID is dependant on the contents of the parameter as some sort of encoded hash how do I generate it? My feeling is that the structure is right but the ID is now wrong but any help here would be very gratefully received.

 

Thanks in advance,

 

Polski

[vikpaw]

I don't think guessing will work as you've found. You need to find out why the values are not being output, and then fix that.

Can you try it without a prompt at run time, just hardcoded. This may populate the params file and allow you to just tweak the date data in the relevant tags?

I'm sure someone else had this a while ago, will just check that thread.

[vikpaw]

Ignore what i said, you need prompt at run time ticked.

Just need to put default dates in. Maybe put them in, in design mode and save it, then run it. That usually keeps them there for the next user.

Check this thread - especially the bit from about post 30 onwards.

http://www.edugeek.net/forums/mis-systems/45448-sims-command-reporter.html

[vikpaw]

Your parameter type is daterangerange is that correct or just a typo? When i did it before it was just daterange.

[DoctorPolski]

Thanks for the response vikpaw.

 

I read through that thread you linked, it came close but not close enough. None of those examples used a type of DateRange which it *appears* from the inline schema of the /PARAMDEF file I need to use.

 

As it seems you may have already discovered if you don't have the "prompt at run time" ticked then the generated param file has no mention of that particular element at all. Having "prompt at run time" ticked gives the example in my first post which just has an empty element. I have no choice but to supply dates in the report designer on SIMS so there are default dates in there.

 

The parameter type of "DateRangeRange" is what is generated by the CLR with /PARAMDEF. I have tried it with just "DateRange" to no avail.

 

Any thoughts on the parameter ID? This changes if I change the default dates in the report designer so how can it ever be correct in my params.xml when it is read back into clr?

 

Am I right in thinking that I need to sign NDA's and stuff just to get documentation for the CLR which is a part of the SIMS software we already license and use in school?

[LosOjos]

Any thoughts on the parameter ID? This changes if I change the default dates in the report designer so how can it ever be correct in my params.xml when it is read back into clr?

 

Having created a few automated reports using CLR, I can tell you the ParamDef changes whenever you save the report in SIMS, so you will need to check it each time you make an alteration. Once it works how you want it to though it won't be a problem (as long as you don't make any more changes to it!)

 

Am I right in thinking that I need to sign NDA's and stuff just to get documentation for the CLR which is a part of the SIMS software we already license and use in school?

 

Supposedly yes, however I have tried repeatedly to obtain said documentation to no avail (my local support don't know what I'm talking about). Trial and error is key, there are quirks but it is quite simple to use once you get the hang of it.

 

I did write a script for another member here a few months back that set the date range based on the current date, I'm just trying to find the thread... hopefully the code from that will help us figure out why your paramdef isn't working...

[LosOjos]

I've found the thread and can't see anything that I did differently (I would definitely make sure the "Type" is "DateRange" though).

 

One thing that does come to mind - are you using a ParamsDef XML file, or trying to pass the XML straight in to CLR? I have never had any luck passing XML, I always create an XML file and pass that as the argument.

 

[if you're interested, the thread I was referring to is here: http://www.edugeek.net/forums/mis-systems/66215-sims-net-reporting.html]

[DoctorPolski]

Thanks LosOjos, it's good to know the parameter ID can be set then forgotten.

 

I have a little progress I think. Here is my param file:

 

	Active
	DateRangeRange
	Active during
	
		
			2011-09-06T00:00:00+01:00
			2011-09-20T23:59:00+01:00
		
	

 

I have tried it with and without the timezone addition in the DateTime fields but it made no difference. The interesting thing is highlighted in red. When this value is set to ALL CAPS I get the output as if I was running the report without prompting at runtime, that is to say using the default dates in the report. If it is "True" as per the /PARAMDEF then I get no output at all, well I get the output file schema but with no actual data. Does that mean that having all caps is rejected and so it goes with the value in the report? If so and if "True" is correct then it must be trying to use my parameter but somehow failing.

 

I am using a param file rather than supply an xml snippet on the clr. That way I can edit the file and test.

 

If it matters the parameter is supplying the "Active during" filter/clause for Group Membership (Historical). The report allows 'active on' which reqires a single datetime field or 'active during' which requires 2 datetime fields.

[LosOjos]

I'm scratching my head with this one... I am going to assume that using "TRUE" simply causes SIMS to reject the XML and go for default values instead, as I use "True" successfully in my reports and theoretically it should be the same for you.

 

Other things that come to mind are that there's a problem somewhere else in your XML... are you including the XML schema? I don't, so if you do try removing it.

 

I know you have already said this is just a snippet, but just to confirm you are opening and closing your XML with "" and "" respectively?

 

If you really are at a loss with it, I don't mind taking a look at your full XML, just post it here or PM me and I'll take a look for you if you want me to.

[DoctorPolski]

Thanks again mate.

 

This is the full contents of my params.xml file:

 

	Active
	DateRange
	Active during
	
		
			2011-09-06T00:00:00
			2011-09-19T23:59:00
		
	

 

It represents my best shot at this. I've changed

DateRangeRange

from the /PARAMDEF output to

DateRange

as I agree that makes sense.

 

I did even try changing

to

so it matched the /PARAMDEF output but to no avail.

 

I have used the datetime with and without timezone, I have removed all the tabs so it is one unbroken line. I'm not sure what else I can try.

 

Thanks for your continued help though.

[LosOjos]

This is very odd, I can't see any problem whatsoever with your XML...

 

Just a couple of things to check:

- That the report runs as you would expect it to from SIMS

- That you are definitely using the parameter ID as given by the latest version of the report (re-run paramdef to make sure)

- That you have not ticked "accept all" to bypass the filter by default in SIMS before saving it (to check, open up the report filter page, click modify on the date filter and see if it is checked. If it is, un-check it, close the window, save the report and don't forget to get the new parameter ID from the paramdef)

[DoctorPolski]

Yep the report runs as expected in SIMS, I can easily check it's working by supplying and earlier start date and seeing 5 more lines in the onscreen output. The parameter ID is definitely correct, I check it every time with /PARAMDEF everytime I even look at the report design.

 

Now, on the window to modify the filter there is no option to "accept all" although I have seen that elsewhere. Interestingly when I run the report from within SIMS the pop-up window that asks you to supply the dates does have that option underneath but it is not checked.

 

The thing is though, the only way I can get the report to run with the default dates from the report is to change that True to TRUE. Otherwise my output contains xml schema for the output but no records.

[LosOjos]

Very strange... and I am finding it odd that your default values don't come out in the paramdef, just tried it on one of mine using a DateRange and that does.

 

Last idea I have is if you'll let me try the report on my system? If you go to "Reports > Export", then Zip it and either post it in here or PM me for my email, I'll try running it on my system and see if I can spot anything more from that (don't worry about data protection, only the fields used in the report are exported, no actual student data will be [just in case])

[DoctorPolski]

OK here is the report:

 

Timetable_Members_2.zip

 

Good luck.

[LosOjos]

I'm completely at a loss. It's no help to you I know, but the only conclusion I can come to is that there is a problem with that particular sub report ("Group Membership (Historical)") and CLR, because using any other date based filter in a sub report is working for me, but that one just won't.

 

Next step - what is it you're trying to extract? Perhaps there is another [CLR friendly] way we can go about it?

[DoctorPolski]

Right I have a result!!!

 

Thank you for trying that report, I'm glad it's not just me that it fails for.

 

What I wanted was a record of all the group memberships that were active during the given date range. This will be further enhanced by filtering on pupil name or similar to limit the result set. What I have done to achieve this is split the filter into 2 and asked for (pseudo query clause):

(all memberships started before the later date) AND (all memberships ended after the earlier date)

That basically gives me all the group memberships that are active during the date window. This is what the Active filter does within SIMS but fails on through the command line. Given the mess it outputs for the filter using /PARAMDEF on the command line and the fact it won't read back I'm going to go out on a limb and say this is a bug in CommandReporter for that filter at least.

 

Thank you so much for help LosOjos. Also very glad I have discovered EduGeek.

[LosOjos]

Always happy to [attempt to] help!

 

And welcome to EduGeek, I hope it's not the last time you find the place useful!

[vikpaw]

Right I have a result!!!

 

Thank you for trying that report, I'm glad it's not just me that it fails for.

 

What I wanted was a record of all the group memberships that were active during the given date range. This will be further enhanced by filtering on pupil name or similar to limit the result set. What I have done to achieve this is split the filter into 2 and asked for (pseudo query clause):

(all memberships started before the later date) AND (all memberships ended after the earlier date)

That basically gives me all the group memberships that are active during the date window. This is what the Active filter does within SIMS but fails on through the command line. Given the mess it outputs for the filter using /PARAMDEF on the command line and the fact it won't read back I'm going to go out on a limb and say this is a bug in CommandReporter for that filter at least.

 

Thank you so much for help LosOjos. Also very glad I have discovered EduGeek.

 

Glad you got it sorted it in the end. You'll be hooked now @DoctorPolski

 

Is your new report identical apart from the type of comparison your are doing with the filter? I.e. are you just changing 'Active during' to 'Active before date' or some such?

 

I'm just wondering if it's the same filter but just a different condition. If so, it's a strange one. At any rate, it's worth logging a case with Capita, as if it's a bug, it can be looked at and rectified. Or you may just have to log a change request to that functionality added!

[DoctorPolski]

Hi vikpaw, yeah I'm hooked already.

 

I'm using a different filter now. Rather than the bugged "Active" filter I'm now using a combo of "Start date"/"End date" to achieve the same goal. It makes the pseudo query much more complicated but achieve the same result.

 

How and where would I report such a thing to Capita? If it is part of the usual laborious escalation process I shan't bother. Code bugs ought to be referred from web devs like me straight to the coders at CCS.

[vikpaw]

I thought maybe that if no conditions on that filter worked it would be easy to replicate, if it's only the 'between dates' part, it should still be reported.

You'd have to go through the usual channels of starting a support incident. The fact that it's been replicated by @LosOjos gives you some backup.

I would create a case on SupportNet and so bypass any inane troubleshooting over the phone. If you put the case clear enough, they may just escalate straight away...

Then three weeks later tell you that is how it was designed and you need to log a change request , but if you're lucky and they agree it's a bug then they may just fix it overnight.

[LosOjos]

Sorry to re-raise this thread, but I've had sort of a break through with this I think...

 

Still not sure it will work for the original query, but I've discovered that to pass parameters on the command line, you should use an apostrophe (') in the place of quotes ("), and that the format for date ranges should be "YYYY-MM-DDTHH:MM:SS" everywhere except the effective date, which has the format "DD/MM/YYYY HH:MM:SS" for some reason...

 

Been banging my head all morning trying to get date filters to work!

[iceman]

LosOjos,

 

I have managed successfully to pass xml parameters directly to CR to do roughly what you are trying to do.

I did it via a batch file first to get it working but in the finished app I build up the parameter string and send the whole command line to CR via by creating a new process on the fly.

The relevant bit of code is below (in Pascal). The variables StrDateBefore and StrDateAfter are strings of the form 2011-05-12

params := '';
params := params + ' /PARAMS:"';
params := params + '';
params := params + '';
params := params + 'Mark date';
params := params + 'Date';
params := params + 'MarkDateBefore';
params := params + '';
params := params + '' + StrDateBefore + 'T00:01:00';
params := params + '';
params := params + '';
params := params + '';
params := params + 'Mark date';
params := params + 'Date';
params := params + 'MarkDateAfter';
params := params + '';
params := params + '' + StrDateAfter + 'T23:59:00';
params := params + '';
params := params + '';
params := params + '"';

 

The trick I found I needed was to add a time part to the date as well.

 

so the whole string as generated would be something like

/PARAMS:"Mark dateDateMarkDateBefore2011-05-12T00:01:00Mark dateDateMarkDateAfter2011-01-10T23:59:00"

 

then I put that string after all the other parameters that get sent to CR (sims username, report name etc)

 

It not much different to putting it in a file and passing the name of the file but doing it this way saves one more step and fully automates the process.

[LosOjos]

LosOjos,

 

I have managed successfully to pass xml parameters directly to CR to do roughly what you are trying to do.

I did it via a batch file first to get it working but in the finished app I build up the parameter string and send the whole command line to CR via by creating a new process on the fly.

The relevant bit of code is below (in Pascal). The variables StrDateBefore and StrDateAfter are strings of the form 2011-05-12

params := '';
params := params + ' /PARAMS:"';
params := params + '';
params := params + '';
params := params + 'Mark date';
params := params + 'Date';
params := params + 'MarkDateBefore';
params := params + '';
params := params + '' + StrDateBefore + 'T00:01:00';
params := params + '';
params := params + '';
params := params + '';
params := params + 'Mark date';
params := params + 'Date';
params := params + 'MarkDateAfter';
params := params + '';
params := params + '' + StrDateAfter + 'T23:59:00';
params := params + '';
params := params + '';
params := params + '"';

 

The trick I found I needed was to add a time part to the date as well.

 

so the whole string as generated would be something like

 

 

then I put that string after all the other parameters that get sent to CR (sims username, report name etc)

 

It not much different to putting it in a file and passing the name of the file but doing it this way saves one more step and fully automates the process.

 

Thanks for clarifying @iceman - between the lot of us we should crack this!

 

After playing with this a lot, I'm now convinced that the "date between" type parameter simply does not work from CLR - I'm going to report it as a bug.

 

No matter how I format dates, they get ignored by SIMS in any "date between" field, but if I pass them separately as "date after" and "date before", it works.

 

EDIT: every time I think I have it working it doesn't still just returning the dates I set in SIMS and ignoring my parameters.

Edited October 20, 2011 by LosOjos

[iceman]

@LosOjos

I found that as well. That's why I did it in a rather more verbose way, but at least I had more control.

PS you have a PM from me

[DoctorPolski]

I tried it with the "T" in the format of the datetime as per the xml and all other variations of ' and " and it simply does not work.

 

The file generated by /paramdef doesn't even show the format like it does for every other filter I have used.

 

In short I agree that the "date between" field is broken for CLR.

 

Thanks for clarifying @iceman - between the lot of us we should crack this!

 

After playing with this a lot, I'm now convinced that the "date between" type parameter simply does not work from CLR - I'm going to report it as a bug.

 

No matter how I format dates, they get ignored by SIMS in any "date between" field, but if I pass them separately as "date after" and "date before", it works.