We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

new wikibook article - write rules with excel

Hi all,

In case it is useful for anyone else, in the wikibook I've written a new article describing a technique I use when writing a lot of rules for OpenClinica [1].

As the introduction goes:

"If you want to write more than a few rules, and don't want to set up the Rules Designer app, it is possible to make life a little easier using the xml tools available in Excel 2010. This method takes advantage of OpenClinica's xsd schema used to validate rule xml files to guide Excel into providing an interface for working with rule xml data."

So basically if you are comfortable writing rules in plain xml, using Excel in the way described makes the process a bit easier to handle.

[1] https://en.wikibooks.org/wiki/OpenClinica_User_Manual/WriteRulesWithExcel

Best regards,


  • haenselhaensel Posts: 602 ✭✭✭
    Hi Lindsay

    Very good work and thank you for sharing this. One thumb up from me.
    Unfortunately you added an additional http:// to the end of the link.

    Since is isn't possible to correct posts (older than 1 hour) I attach the corrected link here [1].


    [1] https://en.wikibooks.org/wiki/OpenClinica_User_Manual/WriteRulesWithExcel
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    Hi Christian,

    Thanks, I think I fudged the copy/paste there.

    I wanted to add also that in the Limitations section, I mention a xsl transform for collating ruleRefs by Target and splitting them again, which I have now updated.

    The other day I figured out how to copy a node set and modify elements without having to specify the node structure. This is handy because the OpenClinica rule xml download inserts a bunch of spaces in the Message text, which makes the upload and downloaded versions of the xml not equivalent. So the update copies everything, and if it comes across a Message node it'll normalize (collapse consecutive) spaces, which closes the loop of: write rules - modify for upload - upload - download - modify for editing - modify for upload - etc.

    Best regards,
  • mrodriguezmrodriguez Posts: 9
    Hi Lindsay

    I used your post the first time that I worked with rules, it was very helpful!

    Now I'm working in a study where I need to make more than one rule for the same target, and Openclinica doesn't allow it. I can upload the first one and then the second one, etc, but it is very slow and it may cause errors (e.g. forget upload one rule). 
    I need to upload all the rules at the same time. 
    Do you know if there is some way to manage it? 

    Kind regards,
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Glad it was useful! In the wiki article, in the "Limitations" section,
    there is a bit on grouping and ungrouping the XML by rules by target.

    The link goes to a XSLT file that does the conversion. All you need for
    that is a copy of the Java jar for saxon9he (it's free), and Java. The XSLT
    file has more comments on what it does, as well as the command line syntax
    for Saxon.

    So you would write the rules in Excel, export the XML, run the script then
    upload the output XML.
  • mrodriguezmrodriguez Posts: 9
    Dear Lindsay,

    I'm trying to follow your indications:

    - I copy the XSLT into a xml (I'm not shure if it is ok, but I didn't find xslt format)
    - I downloaded the saxon9he
    - Export rules to xml
    - Run the script: I don't know how run the script. If I'm not wrong, the script is the XSLT, I don't know how to run it. And I'm not sure if I am right because I have not used the saxon9he

    Please, could you explain me the process with more details?

    Kind regards,
  • mrodriguezmrodriguez Posts: 9
    Hi Lindsay,

    I forgot to say you that I use notepad++ to edit and view xml files. Could I run it using notepad?

  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    The XSLT script is instructions for modifying a XML file. It should not be
    pasted into the XML file. You can open these files in notepad, but in order
    to run them you need a XSLT 2.0 processor (such as Saxon) and Java, because
    Saxon is written in Java.

    The syntax for running the script on an XML file is in the comments at the
    top of the XSLT file. The following command collates the rule targets for
    upload to OpenClinica. There is another example command in the comments
    that does the opposite.

    java -cp "path\to\saxon9he.jar" net.sf.saxon.Transform -s:rulesForExcel.xml
    -o:rulesForOpenClinica.xml -xsl:collateOrSplitRuleRefsByTarget.xsl

    Open a command prompt in the directory where your XML file is. Put the path
    to the saxon9he jar in place, put the XML file name where rulesForExcel.XML
    is. Possibly Java isn't on your path either, in which case replace the word
    Java with the path to java.exe. After running the command, a file called
    rulesForOpenClinica.xml will be created in the same directory. This new
    file is the one to upload to OpenClinica.

    Also, make sure you're using the file called
    *collateOrSplitRuleRefsByTarget.**xsl*, not collaterules.xsl.
  • mrodriguezmrodriguez Posts: 9
    Hi Lindsay,

    Actually, we need to use directly XML to write rules (instead of write rules with excel and run the XSLT ). And we need to create different rules with the same target. E.g. we need to validate that the age is between 18 and 59 and we need to chech that the age is >18 and the inclussion criteria number 1 is true. To do that, we want to create 2 rules with the same target (age). 

    Do you have an XML to show an example about how to indicate and use multiple rules for just one target? 

    Thanks in advance,

  • mrodriguezmrodriguez Posts: 9
    Hi Lindsay, 

    I think I have already know how to write in XML more than one rule to the same target.we just added different rules into the same RuleAssignment.


  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Yes, that is what the xlst does.
This discussion has been closed.