Create an Excel Template
To use the Excel Merge Action, you first need to create an Excel template to define what data is retrieved from CSM. Data can be retrieved from:
To create an Excel template:
- 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.
- Define the Business Object, Relationships, and settings to use for retrieving data from CSM.
- In the AA column of the worksheet, specify BusinessObject on a row, and then provide the name of the Business Object (ex: Incident) on the following row.
- Specify Relationships on another row. If related Business Objects will be used in the template, provide the Relationship names on the following rows (ex: Incident Links Configuration Items).
Note: If you want to include multiple Relationships, provide each one in a separate, consecutive row. If you do not want to include any data from related Business Objects, then you can leave the rows underneath Relationships blank.
- Specify Settings on another row. If the One-Step 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 will be generated for each record.
- 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 in the following format: <#FunctionName(parameters)>. When multiple parameters are passed to these functions, a semicolon is used to separate the values.
- Define which Fields to pull data from:
- <#BusinessObject.FieldName)>: Provide this function to look for the specified Field in the parent Business Object (ex: 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 (ex: 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 (ex: CustomerHasContacts, CustomerHasPrimaryContact).
Important: Do not use the GetField function if you run an Excel Merge One-Step against a group of records and also include data from a related Business Object (ex: You want a list of Tasks and also want to include the parent Incident's Service category). Use one of the following options.
- (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:
- Use the scope and then the name of the Counter, Expression, or Stored Value:
- (Optional) Create named ranges. This is done to create a list using the related Business Objects (ex: List of Configuration Items).
- In a row, define a function in each cell to retrieve data from Business Object Fields (ex: <#RelatedBusOb.FieldName>).
- Select the series of cells containing the functions to be used in the range.
- Click Formulas>Define Name, or right-click>Define Name.
- Provide the name of the related Business Object with two underscores on each side (ex: _ _ RelatedBusinessObject _ _).
- Save the file in the Attachment Manager, or on your computer.