Overview
Design an Excel Template and associate different parts of the template to the tabbed data, column header, and row header panes of the Crosstab design created in the steps above. Additionally, setup headers and footers for the report to draw information from the DT_FACILITY table in EQuIS.
|
Set up Excel templates to facilitate data reporting. |
|
Exercise Objectives |
•Setup an Excel Template
•Customize Headers and Footers
•Format Table Design and Cell Styles
•Review Excel Template Guidelines for Header and Footer Syntax
Skills, Software and Permissions Required
•EQuIS Professional 7 must be installed on the workstation with the EQuIS Library feature turned on
•.NET Framework 4.7.2 or higher must be installed on the workstation
•Microsoft Excel 2003 or higher must be installed on the workstation
|
Objective: Setup an Excel Template |
1.Open a new workbook in Microsoft Excel, and save it as an Excel Template (.xltx).
2.For Microsoft Excel 2010 and earlier versions, right-click on Sheet2 and Sheet3 from the workbook and select Delete. These sheets will not be present in Microsoft Excel 2013 or later versions.
3.Place the cursor in and select cell A1, right click and select Define Name. This is the location in the worksheet where the top left cell of the exported table will appear.
4.Name the range cell A1 as Data, followed immediately by a hard return on the keyboard.
|
Objective: Customize Headers and Footers |
Customize headers and footers to include additional information using field name codes. The field names that are available for use are:
•Any field included in the crosstab design’s Row Headers.
•Any field from the DT_FACILITY table.
1.Select the View ribbon.
2.Select Page Layout from the Workbook View group.
3.In the center Header section, enter the desired header information, such as:
TABLE 4
Project: {dt_facility.facility_name}
Groundwater Analytical Results
|
For additional header and footer syntax, see the section below on Guidelines for Header and Footer Syntax. |
4.Select the Page Layout tab and select Print Titles.
5.On the Header/Footer tab select Custom Footer.
6.In the Left Section, enter the desired text for the footer (such as the table below) and set the Font to Times New Roman and the Font Size to 8.
Qualifier |
Definition |
|---|---|
J |
The concentration is an approximate value. |
U |
The compound was not detected. |
NA |
Not analyzed. |
Bold |
Concentration exceeds CWI. |
Bold and Shaded |
Concentration exceeds CWI and CWR. |
7.In the Center Section, select to Add a Page Number that will dynamically update.
8.In the Right Section, select to Add an Image (a logo, for example) to the footer.
9.Select the OK button in the Footer window and then again in the Page Setup window after previewing the custom footer.
|
Objective: Format Table Design and Cell Styles |
1.Select the top-left corner of the Excel worksheet to highlight the entire worksheet.
2.Set the Font to Times New Roman and the Font Size to 11 on the Home ribbon.
3.Select cells A1 to C4 and change the background fill color to Silver.
4.Select “Cell Styles,” select one of the existing styles, right-click and select Duplicate.
5.Populate the Style name with ColumnHeader.
6.Select Format and then set the desired format for the cell style.
7.Select the OK button to view all of the settings in the Style window.
8.Select the OK button to exit out of the Style window.
9.From the Style toolbar, select the now-existing ColumnHeader style, right click and select Duplicate.
10.Populate the Style name with RowHeader.
11.Select Format and then set the desired format for the cell style.
12.Select the OK button to view all of the settings in the Style window.
13.Select the OK button to exit out of the Style window.
14.From the Style toolbar, select the now-existing RowHeader style, right click and select Duplicate.
15.Populate the Style name with GroupColumn0.
16.Repeat steps 11-13 as desired.
17.Save the template again (be sure to save it as an .xltx).
18.Close Microsoft Excel.
|
Objective: Review Excel Template Guidelines for Header/Footer Syntax |
Review the guidelines in the tables provided below to utilize the syntax unique to EQuIS Crosstabs in Microsoft Excel headers and footers.
Guideline |
Syntax |
|---|---|
General |
|
Static Text Syntax Text that will not change depending upon the content of the report is called “static text,” such as the name of a header.
Examples are: “Project:,” “Date Range,” and “Location.” Static text can be entered in the designed font and will not change when the Excel template is used. |
Project Name: Date Range: Location: |
Dynamic Text Syntax Text that will change depending upon the content of the report is called “dynamic text” and can be populated by values in the Row Headers of the Crosstab Design or from DT_FACILITY.
All dynamic text field names must be enclosed in curly brackets {}. Note that dynamic text should include the desired font in the template. |
Correct {sys_loc_code} {dt_facility.facility_name}
Incorrect sys_loc_code dt_facility.facility_name |
Including Fields from DT_FACILITY To include fields from DT_FACILITY, the field names must be preceded by “DT_FACILITY.”
*The exception to this rule is when the field from DT_FACILITY is included in the Crosstab design’s Row Headers |
Correct {dt_facility.facility_name} {dt_facility.facility_address_1} Incorrect (Unless the field is included as a Row Header): {facility_name} {facility_address_1} |
Including Fields from Crosstab’s Row Headers To include fields from the Crosstab designs’ Row Headers merely enter the field’s name Row Header’s “column” name. |
Correct sys_loc_code Incorrect dt_location.sys_loc_code location |
Options for Displaying Row Header Values in Headers or Footers |
|
Display All Values To show all unique values included in a row of the Crosstab design, use the code “:all.” This may occur if “Separate by each unique” is not selected in the Crosstab design for SYS_LOC_CODE. By entering “:all” into the field’s dynamic name, the export from Crosstab into Excel will list all of the locations in the worksheet. |
Correct {sys_loc_code:all}
Incorrect {sys_loc_code} all |
Display Minimum to Maximum Ranges To show a range of values within a Row Header field, such as a date range, use the codes “:min” and “:max” within the curly brackets {}. |
Correct {sample_date: min}- {sample_date: max} Incorrect (sample_date} – {sample_date} |
Display Values Not Displayed in Body of Report It is possible to include information not displayed in the body of the report, such as the sample types included in the report. This may be done by including the value in the Row Headers of the Crosstab Design and selecting a display type of “None” in the design. |
Correct {sys_sample_type} OR {sys_sample_type:all} |
SQL Users When using an SQL Server for the EQuIS database, either capital or lower case letters may be used when designing an Excel Template. |
Correct dt_facility.facility_name sys_loc_code |