Create an Excel Template

To use the Excel Merge Action, first create an Excel template to define what data is retrieved from CSM. Data can be retrieved from:

Good to know:

  • For best results, use a template from the Attachment Manager as a starting point (example: Incident Scorecard).
  • Templates must be saved with an .xls or .xlt extension.
  • Many functions can be defined for an Excel template. For several examples of useful functions, see Excel Merge Template Functions.

Creating an Excel Template with a Single Business Object

  1. Name the first tab of the template <#Config>. CSM uses this page to retrieve data from a Business Object, Business Object Relationships, and other settings to properly create the file.
    Note: All items must be in the AA column of the worksheet. Use the Excel Hide feature to hide columns A through Z.
  2. Define the Business Object, Relationships, and settings to use for retrieving data from CSM.
    1. In the AA column of the worksheet, specify BusinessObject on a row, and then provide the name of the Business Object (example: Incident) on the following row.
    2. Specify Relationships on another row. Leave the rows underneath Relationships blank for a single object template.
    3. Specify Settings on another row. If the One-Step Action is run against a group of records, specify RunForAll in the following row to have the file append all of the records together. If this keyword is not present, a file is generated for each record.
      Note: GetField functions do not work against a group of records. Consider using the lookup function instead (see Excel Merge Template Functions).
  3. Define the contents for the Excel file (on another tab/worksheet of the Excel template). This is done using functions that pull data from CSM.
    Note: Functions must be in the following format: <#FunctionName(parameters)>. When multiple parameters are passed to these functions, a semicolon is used to separate the values.

    1. Define which Fields to pull data from:
      • <#BusinessObject.FieldName>: Provide this function to look for the specified Field in the parent Business Object (example: Specify <#Incident.Status> to retrieve values from the Status field in Incident Business Objects).
      • <#GetField(BusinessObjectName;fieldname)>: Provide this function to look for a Field in a related Business Object (example: Type <#GetField(Customer;Full Name)> to retrieve a Customer's name from the Customer Business Object related to an Incident).
      • <#GetField(RelationshipName;fieldname)>: Provide this function to use the specified Relationship to find the related Business Object.
        Note: It is important to use the Relationship name so the correct Business Object is used, especially if more than one Relationship exists with the same related Business Object (example: CustomerHasContacts, CustomerHasPrimaryContact).
        Important: Do not use the GetField function if you run an Excel Merge One-Step Action against a group of records and also include data from a related Business Object (example: You want a list of Tasks and also want to include the parent Incident's Service category). See the section below to create an Excel template with multiple Business Objects.
    2. (Optional) Define which Tokens to pull data from. This is done using the GetCounter, GetExpression, and GetStoredValue functions, which have the following options:
      • Use the name of the Counter, Expression, or Stored Value:
        • <#GetCounter(name)>
        • <#GetExpression(name)>
        • <#GetStoredValue(name)>
      • Use the scope and then the name of the Counter, Expression, or Stored Value:
        • <#GetCounter(scope;name)>
        • <#GetExpression(scope;name)>
        • <#GetStoredValue(scope;name)>
  4. Create a named range. CSM uses the named range to structure the report.

    1. On the Contents tab, select the series of cells containing the functions. Do not select the column headers.
    2. Click Formulas>Define Name, or right-click>Define Name.
    3. Provide the name of the Business Object with two underscores on each side (example: _ _ BusinessObject _ _).
  5. Save the file in the Attachment Manager or on the computer.

Creating an Excel Template with Multiple Business Objects

  1. Complete the steps to create an Excel template with a single Business Object.
  2. Define additional Business Objects and Relationships to use for retrieving data from CSM.
    1. In the AA column of the <#Config> worksheet, underneath BusinessObject provide the name of each additional Business Object to be used.
    2. Use the rows underneath Relationships to provide the Relationships between the Business Objects.
      Note: To include multiple Relationships, provide each one in a separate, consecutive row. Leave the rows underneath Relationships blank to not include any data from related Business Objects.
  3. On the Contents tab, define additional functions to pull Field data from CSM.
    Note: The placement of functions will determine the structure of the report. (example: defining the Field <#Incident.IncidentID> in cell A1 and <#Task.TaskID> in cell B2 will generate a report with Incident ID values in column A and associated task ID values in subsequent rows in column B).
    Important: Do not define Fields for multiple Business Objects on the same row. Each row should handle Fields for a single Business Object.
  4. Re-configure the existing named range to accommodate multiple Business Objects, and create additional named ranges.
    1. Expand the named range for the parent Business Object to include all cells containing Fields.
      1. Click Formulas>Name Manager.
      2. Select the existing named range (example: __Incident__) and click Edit.
      3. Select and delete the text in Refers To.
      4. Select all cells containing functions. The named range will automatically populate with the new selection.
      5. Click OK.
    2. Create a named range for the child Business Object that includes all cells containing child object Fields.

    The following figure shows an example:

    When the Excel Merge One-Step Action retrieves the data from the specified Fields, the parent object data is listed first, and the child object data is listed underneath (example: Incident data followed by data from related Tasks).

  5. Save the file in the Attachment Manager or on the computer.
© Copyright 2018 Cherwell Software, LLC. All rights reserved.