Create Excel Report actions
Create Excel Report actions write the contents of one or more Table datasets or parameters to a Microsoft Excel worksheet. The worksheet is created from a template that may be uploaded by selecting the Data > Report Templates > Excel Templates folder in the LiveCompare hierarchy.
When a Create Excel Report action is run, a new Excel report is generated based upon the template worksheet, and stored in a folder underneath the LiveCompare user data directory. If the Create Excel Report action’s Fixed parameter is set to false, its reports are stored in the following time-stamped directory:
<LiveCompare user data dir>\public\Reports\<Workspace Name>\<Report Name>_YYYYMMDD_HHMMSS
If the Create Excel Report action’s Fixed parameter is set to true, its reports are stored in the following directory, which doesn't include a time stamp:
<LiveCompare user data dir>\public\Reports\<Workspace Name>\<Report Name>
In the Configuration - Reports screen, the OfficeMaxTableRows setting determines the maximum number of rows that may be written to an Excel worksheet. The default value is 500,000, however this may be changed by a user with Administrator permissions. Note that you are able to force LiveCompare to generate Office documents that won't open without repair if you set OfficeMaxTableRows to a very large value.
Create Excel Report actions may be used in conjunction with the Create Object Differences Report action when creating object reports. The Hyperlinks and Hyperlinks to Source Map properties may be used to match the NAME column in an Excel Report against the NAME column in the Create Object Differences Report action’s Result dataset. This converts the Excel report’s NAME column into a hyperlink to the associated Object Differences report.
| Action property | Description |
|---|---|
| Fixed |
A Boolean parameter that indicates whether the Create Excel Report action will generate fixed location reports. If this parameter is set to true, the action stores its report in the <Report Name> directory; this directory will be removed and then recreated each time the action is run. If the Fixed parameter is set to false, a new report will be generated in its own time-stamped directory each time the action is run. Each fixed location report in a workspace should normally have a different value for its Report Name parameter. This will ensure that each workflow’s Report URL dataset points to the expected report. |
| Hyperlinks |
An optional Table parameter or dataset that includes a set of objects and their associated hyperlink URLs. The first column in the table should store an object type, the second column should store an object name, and the third column should store a URL. This property may be set to the Result dataset returned by the Create Object Differences Report action. In this case, an Excel report for the Object Differences report’s source objects would include hyperlinks to the appropriate Tier 2 reports. |
| Hyperlinks to Source Map | A Table parameter or dataset that specifies which columns in a Source table should be used to identify a hyperlink. |
| Publish |
A Boolean parameter or dataset that specifies whether the report will be published in the Dashboard screen. If you don’t set this property, the action uses false the default value, which means that the report won't be published. This parameter is used in the context of running or scheduling a workflow in the LiveCompare studio. It is not used in the context of running an app. If a workflow (or any of its sub workflows) includes more than one reporting action whose Publish parameter is set to true, only the results of the last reporting action to run will be published in the Dashboard screen. If a workflow generates several reports, the Publish parameter should be set to true only for the report that provides the workflow’s final result. |
| Report Name | A String parameter that stores the name used for the generated report. If the Fixed parameter is set to false, the generated report name includes a timestamp indicating when the report was created. The generated Excel spreadsheet is named <Report Name>.xlsx, excluding any characters that are not permitted in for use in filenames. |
| Report URL | A String dataset that is set to contain the URL of the generated Excel report. |
| Source to Sheet Map | A Pair List parameter that maps the Source1 or Source2 tables to worksheets in the template spreadsheet. The first item in each pair list should be set to either Source1 or Source2. The second item in each pair should contain the name of an existing worksheet. If a worksheet used in the Source to Sheet Map has already been populated with table data, the data in the worksheet will be overwritten. |
| Source1 | A Table parameter or dataset containing data to be written to the Excel spreadsheet. |
| Source2 | A second Table parameter or dataset containing data to be written to the Excel spreadsheet. |
| Table Set | A Table Set dataset that contains the tables to be included in the report. If this parameter is specified, each table in the table set is written to a worksheet in the Excel spreadsheet. The worksheet has the same name as the table being reported upon. |
| Template Workbook | A String parameter that contains the name of a template Excel spreadsheet stored in the ‘ExcelReports’ directory on the LiveCompare server. The spreadsheets stored in this directory may be selected from a dropdown list. |
The Hyperlinks to Source Map table should have the following columns:
| Column | Description |
|---|---|
| SOURCE | The name of a Source table property defined for the Create Excel Report action, for example Source1. |
| TYPE_NAME | The name of a column in the Source table that will be used to identify object types. |
| NAME_NAME | The name of a column in the Source table that will be used to identify object names. In the Excel report, this column will contain hyperlinks for matches that are found in the Hyperlinks table. |
Generated Excel reports may be opened by in your browser by double-clicking the Report URL dataset, or choosing View Details from the dataset’s context menu. They may also be accessed from a workspace’s Reports folder in the LiveCompare hierarchy. Choose Reload Children from the folder’s context menu to list any newly generated reports.
Example
To examine the details for a Create Excel Report action, double-click the action node, or select View Details from the node’s context menu. The Details screen displays the time taken for the action to complete.
Create Excel Report actions support the use of the Define Action Parameters Wizard which allows new Source input parameters to be created with the type Table. This allows Excel reports to be created from more than two Table parameters or datasets.