Inheriting MDX cube source formats for metric values

You can specify for the metric values in MicroStrategy MDX cube reports to inherit their value formatting from an MDX cube source. This enables MicroStrategy MDX cube reports to use the same data formatting available in your MDX cube source. It also maintains a consistent view of your MDX cube source data in MicroStrategy.

Inheriting value formats from your MDX cube source also enables you to apply multiple value formats to a single MicroStrategy metric. Normally, a MicroStrategy metric can only have one value format for all of its values. For example, all values for the Profit metric are displayed with a currency format.

Custom groups and consolidations can also apply multiple value formats for a metric. However, custom groups and consolidations are not available for MDX cube reports. For information on custom groups and consolidations, see the Advanced Reporting Help.

However, some MDX cube sources allow data that can be mapped to MicroStrategy metrics to have more than one value format. For example, profit data in your SAP BW MDX cube source has some values displayed with a currency format, while other values are displayed with a percentage format.

This scenario is common when reports include characteristic structures mapped to MicroStrategy attributes. These structures can perform calculations that return various value formats. This is shown in the report below, which includes a Regions attribute mapped to a characteristic structure:

Another common scenario in SAP BW MDX cube sources is when formats are defined to reflect the locale of the data. This also requires multiple value formats for MicroStrategy metric data.

For example, in SAP BW you can define your profit data to return currency formats based on the locale of the data. You can then map this data to a Profit metric and a Country attribute in MicroStrategy. When you include Country and Profit on a report, you can select whether to inherit MDX cube source formatting. If you choose to inherit formatting, currency formats are applied based on the locales listed for the Country attribute.

Steps to specify that the metric values in MicroStrategy MDX cube reports inherit value formats from an MDX cube source are provided in the procedure below.

Prerequisites

• To create and modify an MDX cube report in MicroStrategy Developer, you need to have Developer privileges, including the Define MDX Cube Report privilege.

• To access the VLDB Properties Editor, you need to have the Developer privileges, including the Use VLDB Property Editor privilege.

• At least one MDX cube must be imported into your MicroStrategy project. Importing MDX cubes is often handled by a MicroStrategy architect. For more information on importing MDX cubes into MicroStrategy, see Importing MDX cubes in the MDX Cube Reporting Help.

To inherit MDX cube source formats for metric values

1. In MicroStrategy Developer, log in to a project.

   VLDB properties cannot be modified from MicroStrategy Web.

2. Browse to an MDX cube report, right-click the report, and select Run. The MDX cube report is executed.

3. From the Data menu, select VLDB Properties. The VLDB Properties Editor opens.

4. From the Tools menu, select Show Advanced Settings.

5. In the VLDB Settings list, expand MDX, and then select MDX Cell Formatting.

6. Clear the Use default inherited value check box.

7. Select one of the following options:

• MDX metric values are formatted per column: If you select this option, MDX cube source formatting is not inherited. An MDX cube report that is defined to format metric values individually for each column is shown below:

  

  You can then format each metric on the MDX cube report to use only one value format for all the metric values.

• MDX metric values are formatted per cell: If you select this option, MDX cube source formatting is inherited. Metric value formats are determined by the formatting that is available in the MDX cube source, and a metric can have various value formats. The report shown below is defined to inherit formatting from the MDX cube source, and applies multiple formats to the values of metrics.

  

  You can use standard MicroStrategy techniques to further format the inherited formatting for metric values. However, you must use default Number formatting. Modifying the Number formatting for the metric values to anything other than default removes any inherited formatting from the MDX cube source. Number formats include general, fixed, currency, percent, and so on.

• Click Save and Close to save your changes and close the VLDB Properties Editor.

The MDX Cell Formatting VLDB property can also be defined for database instances connected to MDX cube sources. Defining this VLDB property for a database instance applies the definition as the default for all MDX cube reports associated with the database instance. For steps to define this VLDB property for a database instance, see Opening the VLDB Properties Editor in the System Administration Help