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:
| Formatvorlage | Section | Usage |
| applicationname | Title page | Term „DeltaMaster“ |
| reporttype | Title page | Term „Report“ |
| reportname | Title page | Name of the database |
| Standard-Right | Title page | Date and time of report creation |
| Verzeichnis-Titel | Table of contents | Header „Table of Contents“ (style name in German) |
| TOC[x] | Table of contents | Table of contents entry on level x |
| Book[x] | Report | Name of report folder on level x |
| ReportTitle[x] | Report | Name of report on level x |
| focus | Report | Report view, dimension, value, comment, … |
| head-part | Report | Header „Table“, „Chart“, „Annotation“ |
| table-head | Report | Word table: header |
| Table-Standard | Report | Word table: text |
| Chart centered | Report | Charts; tables exported as chart |
| Description | Report | Report 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.
| Placeholder | Meaning |
| @Dxx | Update dimension |
| @Fxx | Name of the report folder |
| @IMN | Name of the current generator element |
| @IDA | Adress of the current generator element |
| @Pxxyy | Element 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:
| Name | Meaning |
| _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.
| Name | Meaning |
| _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.
| Name | Meaning |
| _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.
