Exporting Data to Microsoft Word and Excel

Dear data analysts,

according to estimates, more than 400 million people worldwide are working with the Microsoft Office suite. Obviously, there’s no way around t hese products – and this is why DeltaMaster takes the beeline once again here: you just hand cockpits, reports and analysis results over to Office at any time and at the touch of a button.

DeltaMaster takes care of an appealing formatting. As in many other places, you may change the defaults and create your own report layout. An important basis for this purpose are the document templates DeltaMaster reverts to. The things to consider during this action will be contemplated in this issue of DeltaMaster clicks!. We will be concentrating on Word and Excel in particular and elaborate on the possibilities around the PowerPoint export in a later issue.

Kind regards,
Your entire Bissantz & Company team

The context menu found in cockpits, analysis results and briefing books contains commands to output the currently displayed data into a Word, Excel, or PowerPoint document. Cockpits and analysis results offer the same option in the Save menu additionally, whereas in the briefing book it is contained in the I want to menu. During the export, the Office applications are “under remote control” by DeltaMaster. We do not use any macros as they are considered a security hazard in many places.

Each document created is based on a document template depending of its file type. This template may be edited with the corresponding Office application as you are used to. Thus you can design the look and feel of your reports on your own to a large extent. It is important to differentiate between three approaches:

1. You adapt the relevant template according to your demands. For instance, you choose fonts that meet your corporate design requirements, change the page layout from portrait to landscape, or integrate your company logo. These examples are depicted in detail below.
2. In ReportServer, the additional module for automated report generation and distribution, you decide about the template to be used for each job. This may be useful if you have to supply various target groups (e.g. internal and external recipients) with different layouts or styles.
3. There are special settings for the treatment of tables. These can be saved in the analysis session for all reports or individually. With this “regulator”, you precisely configure the way in which each content will be rendered.

Template Files

To export data from an analysis session, DeltaMaster expects the templates in the folder specified in the general options (Extras menu). By default, this is DeltaMaster’s installation directory. The file names of the templates are:

• DeltaMaster.dot as the document template for Word documents,
• DeltaMaster.xlt as the style template for Excel workbooks, and
• DeltaMaster.pot as the design template for PowerPoint presentations.
  

To modify the templates, open the file in the corresponding Office application. You should use the Open command in the File menu and choose the appropriate file filter in the dialog, as a double-click in Windows Explorer would cause Office to create a new document based on the template instead of opening the template for editing.
Word The DeltaMaster.dot file contains numerous styles that determine the appearance of the different parts of the reports to be exported. You can take a look at the individual styles when you simply export any existing cockpit, report or analysis result. Typical actions include:

• Modifying fonts. In the word menu Format/Styles, you can define the formatting of paragraphs and characters. If, for instance, you want your reports to appear not in Arial but some other font, you’ll want to change the style for headers, report titles, table rows etc. Many styles are based on “Standard”. The modifications made there will effect all dependent objects.
• Switch the page layout between portrait and landscape. This decision may be made in the File/Page Setup menu.
• Edit page headers and footers. In the DeltaMaster document template, the title page is treated differently from the following ones. This is particularly evident for page headers and footers. For instance, the DeltaMaster logo is not part of the first page header, but appears only on the remaining pages. To edit this section, insert a manual page break. On the second page, design the page header according to your preferences. Before you save the template, we recommend to delete the page break again; your modifications will be maintained.

Please take care not to delete any styles as they are required for trouble-free report creation.

Excel The templates for Microsoft’s spreadsheet calculation program are much less known than the ones in the word processor. The DeltaMaster.xlt file contains some cell styles which can be displayed and modified via the Format/Styles menu in the same way as in Word.

Global and Report-Specific Settings

The templates we mentioned so far are aiming at design aspects exclusively. In addition to these, DeltaMaster knows various parameters that allow you to control how data exports from tables are to be executed. You can find them in the Extras/Options menu. These settings apply to all reports – unless you specify in the report properties of individual reports that you want specific rules to come into effect instead of the global ones. Report-specific settings have primacy over the global ones defined in the options.

Export Options for Tables

Word Here you determine that certain representations of tables will be rendered as images (bitmaps) instead of textual tables. The image export is recommended especially for flex reports with graphical components. For both variations, the number of lines and columns may be restricted to reduce calculation time during the export and to limit the display to the crucial details. Defining blocks leads to the segmenting of the tables into several smaller ones in order to arrange them conveniently on multiple pages.

For pivot tables containing Sparklines, you can chose the technique to insert them into the Word document: Metafile uses a vector-oriented and thus clear-cut, nicely printable display. With SparkFonts, the columns and line segments will be converted in specially crafted TrueType fonts, equally allowing for razor-sharp printouts. The SparkFonts are installed automatically with all current DeltaMaster versions. The output as a bitmap is very fast, but the printing quality is minor compared to the other two options.

Excel The fundamental subdivision of a document into pages like in Word does not exist in Excel, so that options to limit or divide exported tables are superfluous here. Only at the point of printing out a table that won’t fit onto a page, one may wish that line or column headers are repeated on every sheet. For this purpose, define the line and column headers of pivot tables and crosstables as well as the fixed lines/columns in flex reports as rows/columns to repeat.

In Excel, it is not possible to insert an image directly into a cell and keep it there. Consequently, sparklines can be exported neither as bitmaps nor as metafiles. Exclusively by the help of SparkFonts, you can place cell-fixed images in a spreadsheet table.

Export Templates in ReportServer

In ReportServer, you can specify templates for each job individually. First pick the report format (Word, Excel, or PowerPoint) and then choose the desired Export template. If you leave these fields empty, DeltaMaster will use the default template files as described above.

Often the Better Choice: DeltaMaster in (Offline) Reader Mode

Despite all the possibilities the Office integration offers: Each export means a loss of analysis capabilities. Word and Excel documents are detached from the OLAP server forever, and it is not possible to tackle further analyses or even simply switch to another view anymore. Because of this fact, we recommend to save your analysis results as a .das file simply and pass them on to others. As a consequence, interactive reports with live data are available for each user. In the July 2005 issue of DeltaMaster clicks!, we have presented our OfflineReader as a particularly cost-efficient, easy-to-handle and still comprehensive entry mode for DeltaMaster.