Formatting Data in Excel with Tags
Format an Excel report template with tags that map data sources and fields into the layout.
Use Microsoft Word or Excel to create a template layout for the report. For this one, you choose a spreadsheet. When thinking of the data your report requires, consider its source and the fields from it you need to incorporate. Assign each data source a short name.
For the Tenure Report you need data like:
-
Department info (short name
dep)- with fields:
dname,deptno,loc
- with fields:
-
Employee info ordered by tenure (short name
emp_t)- with fields:
ename,empno,tenure,job
- with fields:
-
Employee info ordered by name (short name
emp_n)- with fields
ename,hiredate,job
- with fields
In your document template, reference the short name of your data source using Document Generator tags {#shortname} and {/shortname} to surround {fieldname} tags that reference the data source field values by name.
As shown below, the Excel sheet formats the three fields of the Department data source using its short name and field names like this:
**{#dep}**Department **{dname}** (**{deptno}**) in **{loc}****{/dep}**In a similar way, under the Employees by Tenure heading, it references the fields of the Employee data source ordered by tenure using the following tags. A vertical bar below represents a cell boundary. Document Generator repeats the row of cells between {#emp_t} and {/emp_t} for each row in the data source. When data source row repetition produces new spreadsheet rows, it’s known as horizontal repetition of cells.
**{#emp_t}****{ename}**(**{empno}**) | **{tenure}** | **{job}{/emp_t}**Under the Employees by Name heading, it uses slightly different tags to repeat the Employees ordered by name vertically instead. It references the fields of the Employee data source ordered by name using the following tags. A horizontal bar represents a cell boundary. Document Generator repeats the column of cells between {:emp_n} and {/emp_n} for each row in the data source. When data source row repetition produces new spreadsheet columns, it’s known as vertical repetition of cells.
**{:emp_n}****{ename}**
-
**{hiredate}**
-
**{job}{/emp_n}**The figure shows the Woods Clinic Tenure Report in Excel, with a graphic featuring two seasoned physicians in the upper left corner, the report title to its right, and Document Generator tags and text in the cells below it to format department and employee information.
