Report Templates

UniPlot can be used to create data reports. To use this function, MS-Excel 97, MS-Excel 2000 or higher must be installed on your computer. UniPlot uses the Active-X interface to insert data into an Excel template.

The template can consist of multiple sheets. Data from multiple files can be inserted into the template and be used to compare header data from different files.

The Excel template contains placeholders for header data and channels as well as the complete protocol format. A placeholder consists of a channel name or attribute name enclosed in the control characters * or $.

Small sheets can be embedded into a UniPlot page. See Embedding an Excel Template into a UniPlot Document Page.

A template can be created manually or by using the template creator. See Creating a Report Template.

Example of an embeddded Excel report:

../../_images/XLSReportExample-Embed.png

Creating a Report Template

To create a new report template:

../../_images/ReportTemplateCreator-en.png

Specify the name for the new template. The name will be displayed in the Report dialog when you fill the template with data. Therefore the name should describe the template well. The name should fit into the text field.

A template can contain fixed channels as well as placeholders which can be selected after data has been chosen. The number of these channels is set in the field User Channels.

To choose the header data (attributes) and channel names, specify a typical data file name. To choose a data file click, the Browse button.

When you are done with all input, click the OK button.

  • In the dialog box that appears, choose all the header data. To do so, select the items and the left list and click the => button.
../../_images/ReportTemplateCreator-SelectHeaderData-en.png
  • In the next dialog box that appears choose all the channel names. To do so select the items and the left list and click the => button.

    ../../_images/ReportTemplateCreator-SelectChannelData-en.png
  • After the header data and channel names have been specified, the template will be created.

    ../../_images/ReportTemplateCreator-ExcelSheet-en.png
  • The template will be displayed in Excel. You can now adjust the layout and then save the changes.

Using a template and selecting data is described in the chapter, Example for a Protocol Creation.

Placeholders

The Excel template can contain multiple sheets. Each sheet can contain placeholders. Placeholders can be specified to display the data of channel attributes or global attributes. Data from different data files can be read into the template.

Attribute Placeholder

A placeholder is text that begins with a dollar sign, followed by the text, followed by another dollar sign, e.g. $Operator$ is a placeholder for a global attribute. For a channel attribute the channel name followed by a period (.) is placed before the attribute name, e.g. $EngSpd.units$.

To access an attribute from a specific file, add a file alias to the placeholder, e.g. $File1:Operator$. The placeholder $File2:Operator$ would be replaced by the value of the attribute Operator of the second file. Numbering of files aliases starts with 1. The cell can contain more than one placeholder.

UniPlot will search for the placeholder and replace the text with data from the specified data file.

The text must be identical to the attribute name in the NC file.

Note

UniPlot converts all data files into the netCDF format with the file extension .nc. Because the attribute and channel names in the NC file cannot contain any special characters, these characters will be replaced by an underscore. To view names, open a data file, see File=>Import Data.

Channel Placeholder

The channel name is enclosed in asterisk signs, e.g.: EngSpd. UniPlot will insert the data of the specified channel as a column into the Excel template. The first value will replace the placeholder. The number of decimal places, font, font size, etc. can be specified in the Excel template.

If the NC file contains the channel attribute C_format, the data format (decimal places) will be overwritten.

The following figure is an Excel template. Header data (global attributes) is located in the upper part. Data channels are placed below the header data.

../../_images/XLSReportExample.png

User Channel Placeholders

User channel placeholders are replaced by a channel name during the execution of a template. A dialog box is displayed with channel names which are not already in use in the template. User Placeholders have the following setup: ***UCHxx***, where xx is a number starting with 1.

Example: ***UCH1***.

User Placeholders which are not used are removed from the report.

Other Control Elements

To display record numbering, use the placeholder **ROWCOUNTER** as a channel name

Types of Excel Templates

UniPlot distinguishes between three different types of templates:

Single Line Templates

The following figure displays an Excel template and the result. The header is displayed in the upper part and the channel data is located further down.

../../_images/Protocol-SingleLineTemplate.png ../../_images/Protocol-SingleLineFilled.png

For printing purposes channel title and unit blocks can be repeated at the top of each page. This can be set in the Excel Page Setup dialog box.

Multi Line Template

If the number of channels doesn’t fit into one line on the print out, it can be broken into several lines. Between each block a empty line can be inserted by adding a placeholder **EMPTYROW** into one of the cells in the row following the channel names.

../../_images/Protocol-MultiLineTemplate.png ../../_images/Protocol-MultiLineFilled.png

The cells with a channel placeholder contain the complete cell format, e.g. fill color, font, frame, alignment, decimal format, etc. UniPlot will copy this format into every cell that is filled with data of the channel.

Every channel can occur several times in the template. If a record numbering is necessary, the predefined placeholder, **ROWCOUNTER**, can be entered.

For printing purposes channel title and unit blocks can be repeated at the top of each page. This can be set in the Excel Page Setup dialog box.

Single Line Space-Saving Template

A record of channels is split into multiple blocks. One block consists of a header and the channel placeholder. The first row of the header is marked with the keyword **CHANNELNAMES** in column A. The line with the channel name placeholders, which must be the last of a block, is marked with the key word **CHANNELS** in column A. The number of blocks is not limited. UniPlot will insert an emptry row between the blocks. The channel name header can contain attribute placeholders.

If the channel’s data does not fit on one page, the channel header is repeated at the beginning of the next page.

To prevent the header from moving to a different position, the page format, the cell height and other page setup properties should not be altered after the report is created. All these properties should be set in the template.

When a report is created by UniPlot, the keywords **CHANNELNAMES** and **CHANNELS** are removed. The width of column A can be set down to zero.

The key word **CHANNELNAMES** can be extended with the two following key words:

STARTNEWPAGE Always start block on new page.
NEWPAGE If a block fits completely on the following page, the block is moved to the next page.
EMPTYREMOVE If a block does not contain any valid channel names, it will be removed.

The key words are separated by a colon.

Example: **CHANNELNAMES:NEWPAGE:EMPTYREMOVE**

../../_images/Protocol-BlockTemplate.png

In the figure, column A is set wide enough for key words to be read.

../../_images/Protocol-BlockFilled.png

Embedding an Excel Template into a UniPlot Document Page

An embedded Excel Template can be used as a page header.

To insert a sheet into a UniPlot page:

  • Choose the command Edit=>Insert New Object.

  • Select the command Microsoft Excel Worksheet and click OK. The Excel worksheet will be inserted in the upper left corner of the page.

    ../../_images/Protocol-ActivtedEmbeddedTable.png

    To deactivate the OLE object, click outside the object or press the ESC key

  • Right click in the worksheet to open the shortcut menu. Choose Rename and enter Excelsheet into the text control. This tells UniPlot that the sheet contains place holders.

    ../../_images/Protocol-EmbeddedTableRename-en.png
  • Double-click the sheet to activate it.

  • Enter the following placeholders into the worksheet:

    $Origin$    
    rpm SAETrq be
    [$EngSpd.units$] [$SAETrq.units$] [$Fuel.units$]
    *EngSpd* *SAETrq* *Fuel*

    Your sheet should look like this figure:

    ../../_images/Protocol-EmbeddedTableWithPlaceholder.png

    You can open the data browser to see attribute and channel names.

  • When you are done, click outside the worksheet to deactivate it.

  • To load data into the sheet, right click on the object to open the shortcut menu. Click on the Load Data item. This item is only displayed if the object name is Excelsheet. Select the data file c:\program files\uniplot\samples\test1.xls. The data should be inserted into the template.

    ../../_images/Protocol-EmbeddedTableInPage.png

Note

If you see display errors, move the sheet object a bit. Excel will recalculate the object and fix this problem.In embedded Excel-Templates, only the first sheet of the workbook is filled. Before UniPlot inserts data into the sheet, it will create a copy of the sheet and add it to the workbook. The name of the copy is the name of the first sheet followed by the text -UPT. The copy contains placeholders and cell format. When the command Load data or Update Sheet is called, the sheet contents are deleted, the placeholders and cell format are copied from the saved sheet and the data is inserted into the sheet.

The following figure is an example from the document UniPlot\samples\sample.ipw:

../../_images/Protocol-sampleEmbeddedTable.png

Setting up a Report

Save templates in the UniPlot\template\report\ directory. The path can be specified in the Tools=>More Options dialog box. Excel file names without paths or extensions are displayed in the report dialog box.

../../_images/Protocol-SelectDlg-en.png

Record filters specified in the data browser are applied to select the data. Example: You can specify a filter so that only records in which the channel speed is in the range 990 to 1010 rpm is inserted into a report.

Missing Values are displayed in the Excel sheet as empty cells.

All placeholders which could not be filled are replaced by empty cells.

Example for a Protocol Creation

  • Choose File=>More File Functions

  • Select Excel-Report: Create Report and click OK to open the following dialog box:

    ../../_images/Protocol-SelectDlg-en.png

    Select the report Example 1 and click OK.

  • Click on the Browse button in the dialog box that appears to specify the directory and file extension of the files you would like to insert into the template. For this example choose the c:/program files/uniplot/samples/automate/ directory and click OK. Then choose the file wot_xls.nc and click OK.

The data will be loaded, UniPlot will start Excel and the data will be inserted into the template. When UniPlot is finished it will put Excel on the screen.

Creating a Protocol within an Automation

Excel templates can be used in an UniScript automation.

Simple Example:

auto_AddToUI("Example Excel-Report", "Report1", "RSXLS_Template1");
def RSXLS_Template1()
{
    ssXLSTemplate = "UP_Temp.xls"
    svFile = auto_GetFileNameDialog(1, "Excel-Report");
    XLSREPORT_CreateFromTemplate(ssXLSTemplate, svFile);
}

To execute the example choose, Report=>Excel-Report.

The following figure shows the result:

../../_images/XLSReportExample-Erg.png

To find out more about automation with UniPlot, go to Overview Automation.

id-319975