Create the Excel template

You can create the Excel template with the embedded XML model by using the GET action of the Reports API.

Tip: Before you start, make sure the OData queries are valid and the OData URI is accessible.

Generate Excel template

  1. Open the WorkZone Reports API in a 3rd party Web debugger tool, for example, Telerik Fiddler.

  2. Use the GET action of the Reports API and specify the URI of the Report JSON file to be used for the custom report as a parameter to the GET action. Specify the template's language in the Accept-Language header.

  3. The OData queries are used to build XML model document where corresponding OData entities and their relations are described without actual values. The XML model is used to automatically generate a corresponding XML schema, which formally describes validation rules for the elements in the XML model document.

  4. The Excel report template is created. It has the name specified in the localized FileName parameter of the Report JSON. Save the Excel template to the same folder as the source Report JSON file.

    Note: If the Excel report template specified in the Report JSON already exists in the same location as the Report JSON file, the XML Schema of the Excel template will be overwritten with the new XML Schema created by the Report JSON.

  5. Adjust the generated XML schema to your actual business model and OData entities:

    • Use the POST action of the Reports API and specify the URI of the Report JSON file as a parameter to the POST action. Specify the template's language in the Accept-Language header and define some other parameters as test data.

    • If you have received the 200 Success response – your XML schema is valid, please skip steps below.

    • If you have received the following error – 400 [XmlException] XML is not valid according to specified XML Schemas – then read the issue's description in Message:

    • Change the Excel template extension to.zip and open it. Move xmlMaps.xml from the xl folder outside the.zip package. The xmlMaps.xml file contains generated XML schema.

    • Fix found and potentially dangerous issues in xmlMaps.xml by aligning it to the WorkZone Content Server data model.

    • Overwrite the xl\xmlMaps.xml file with the updated one. Change the file extension back to.xlsx.

    • Repeat first step again to verify XML schema.

Customize Excel template

Now you have a report template with the embedded XML model. It is currently empty and not yet adjusted to your company's needs.

To create a ready-to-use template, proceed with the following steps:

  1. Open the generated template.

  2. Ensure that you have the Developer tab added to the main ribbon. Click the Source button to open the XML Source pane.

    1. On the File tab, go to Options > Customize Ribbon.
    2. Select Main Tabs under Customize the Ribbon.
    3. Select the Developer check box and click OK.

  3. Insert a Table object. Number of columns must reflect requirements to the report.

  4. Bind table cells and XML elements manually or by using formulas.

    Manually

    • In the Excel template, select the cell in which you want the XML element to be displayed.
    • In the XML Source pane, right-click the needed data element and select Map Element.
      You can also drag the XML element from the XML Source pane into the desired cell.

    XML elements in Excel are repeating by default. This means that, unlike the content controls in the Word template, you do not have to define special repeating XML elements to manage the display of multiple data records.

    By using formulas

    Use custom formulas to concatenate, format, or group values of XML elements.

    Specify a custom formula in the needed cell in the following format: Path -> Format, where

    • Path is a path to the needed element. Use character '/' as delimiter.
    • Format is a string of properties separated by the '&' character. Optionally, you can define additional parameters by using the following delimiters:
      • : – define a type
      • = – define a style
      • "" or '' – define constants.

    • Files -> VFacetings/Path

    • Files -> Parties/CustomLabel_Value & " - " & Parties/NameType_Value & " " & Parties/NameCode_Value & ": " & Parties/Name/Name1 & " " & Parties/Name/Name2

    • Files -> Infoes/CustomLabel_Value & ":" & Infoes/Info_Value

    • Files -> Dates/CustomLabel_Value & ":" & Dates/DateStamp:DateTime=dd-MM-yyyy

  5. Click Save.

  6. Test the customized report template. To do this, use the POST action of the Reports API.

View OData query and XML model

  • You can view the OData query used to generate the XML model in the Report JSON by using the Text/Plain parameter to the ACCEPT request to the Reports API.
  • You can view the XML model which is generated by the Report JSON by setting the Accept header to Application/XML value when you make requests to the Report API.