SIMs and Command Reporter

Printable View

Show 25 post(s) from this thread on one page

3rd February 2011, 12:09 PM
iceman

Quote:

Originally Posted by J_Pratt

Thanks - it is working beautifully now.

J Pratt

Please could you explain what you did to make it work as I am having the same problem. I have a report focussed on student with a subreport on attendance. I filter attendance to be after a certain date and before another one, currently very far apart. These are what I want to set via parameters. I ran the report with the \PARAMDEF switch but couldn't see any reference to my dates in it so don't know what to change (or even how to format the date if I did!) I even set the repoprt to prompt on running but got the same paramdef output. At the risk of boring you my paramdef output is below.

Howard

SIMS.net Command Line Report Exporting Utility 7.136.29.0
Copyright ¸ Capita Business Services Ltd 1984-2010. All rights reserved.
Logging into to SIMS.net as user hom-it
Getting reports for user hom-it
Report 'HOM_ExportAllSessionAattendanceMarksXMLS' found.
Generating Report Parameter Definition file
<?xml version="1.0" encoding="us-ascii"?><ReportParameters><xs:schema id='ReportParameters' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:msdata='urn:schemas-microsoft-com:xml-msdata'
attributeFormDefault='qualified' elementFormDefault='qualified'>
<xs:element name='ReportParameters' msdata:IsDataSet='true' msdata:Locale='en-GB' msdata:EnforceConstraints='true'>
<xs:complexType>
<xs:choice maxOccurs='unbounded'>
<xs:element name='Parameter'>
<xs:complexType>
<xs:sequence>
<xs:element name='Name' type='xs:string' minOccurs='0' msdata:Ordinal='0' />
<xs:element name='Type' type='xs:string' minOccurs='0' msdata:Ordinal='1' />
<xs:element name='PromptText' type='xs:string' minOccurs='0' msdata:Ordinal='2' />
<xs:element name='Values' minOccurs='0' maxOccurs='unbounded'>
<xs:complexType>
<xs:choice>
<xs:element name='Logical' type='xs:string' minOccurs='0' maxOccurs='1' />
<xs:element name='String' type='xs:string' minOccurs='0' maxOccurs='1' />
<xs:element name='Integer' type='xs:string' minOccurs='0' maxOccurs='1' />
<xs:element name='Date' type='xs:dateTime' minOccurs='0' maxOccurs='1' />
<xs:element name='ValidValue' minOccurs='0' maxOccurs='unbounded'>
<xs:complexType>
<xs:sequence>
<xs:element name='Id' type='xs:string' minOccurs='1' />
<xs:element name='Code' type='xs:string' minOccurs='1' />
<xs:element name='Description' type='xs:string' minOccurs='1' />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name='IntegerRange' minOccurs='0' maxOccurs='1'>
<xs:complexType>
<xs:sequence>
<xs:element name='Min' type='xs:string' minOccurs='1' />
<xs:element name='Max' type='xs:string' minOccurs='1' />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name='DateRange' minOccurs='0' maxOccurs='1'>
<xs:complexType>
<xs:sequence>
<xs:element name='Start' type='xs:dateTime' minOccurs='1' />
<xs:element name='End' type='xs:dateTime' minOccurs='1' />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name='id' form='unqualified' type='xs:string' use='required' />
<xs:attribute name='subreportfilter' form='unqualified' type='xs:string' use='required' />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema></ReportParameters>Generating Complete
3rd February 2011, 12:23 PM
vikpaw

Have you got default dates entered in the report, so that when you get the above export, it has some data in it as an example? This was suggested by losojos earlier.

I can see some Date and DateRange elements, but no actualy data in between the tags.
3rd February 2011, 12:30 PM
LosOjos

As Vik rightly says, you need to set default dates on your report so that the Paramdef contains example data, then you can see what you need to modify in order to run your report.

I wrote a script a short while back for dynamically setting date ranges for Command Reporter, see this thread (it might help you write your own script):
http://www.edugeek.net/forums/mis-sy...tml#post593543
3rd February 2011, 06:29 PM
iceman

Yes I do.
I tried it with the dates simply set as part of the filter and then again with the filter ticked to say prompt at runtime (but with the same dates in the boxes, so these ought to be the default?). I am assuming that the dates I set as my filter are my parameters. Unless there is somewhere else where I should be explicitly naming parameters? The report is correctly filtering with the dates set in it but the earlier one is set to 30 Aug 2010. Obviously as time goes on the amount of data is going to become huge.
Howard
3rd February 2011, 06:33 PM
iceman

Losojos, I can see what your script is doing but , like you say, I can't see the parameters I need in the output I get with /PARAMEF
3rd February 2011, 08:21 PM
LosOjos

Are the dates you're trying to filter part of the report filters or are you trying to set the effective date of the report?

Try setting an output file, then upload the resulting XML file here please?
5th February 2011, 07:35 PM
iceman

Quote:

Originally Posted by LosOjos

Are the dates you're trying to filter part of the report filters or are you trying to set the effective date of the report?

Try setting an output file, then upload the resulting XML file here please?

My report already had an output file (as a csv not xml). I'm still unsure what you mean by parameter. I know what a parameter is - (I've taught computing for 25 years!) - but I'm not sure what how SIMS lets us refer to them. In case its any help this is what my report definition looks like. You can see that I am setting default dates in the filter (also tried ticking the 'prompt at runtime' box but I don't really want to do this as these reports run overnight. I can see no mention of parameters or how to define them anywhere in the report designer. There is nothing under 'filter students' as I'm not filtering the students, I'm filtering the date in the attendance sub report. (although even the word 'filter' is a bit odd. I'm used to using 'select' in SQL.)

Interestingly this time when I ran it with /paramdef I got no output (well, only that shown below the report definition shown here.
Howard

Report Name: SITKA_AllSessionAttendance

effective on: 05/02/2011

Data area: Student - Population: On roll

Data Fields: Admission number, UPN, Surname, Forename, Legal Surname, Legal Forename, Gender, Reg group, Year group; for attendance marks of each student where Mark date is after 30 Aug 2010 and Mark date is before 05 Feb 2035: Mark, Mark description, Mark date, AM/PM, Session, Day of week, Month, Statistical meaning

Filter Students

Use the default sort order: Surname, Legal Forename

Default Output: Text

Report Title:

the output with the /paramdef switch

SIMS.net Command Line Report Exporting Utility 7.136.29.0
Copyright ¸ Capita Business Services Ltd 1984-2010. All rights reserved.
Logging into to SIMS.net as user hom-it
Getting reports for user hom-it
Report 'SITKA_AllSessionAttendance' found.
Generating Report Parameter Definition file
Generating Complete
7th February 2011, 10:14 AM
LosOjos

If you want to be able to change the date range from Command Reporter, you must tick 'prompt at runtime' on any filters you apply and want to be able to change. Command Reporter will not actually prompt for input, instead it takes an XML file defining the parameters, so fully automatic execution of the report is still possible.

The reason you get no output from /PARAMDEF is that you haven't ticked prompt at run time, so SIMS doesn't give you the option to change any parameters. Try running the Command Reporter again with the /PARAMDEF tag and 'prompt at runtime' ticked, but also define an output file so we can see exactly what your paramdef file should look like (just add /output:"filename.xml" to the end of your call to Command Reporter)
7th February 2011, 10:33 AM
vikpaw

@iceman not sure how you're defined the output file, as by default it becomes a csv, well it did for me.
basically, it's probably overwritten your old output file. just open that and it should be the xml output.

The output you've described above is correct, and it should update the output file for you.

As an example, i've run one of my own reports that have a subfilter on dates, it's for medical events.

With the output file set, and paramdef switch i get a full page of xml, at the end after </xs:schema> and before </ReportParameters> i get the key parts i believe you are missing:

Quote:

<Parameter id="PZliJoDsjLeQr6G/EyWPIA==" subreportfilter="True">
<Name>Date</Name>

<Type>DateRange</Type>

<PromptText>Date is between</PromptText>

- <Values>
- <DateRange>
<Start>2010-09-18T13:46:25</Start>

<End>2010-12-15T13:46:25</End>

</DateRange>

</Values>

</Parameter>

</ReportParameters>

So first i ran this:

Code:

commandreporter /trusted /report:"bisj - medical events by date" /paramdef /output:"test.xml"

producing the parameter file.

Next running this:

Code:

commandreporter /trusted /report:"bisj - medical events by date" /output:"results" /paramfile:"test.xml"

Gave me a file called results.csv

I know you've probably tried something similar, but maybe this helps...
20th February 2011, 08:51 AM
iceman

Sorry I've not replied earlier but I've been away. VikPaw - that seems a very clear example, thank you. I'll try these suggestions again and post my results (may be a few days as I'm not around again for a while). I appreciate the efforts being made. It would be good to get this sorted as although my current system works - exporting everthing after august 2010 - the output file is getting unnecessarily bigger and bigger!

- got my paramdef file now, looks like below. will try editing it and running it with CR.

Code:

<?xml version="1.0" encoding="us-ascii" ?> - <ReportParameters> - <xs:schema id="ReportParameters" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> - <xs:element name="ReportParameters" msdata:IsDataSet="true" msdata:Locale="en-GB" msdata:EnforceConstraints="true"> - <xs:complexType> - <xs:choice maxOccurs="unbounded"> - <xs:element name="Parameter"> - <xs:complexType> - <xs:sequence> <xs:element name="Name" type="xs:string" minOccurs="0" msdata:Ordinal="0" /> <xs:element name="Type" type="xs:string" minOccurs="0" msdata:Ordinal="1" /> <xs:element name="PromptText" type="xs:string" minOccurs="0" msdata:Ordinal="2" /> - <xs:element name="Values" minOccurs="0" maxOccurs="unbounded"> - <xs:complexType> - <xs:choice> <xs:element name="Logical" type="xs:string" minOccurs="0" maxOccurs="1" /> <xs:element name="String" type="xs:string" minOccurs="0" maxOccurs="1" /> <xs:element name="Integer" type="xs:string" minOccurs="0" maxOccurs="1" /> <xs:element name="Date" type="xs:dateTime" minOccurs="0" maxOccurs="1" /> - <xs:element name="ValidValue" minOccurs="0" maxOccurs="unbounded"> - <xs:complexType> - <xs:sequence> <xs:element name="Id" type="xs:string" minOccurs="1" /> <xs:element name="Code" type="xs:string" minOccurs="1" /> <xs:element name="Description" type="xs:string" minOccurs="1" /> </xs:sequence> </xs:complexType> </xs:element> - <xs:element name="IntegerRange" minOccurs="0" maxOccurs="1"> - <xs:complexType> - <xs:sequence> <xs:element name="Min" type="xs:string" minOccurs="1" /> <xs:element name="Max" type="xs:string" minOccurs="1" /> </xs:sequence> </xs:complexType> </xs:element> - <xs:element name="DateRange" minOccurs="0" maxOccurs="1"> - <xs:complexType> - <xs:sequence> <xs:element name="Start" type="xs:dateTime" minOccurs="1" /> <xs:element name="End" type="xs:dateTime" minOccurs="1" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="id" form="unqualified" type="xs:string" use="required" /> <xs:attribute name="subreportfilter" form="unqualified" type="xs:string" use="required" /> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> - <Parameter id="qwdZjJLqbgeq0Mz9V90pHA==" subreportfilter="True"> <Name>Mark date</Name> <Type>Date</Type> <PromptText>MarkBeforeDate</PromptText> - <Values> <Date>2011-02-21T08:55:23</Date> </Values> </Parameter> - <Parameter id="G4eLZ0AEAuyM7z6pd69+8Q==" subreportfilter="True"> <Name>Mark date</Name> <Type>Date</Type> <PromptText>MarkAfterDate</PromptText> - <Values> <Date>2010-08-30T09:21:30</Date> </Values> </Parameter> </ReportParameters>
20th February 2011, 11:30 AM
vikpaw

cool.
See there are the bottom, now you have parameter tags and value tags for your MarkBeforeDate and MarkAfterDate from the subreport filter, which is currently bound to 30th august 2010 to 21st feb 2011.
So if you modify those parts of the file and re-run, you should get the desired output. First though, do as i did, and just run the output file back in unchanged, to make sure it's all working and you've got the syntax and all correct.
20th February 2011, 09:16 PM
iceman

Thanks for your help VikPaw. Using parameters opens up a whole new world of possibilities. Might even get sims to do something useful for me <g>. Shame SIMS has to go about it in such a convoluted way. What's wrong with simply '/MarkBefore:2011/02/20' on the command line like other DOS programs? Nevermind; I will be using a program that I wrote in Pascal to actually run the CR line so I guess I simply use my code to rewrite the param file with the dates I want (don't care about the time so I will leave that as is) and all will be fine! I'll try that when I get back from the mountains. Thank you very much. Howard
21st February 2011, 09:45 AM
PhillipHamlyn

Command Parameters vs. Input Files

Iceman;

I can probably shed some light on that design decision; we have often found problems with the DOS limitaiton of >230ish characters on a command line - using a response file removes that problem, which often occurs when the path to the utility is taken into account and the command shell in "homed" into another directory.

Hope that explains the choice.

Phillip Hamlyn
Capita.
25th February 2011, 06:57 PM
iceman

Philip, Thank you, I can see that makes sense given the dos line limit. But I don't see why we need to jump through so many hoops to get the paramdef file in the first place, then have to duplicate nearly a page worth of text exactly right except for the small bits that need to change. I can see why you cannot use positional parameters but even using a file to pass the parameters they could be done as simple named parameters - even typed ones. But parameters are not even mentioned in the report design. It appears one has to provide a textual prompt to a user that will never exist and then that prompt is used as the identifier for the parameter, at least it serves to identify it to me if not to the SQL. It all seems a very unusual way of doing it.
Howard

Show 25 post(s) from this thread on one page