+ Post New Thread
Page 3 of 3 FirstFirst 123
Results 31 to 44 of 44
MIS Systems Thread, SIMs and Command Reporter in Technical; Originally Posted by J_Pratt Thanks - it is working beautifully now. J Pratt Please could you explain what you did ...
  1. #31

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    Quote Originally Posted by J_Pratt View Post
    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

  2. #32

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,589
    Thank Post
    638
    Thanked 1,306 Times in 1,088 Posts
    Rep Power
    336
    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.

  3. #33

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    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):
    SIMS.net Reporting

  4. Thanks to LosOjos from:

    MikeW (16th February 2011)

  5. #34

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    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

  6. #35

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    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

  7. #36

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    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?

  8. #37

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    Quote Originally Posted by LosOjos View Post
    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

  9. #38

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    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)

  10. #39

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,589
    Thank Post
    638
    Thanked 1,306 Times in 1,088 Posts
    Rep Power
    336
    @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:

    <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...

  11. #40

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    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>
    Last edited by iceman; 20th February 2011 at 09:28 AM.

  12. #41

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,589
    Thank Post
    638
    Thanked 1,306 Times in 1,088 Posts
    Rep Power
    336
    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.

  13. #42

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    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

  14. #43

    Join Date
    Jun 2010
    Posts
    39
    Thank Post
    0
    Thanked 24 Times in 12 Posts
    Rep Power
    12

    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.

  15. #44

    Join Date
    Mar 2007
    Posts
    39
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    15
    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

SHARE:
+ Post New Thread
Page 3 of 3 FirstFirst 123

Similar Threads

  1. [SIMS] Command reporter paramters [Solved]
    By browolf in forum MIS Systems
    Replies: 5
    Last Post: 19th August 2011, 11:02 AM
  2. Replies: 1
    Last Post: 19th October 2010, 09:48 AM
  3. [SIMS] Java API for Command Reporter
    By vikpaw in forum MIS Systems
    Replies: 26
    Last Post: 17th July 2010, 09:11 AM
  4. [Sims] Command reporter working examples
    By browolf in forum MIS Systems
    Replies: 3
    Last Post: 1st July 2010, 12:45 PM
  5. urgent: Sims.net command line reporter (CLR)
    By russdev in forum MIS Systems
    Replies: 2
    Last Post: 20th September 2007, 02:00 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •