Tips & Tricks #5: Designing MicroStrategy Documents for Exporting to Excel

Overview

The following best practices will help ensure that your document is displayed correctly when it is exported to Microsoft Excel. When you export to Excel, the resulting spreadsheet looks like a PDF of the document. For example, objects have the same position and size in Excel that they do in a PDF.

For steps to export a document to Excel, see the section Exporting a document in MicroStrategy Web later in this blog entry.

When designing a document that might be exported to Excel, do the following to ensure that the document is displayed correctly in Microsoft Excel:

  • Know how different object types are exported and displayed in Excel, as shown in the following table:

Tip5-1

  • Choose Excel-compatible colors for all objects, including panels, shapes, and Grid/Graphs. Use the set of 40 colors that appear in the Color dialog box in the Document Editor. Excel supports these 40 colors in addition to many more. Other colors are matched by Microsoft Excel as closely as possible. Avoid using gradient colors, since they are not exported to Excel.

Tip 5-2

1 Excel Fill Colors

Tip5-3

2 Excel Font Colors

  • Use graph styles that are supported by Microsoft Excel. For example, if you include a Gauge graph in the document, it is not displayed in Excel. If you include a Combination graph, the exported version in Excel may not be displayed exactly like the original graph in MicroStrategy Web.
  • Avoid overlapping objects. When exported, the document may not be displayed correctly. For example, an object in the background of the document may be displayed in the foreground of the Excel spreadsheet.
  • Provide extra space around objects because they may increase in size when the document is exported to Excel.
  • Use text field borders to create lines and rectangles. Standard MicroStrategy line and rectangle controls may not be displayed correctly in Excel. You can also use a panel stack to create a colored background; for instructions to create panel stacks, see the Dashboard Creation Guide.

Tip5-4

3 Excel Border (Line) Colors and Weights

Avoid inserting line breaks within text fields. Line breaks (inserted by typing CTRL+ENTER) are not rendered in Excel.

  • Do not enable word-wrapping in a column header on a Grid/Graph in MicroStrategy. If you do so, the headers are not displayed correctly in Excel or PDF. Enable word-wrapping in Excel after you export the document.

Tip5-5

4 Turning off Word Wrap in MicroStrategy

Tip5-6

5 Turning off Word Wrap in Excel

  • Use an absolute file path to define the location of an image used in a document. Do not use a relative file path. Images in documents specified with paths relative to MicroStrategy Web and Intelligence Server are not displayed when exported to Excel.

Exporting a document in MicroStrategy Web

Exporting a document allows you view and interact with the document results outside of MicroStrategy Web. For example, you can view results in a PDF file within Adobe Reader, manipulate exported data in an Excel spreadsheet, or create an interactive Flash dashboard for off-line use.

You can export a document to the following formats:

  • PDF file: Export the document to a PDF file in a reader, such as Adobe Reader. You can view the PDF on any device with a PDF reader, such as another computer, a Linux machine, a Nook, or a Kindle.
  • Excel spreadsheet (.xls): Export the document to a Microsoft Excel spreadsheet for further manipulation and use. See the Document Creation Guide for tips to create a document that will display correctly when it is exported to Excel.
  • HTML file: Export the document to an html file in a separate window.
  • Flash file: Export the document to a fully interactive, stand-alone Flash file. All the Flash files in a project are exported in one of the following formats:
  • MHT format, which can be opened in Internet Explorer, and in Firefox with a third-party plug-in

The document designer determines the Flash file format.

You are prompted for the following when you export a document, unless the document designer has specified the default exporting options for the document:

  • If your document is grouped, you can choose to export the entire document or only the selected group element. Page-by allows you to view the document by a selected group element.
  • If your document contains multiple layouts, you can choose to export the entire document or only the layout currently being viewed by the user.
  • You can export a document from within the document, or directly from a folder. The following procedures describe both tasks. You can also export a single Grid/Graph to a PDF file or an Excel spreadsheet.

To export an open document

  1. In MicroStrategy Web, click the name of the document to execute it.
  2. From the Home menu, select Export, then select the file format in which to export the document. The options are:
    • Excel
    • HTML
    • PDF
    • Flash

       If an export format is not available on the toolbar, that export format has not been made available for this document.

3. Depending on the file type, you may be prompted to open or save the exported file. You can choose to either:

  • Open the file, by clicking Open. A copy of the document opens in a browser.
  • Save the file, by clicking Save. Name and save the file. Do not change the file type.

To export a document directly from a folder

  1. In MicroStrategy Web, right-click the document and select Run as.
  2. If the document contains prompts, answer them and click Export.
  3. If DHTML is disabled, click the Export icon or PDF icon in the Actions column.

*        If an export format is not available on the toolbar, that export format has not been made available for this document.

4. If DHTML is enabled, select the format to export to:

    • To export to a PDF, select Export to PDF.
    • To export to Microsoft Excel, select Export to Excel.
    • To export to HTML, select Export to HTML.
    • To export to Flash, select Export to Flash.

5. Depending on the file type, you may be prompted to open or save the exported file. You can choose to either:

    • Open the file, by clicking Open. A copy of the document opens in a browser.
    • Save the file, by clicking Save. Name and save the file. Do not change the file type.

Exporting a Grid/Graph from a document

You can export a single Grid/Graph displayed in a document to either a PDF file or an Excel spreadsheet. This provides additional flexibility to share and print your data, so that you do not have to export the entire document.

The document must be displayed in Express or Flash Mode. In Express Mode, the title bar of the Grid/Graph must be displayed. The following procedures describe both tasks.

*         A widget is exported as a Grid/Graph.

To export a Grid/Graph from Express Mode

  1. In MicroStrategy Web, open the document in Express Mode.
  2. Click the icon on the title bar of the Grid/Graph and point to either Export to PDF or Export to Excel.
  3. If you choose PDF, the PDF file opens in another browser window.
  4. If you choose Excel, you are prompted to open or save the exported file. You can choose to either:
  5. Open the file, by clicking Open. A copy of the document opens in another browser window.
  6. Save the file, by clicking Save. Name and save the file. Do not change the file type.
  7. In MicroStrategy Web, open the document in Flash Mode.
  8. Hover your cursor over the Grid/Graph. Icons for exporting to Excel and to PDF are displayed.
  9. Do one of the following:

To export a Grid/Graph from Flash Mode

  • To export the Grid/Graph to Excel, click the Export to Excel icon. You are prompted to open or save the exported file. You can choose to either:
  • Open the file, by clicking Open. A copy of the document opens in another browser window.
  • Save the file, by clicking Save. Name and save the file. Do not change the file type.
  • To export the Grid/Graph to PDF, click the Export to PDF icon. The PDF file opens in another browser window.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s