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 does not include a time stamp:

<LiveCompare user data dir>\public\Reports\<Workspace Name>\<Report Name>

Create Excel Report actions may be used in conjunction with the Create Object Differences Report action when creating object reports. The Hyperlink 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 which 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 would normally 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.
Report Name	A String parameter which 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 filenames.
Report URL	A String dataset which is set to contain the URL of the generated Excel report.
Source to Sheet Map	A Pair List parameter which 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 which 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 drop-down 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 double-clicking the Report URL dataset, or choosing ‘View Details’ from the dataset’s context menu. In order to view each report, your client web browser must have the following security settings specified for the ‘Internet’ and ‘Local Intranet’ zones. These settings can be found in the ‘Downloads’ section of Internet Explorer’s ‘Security Settings’ dialog:

Setting	Value
Automatic prompting for file downloads	Enable
File download	Enable

Generated Excel reports may be also 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.