Tag Archives: Documents

Data Blending: Why are Some Metric Values Blank in Documents Using Multiple Datasets in MicroStrategy Analytics Enterprise 9.4.1 (Part 8)

MicroStrategy Analytics Enterprise

Introduction

Starting with MicroStrategy Analytics Enterprise 9.4.1, Report Services documents can contain grids with objects coming from more than one dataset.

Multiple Datasets in a Single Grid/Graph/Widget Object in MicroStrategy Web 9.4 [2]

Users now have the ability to add attributes and/or metrics from multiple datasets to a single grid, graph, or widget. For example, if Dataset #1 contains Category and Revenue and Dataset #2 contains Category and Profit, a grid can be created which contains Category, Revenue, and Profit.

Part 8 - 1

Administrators can control the use of multiple datasets in a single grid, graph, or widget through the Analytical Engine VLDB properties window at the project level.

  1. Right mouse click (RMC) on the project name.
  2. Select Project Configuration.
  3. Click on Project Definition.
  4. Select ‘Advanced’.
  5. Click “Configure” under the Analytical engine VLDB.

Part 8 - 2

NOTE:

The default value is set to: “Objects in document grids must come from the grid’s source dataset only”.

Users can set the set the source of the grid to a particular dataset or choose no dataset (in which case, the MicroStrategy engine will determine the best suited dataset). [1]

The MicroStrategy Analytical Engine displays no data for metrics in ambiguous cases or when there is a conflict. Ambiguous cases can arise in cases where multiple datasets contain the same objects.  Examples based on the MicroStrategy Tutorial project have been provided to explain this information.

Note: When the MicroStrategy Analytical Engine cannot resolve the correct datatset as explained in the cases below, the data displayed for these will correspond to the value chosen for the missing object display under Project Configuration > Report definition > Null values > Missing Object Display. The default value for this blank.

Case1:

Multiple datasets have the same metric. Only one dataset does not contain this metric and this dataset is set as the source of the grid.

This case is explained with an example based on the MicroStrategy Tutorial project.

1. Create the following objects:

a. Dataset DS1 with the attribute ‘Year’ and metric ‘Profit’.

b. Dataset DS2 with the attribute ‘Year’ and metrics ‘Profit’, ‘Revenue’.

c. Dataset DS3 with the attribute ‘Quarter’ and metric ‘Cost’.

2.  Create a document based on the above datasets and create a grid object on the document with the following objects: ‘Year’, ‘Quarter’, ‘Profit’. Set the source of this grid to be the dataset ‘DS3’.

3. In the executed document, no data is displayed for the metric ‘Profit’ as shown below.

Part 8 - 3

In the above example, the metric ‘Profit’ does not exist in the source dataset ‘DS3’ and exists in more than dataset which are in the document i.e., it exists in both ‘DS1’ and ‘DS2’. Since the engine cannot just randomly pick one of the two available datasets, it chooses not to display any data for this metric. If users do not want such blank columns to be displayed, set the source dataset so that such ambiguity does not arise.

Case 2:

The same metric exists multiple times on the grid. For example, users can have a smart compound metric and a component metric of this compound smart metric on the grid in the document. The smart metric and the component metric are from different datasets.

This case is also explained with an example based on the MicroStrategy Tutorial project.

1. Create the following objects:

a. Dataset DS1 with attribute ‘Year’ and metric ‘Profit’.

b. Dataset DS2 with attribute ‘Year’ and metrics ‘Revenue’, ‘Profit’, ‘Profit Margin’ (this is a compound smart metric calculated from metrics Revenue and Profit).

2. Create a document based on the above datasets and create a grid object on the document with the following objects: ‘Year’, ‘Revenue’, ‘Profit’ and ‘Profit Margin’. The source of this grid object is set to DS1.

3. In the executed document, no data is displayed for the metric ‘Profit Margin’, as shown below.

Part 8 - 4

In the above example, since the source of the dataset is set to ‘DS1’, the ‘Profit’ metric is sourced from this dataset and the metric ‘Revenue’ is sourced from the dataset ‘DS2’ (as this is the ONLY datatset with this metric). However, for the metric ‘Profit Margin’, the component metric ‘Profit’ exists on dataset ‘DS1’, so this becomes a conflict metric and is not displayed. If the source of the grid is changed to ‘DS2’, the data is displayed correctly as shown below.

Part 8 - 5

References:

[1] MicroStrategy Knowledgebase, Why are some metric values blank in documents using multiple datasets in MicroStrategy Analytics Enterprise 9.4.1, TN Key: 44517, 12/16/2013, https://resource.microstrategy.com/support/mainsearch.aspx.

[2] MicroStrategy Knowledgebase, Multiple datasets in a single grid/graph/widget object in MicroStrategy Web 9.4, TN Key: 44944, 09/30/2013, https://resource.microstrategy.com/support/mainsearch.aspx.

NOTE: You may need to register to view MicroStrategy’s Knowledgebase.

Tips & Tricks #9: How Do Changes on the Source Report (Dataset) Get Reflected in MicroStrategy Report Services 9.x Documents

In MicroStrategy Report Services Documents, document datasets and their original source reports  (such as a grid report being used as a dataset) are not completely connected to each other. Depending on the changes made on the source report, it can be reflected differently on the document. Basically, the change can be divided into two types.

Type 1 – Formatting Changes

Formatting changes, for example, changing autostyles, thresholds, subtotals.

If a user chooses the option Add to Section without Formatting, the grid/graph showing on the document will not use the report’s stored formatting. Any formatting changes on the source report will not be reflected on the document.

Tip 9-1If a user chooses the option Add to Section with Formatting, the grid will be added with the current format of the report.  However, any formatting changes made to the source report AFTER the dataset has been included in the document will NOT be reflected on the document.

For example, a user disabled the subtotal (see screenshot below) for the original report after the report has been included as a dataset in a document, the document will still show the subtotals.

Tip 9-2

To force the document to recognize the report’s formatting changes, the user needs to delete the grid/graph from document section and add it again using the With Formatting option. By doing this, the latest formatting properties of the grid/graph on the source report are retrieved.

Type 2 – Adding/Removing Objects and Modifying Report Filters

Another type of changes made on the report involving adding/removing objects and modifying report filters.

Unlike the formatting change, this type of change does carry over from the source report to the document datasets.

For example, if users add/remove/modify report filters (see screenshot below), the change will be reflected on the data when running the document.

Tip 9-3

If an object is removed from the source report (see screenshot below), the user can see the change in the document’s Dataset Objects window. After the user runs the document, the object will be removed from grid/graph on the document.

Tip 9-4

If an object is added to the report (see screenshot below), the change will show in the document’s dataset objects window. However, the object will not be automatically added to the grid/graph. The user has to manually add the object to the grid/graph or add the dataset to the section again to make it show up.

Tip 9-5

NOTE:   As of MicroStrategy 9.0, a new feature was introduced where a user can add a dataset report to a Report Services document as a shortcut by selecting the Add to Section As a shortcut option, as shown below:

Tip 9-6

If the grid/graph is added to the document using this option, then the document would be updated automatically if ANY type of change is made on the source report.

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.