Suchen...
Generic filters
Exact matches only
Search in title
Search in excerpt
Search in content

Editing Templates for Word and Excel Exports

PDF Download

Dear data analysts,

in Germany’s premier soccer league, Gerd Müller is still by far the most successful scorer ever. In 14 years with his team FC Bayern Munich, he scored 365 goals, and he was crowned top scorer in seven seasons. The word graphic for the seasons from 1965/66 until 1978/79 9 [9|40] shows his outstanding performance – which Müller certainly owes at least partly to partners like Franz Beckenbauer, who kept providing him with brilliant through-balls. By the way: Beckenbauer, the sweeper, never was the league’s top scorer but became famous nonetheless. In sports, good spadeworkobviously pays off. And in the field of business? Well, certainly just as much!

In November 2005, we described in the DeltaMaster clicks! how to export reports to the Microsoft applications Word and Excel from DeltaMaster or with ReportServer. At that time, we were concentrating mainly on the various “switches” DeltaMaster offers for that purpose. In the following, numerous users asked how the layout of the template documents that are used during this process may be edited and modified. After all, good templates do not lead only to nicer documents but also spare individual manualpostprocessing.

You’ll find the details about how to design office templates according to your taste and corporate requirements in the current clicks! issue.

Kind regards,
Your Bissantz & Company team

When you export reports, cockpits or analysis results to the applications of the Microsoft Office suite, DeltaMaster uses the template documents DeltaMaster.dot for Word documents and DeltaMaster.xlt for Excel workbooks. These files are expected in the folder specified in the Extras/Options/Folders menu. Using ReportServer, you may define the template for each individual job and thus deliver bespoke designs to different target groups and recipients.

Word Document Templates

The Word documents which DeltaMaster creates for you consist of three parts: a cover sheet, the table of contents and the exported reports. The look&feel of your reports depends on the Office templates that are used in the various sections. For instance, to define character and paragraph formats, to set tabstops, to modify frames, background colours etc., you use the Stylesdialog in Word’s Format menu.

DeltaMaster uses the following styles:

FormatvorlageSectionUsage
applicationnameTitle pageTerm „DeltaMaster“
reporttypeTitle pageTerm „Report“
reportnameTitle pageName of the database
Standard-RightTitle pageDate and time of report creation
Verzeichnis-TitelTable of contentsHeader „Table of Contents“ (style name in German)
TOC[x]Table of contentsTable of contents entry on level x
Book[x]ReportName of report folder on level x
ReportTitle[x]ReportName of report on level x
focusReportReport view, dimension, value, comment, …
head-partReportHeader „Table“, „Chart“, „Annotation“
table-headReportWord table: header
Table-StandardReportWord table: text
Chart centeredReportCharts; tables exported as chart
DescriptionReportReport annotation

Ready, steady, go!

On the title page, DeltaMaster follows not only the styles, but also your individual markings, provided you have entered them:

  • If DeltaMaster finds an empty title page in the document template, it automatically inserts the captions “DeltaMaster“, “Report“, the database name, and the date the report was created.
  • However, you can provide your own contents in the title page of the document template.
    In this case, DeltaMaster does not add entries on its own. In order to tag the title page dynamically with elements from the current report, the variables known from ReportServer are at your disposal. These variables will only be resolved when the export is started from ReportServer.
PlaceholderMeaning
@DxxUpdate dimension
@FxxName of the report folder
@IMNName of the current generator element
@IDAAdress of the current generator element
@PxxyyElement­ property

Excel Templates

Adapting templates in Excel follows a different concept than in Word: While in Word you choose the style settings in a specific dialog, in Excel you format certain “sample cells” in special worksheets in a way they are supposed to look. During the export, DeltaMaster takes over these cell formats and applies them to the corresponding cells of your report.

The cells that DeltaMaster uses are distributed among three sheets in the workbook: General, Table, and PivotTable. The settings and formats on the General sheet apply to all reports. Table defines the look and feel of list-oriented reports, for instance from the modules Ranking, PowerSearch, ABC Analysis, or Time Series. The third sheet, PivotTable, affects Pivot Cockpits and the Cross Table Analysis.

In order to make DeltaMaster recognize from which cells or ranges the format is to be taken, these are marked with a name. Excel usually shows the names in the field left from the formula bar. Additionally, to facilitate finding your way quickly, we have inserted the names in the cells of the .xlt file that is shipped with DeltaMaster. But remember that only the assigned name is operative, not the cell contents.

In particular, the following styles (named ranges) are defined on the General sheet:

NameMeaning
_Title_Report title
_LabelView_Header „View:“
_ValueView_Report view description
_Label[x]_Header „Dimension:“, „Measure:“, „Annotation:“ etc.
_Value[x]_Details for the report definition: dimension, measure, annotation etc.
_TableStart_Starting position of the data table

The cell called _TableStart_ has a particular meaning: It marks the top left corner of data tables. Its format is irrelevant, but its position in the sheet is crucial. If you move this cell, the exported data will appear at a different position in the sheet.

The printing settings of your entire exported Excel reports are dependent on the settings you have made with the Page setup command in the File menu for this sheet of the template.

On the Table sheet, the formats for list-oriented reports are defined, for instance rankings, ABC analyses, or time series. Here, only the formatting is relevant: the position of the created table, the headers, the print layout and similar settings are determined by _TableStart_ on the General sheet.

NameMeaning
_FixedCols_First table column; the grey column headers in the default template
_FixedRows_First table row; the grey row headers in the default template
_DataArea_Table data area; the white body area in the default template

On the PivotTable sheet, finally, you specify the look of pivot tables and cross table analyses. Adjacent columns or rows that are formatted differently result in a repetitive pattern DeltaMaster applies over and over again until the actual table width and height is reached. With this technique, it is possible to generate table lines or columns with an alternating white and grey background for instance. Analogous to the Table sheet, only the formats are relevant, but not the position.

NameMeaning
_TopLeftArea_Top left corner of the cross table, e.g. for measure name
_HierRowHeader_Name of the row dimension
_HierColHeader_Name of the column dimension
_TopRightArea_Top right area of the cross table (empty)
_ColAggs_Column aggregations; the yellow cells at the bottom of the columns in the default template
_RowAggs_Row aggregations; the yellow cells at the right end of the rows in the default template
_TableAggs_Table aggregations
_FixedCols_First table column; the grey column headers in the default template
_FixedRows_First table row; the grey row headers in the default template
_DataArea_Table body; the white area in the center in the default template

After the Export is before the Export

Subsequent to the actual export, DeltaMaster checks whether there is a VBA module prodecure (macro) called “AfterExport()” in the template, and calls this macro if it exists. Thus you have the opportunity to trigger further actions programmatically. For example, you might execute specific editing tasks, print the created reports automatically (like in the illustration below), or insert time and date etc. – and all this certainly also in combination with ReportServer.

Avoid the Offside Trap

Once exported, the way to deeper analyses is blocked. The numerous possibilities you find in DeltaMaster in order to get to the bottom of things do not exist in the Office documents any more – your reports are like frozen. To avoid getting caught in the analytical offside trap, we recommend to distribute your reports as .das files and allow your end users to contemplate them with the very convenient and cost-effective (Offline) Reader for instance. So you have an easy-to-use tool at hand, but at the same time you remain in your environment of choice and effortlessly shift into a more powerful mode whenever necessary.