OpenClinica User Manual/WriteRulesWithExcel

Introduction

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.

Limitations

This method is best for DiscrepancyNote or EmailAction rule action types, because these do not have repeatable child nodes. However these two action types are easily the most common, and working with a large number of rules is the reason for doing this in the first place. It is also limited to one rule action per target, because the OpenClinica rules validator requires that RuleRefs with the same Target are grouped in the same RuleAssignment node. A xsl transform for converting back and forth between the format Excel wants (no grouping) and OpenClinica wants (grouping) can be found here.

For the other action types, each child node (typically a set of DestinationProperty nodes) would need to be written as separate rule assignments, since the Target and RuleRef OID need to be in the same table as the rule action nodes. Whether or not this would have any negative impact OpenClinica behaviour was not tested, mainly because having separate rule definitions for each action type is easier to maintain, even if it means there are occasionally some duplicate rule definitions.

Steps

Prepare XSD file

1. Make a copy of tomcat/openclinica.data/rules/rules.xsd (or get it from the source files)
2. Change 'maxOccurs="unbounded"' to 'maxOccurs="1"' for the RuleRef element, and all RuleRefType elements (relevant snippet shown below). This lets Excel export the data in the correct format. Otherwise, Excel will refuse to export and complain about the data being a 'list of lists'.

    <xs:element name="RuleAssignment" type="RuleAssignmentType"/>
    <xs:complexType name="RuleAssignmentType"><xs:sequence>
            <xs:element ref="Target"/>
            <xs:element ref="RuleRef" maxOccurs="1"/>
        </xs:sequence>
    </xs:complexType>

    <xs:element name="RuleRef" type="RuleRefType"/>
    <xs:complexType name="RuleRefType">
        <xs:sequence>
            <xs:element ref="DiscrepancyNoteAction" minOccurs="0" maxOccurs="1"/>
            <xs:element ref="EmailAction" minOccurs="0" maxOccurs="1"/>
            <xs:element ref="ShowAction" minOccurs="0" maxOccurs="1"/>
            <xs:element ref="HideAction" minOccurs="0" maxOccurs="1"/>
            <xs:element ref="InsertAction" minOccurs="0" maxOccurs="1"/>
             <xs:element ref="EventAction" minOccurs="0" maxOccurs="1"/>
        </xs:sequence>
        <xs:attributeGroup ref="RuleRefAttributeType"/>
    </xs:complexType>

Prepare Spreadsheet

Picture of mapping a OpenClinica rule assignment node to a spreadsheet

Picture of mapping a OpenClinica rule definition node to a spreadsheet

1. Create new blank Excel spreadsheet
2. Go to Developer tab and find the XML group (if the tab is not there, go to File->Options->Customize Ribbon and tick 'Developer')
3. Click Source
4. In the XML Source window, click XML Maps
5. Click Add
6. Select the edited rules.xsd file from earlier
7. In the Multiple Roots window, select RuleImport as the root node
8. Click OK
9. There should now be a RuleImport_Map tree displayed in the XML Source window
10. Ctrl+click to select the Target-value, RuleRef-OID, and desired Action node.
11. Drag and drop the selection onto the spreadsheet, this creates a table
12. Select the RuleDef node
13. Drag and drop onto the spreadsheet, leaving at least one column space. This creates a second table.

There should now be two tables on the spreadsheet, one for RuleAssignment details, one for RuleDef details. These can be sorted / filtered independently and have a different number of rows. The main thing is that to have valid rules for OpenClinica, each RuleAssignment will need to refer to a RuleDef.

Writing New Rules

Enter rule details as rows in both tables as needed (see other articles in this WikiBook for tips on that). When ready to upload to OpenClinica, on the Developer tab in the XML group, click Export and choose a file name for the xml file. If the mapping was done correctly, the exported xml file will be in the correct format for upload.

If desired, Excel can validate the rule data against the xsd schema during the export step. On the Developer tab in the XML group, click Map Properties, and tick 'Validate data against schema for import and export' before exporting. The validation step will pick up structural issues like data type errors (boolean fields must be lower case 'true' or 'false', etc.) or missing required fields. The validation step won't otherwise check the content, for example the rule expression, rule target, etc. could be any string as far as the xsd is concerned (this is checked on upload to OpenClinica, however).

Editing Existing Rules

Existing rule xml files downloaded from OpenClinica can be imported to a spreadsheet set up as described above. On the Developer tab in the XML group, click Import, then select the rule xml file to work with. The rule details will populate the mapped tables.

If the imported rule file contains action types not mapped to the spreadsheet, the rule definition nodes will be displayed but rule action nodes for those unmapped types will not be displayed, because there are no columns created for them. However, these can be mapped as described above.