You can specify certain aspects of how reports are formatted in your Excel workbook, PowerPoint presentation, or Word document before you run them. For example, you can ensure that a border is added around the results of every grid report.
Formatting settings applied before reports are run, are applied to all reports that you run after you modify the settings.
Use the steps below to format reports before they are run in your workbook, presentation, or Word document. These settings apply to grid reports, graph reports, and grid or graph reports within HTML documents. They do not apply to Report Services documents.
To format reports and documents before they are run
1 On the MicroStrategy tab, click Options. The Options dialog box opens.
© 2013 MicroStrategy, Inc. Formatting reports and documents added to Microsoft 49 To format grid reports
a Under the General folder, expand the Grid folder.
b To determine where totals are displayed on outline-style reports, select Layout. In the Outline area, select from the following:
Office Style displays totals in a separate row (selected by default).
Web Style displays totals with the first subtotal combined with the first row of each group. If the first subtotal is not Sum, the subtotal name is displayed in parenthesis. This option is not available in Office 2000.
Auto Outline reports with subtotals: If selected, Auto Outline is applied to grid reports that contain subtotals. Auto Outline allows dynamic expansion and contraction of rows. Cleared by default.
c To define how row and column headings are laid out, in the Report Headers area, select from the following:
Office Style: If selected, an extra row is inserted to accommodate overlapping header titles. Selected by default.
Web Style: If selected, an extra column is inserted to accommodate overlapping header titles.
Developer Style: If selected, an extra row and an extra column are inserted to accommodate overlapping header titles.
Compact: If selected, overlapping header titles are eliminated from the grid. If an overlapping title is Metrics, it is removed. If Metrics is not one of the overlapping titles, the row title is eliminated.
d To determine column/row sizing, in the Column/Row Size folder, in the Column Width area, select from the following:
For reports that contain column width information:
– Apply column widths: Column widths from the report’s definition are applied. Selected by default.
– AutoFit columns: Column widths from the report’s definition are ignored and columns are automatically sized to fit their contents. Text wrap settings are ignored.
– Default: Results are displayed in columns without modifying width.
50 Formatting reports and documents added to Microsoft © 2013 MicroStrategy, Inc.
For reports that do not contain column width information:
– AutoFit columns: Column widths are automatically sized to fit their contents. Text wrap settings are ignored. Selected by default.
For reports that contain row height information, in the Row Height area select from the following:
– Apply row heights: Row heights from the report’s definition are applied. Selected by default.
– AutoFit rows: Row heights from the report’s definition are ignored and rows are automatically sized to fit their contents.
– Default: Results are displayed in rows without modifying height.
For reports that do not contain row height information:
– AutoFit rows: Row heights from the report’s definition are ignored and rows are automatically sized to fit their contents.
Selected by default.
e For PowerPoint and Word: In the Scale folder for each product, you can select Don’t exceed slide (or page) dimensions n%, which determines the maximum size of reports as a percentage of their size.
By default, set to 100%; range 10-500%.
f By default, the formatting from the MicroStrategy report definition is applied to the report. To determine whether the report’s original formatting is used, in the Grid folder, select Formatting, then select or clear the Apply formatting from report definition check box. If you clear this check box, reports are displayed in a plain text format when they are executed. Selected by default.
Apply formatting from report definition on refresh: If selected, default report formatting is not applied when reports are refreshed, allowing any modifications made in Microsoft Office to remain.
Selected by default.
g In the Formats to Apply area, enable or disable specific aspects of MicroStrategy formatting. For example, you can ensure that
MicroStrategy currency formats in the reports are displayed, but not colors and fonts. All of these options are selected by default.
h If you are running grid reports into Excel workbooks and you want to perform calculations on that data using Excel functionality, you must
© 2013 MicroStrategy, Inc. Formatting reports and documents added to Microsoft 51 properly format the information. Select Apply date/time cell
formatting semantics to date/time values. Cleared by default.
i You can determine whether styles are shared across reports in your Microsoft Office product. In the Grid folder, select Styles, then select from the following:
Share all styles: Excel styles are shared by all reports in the workbook. Minimizes the number of styles used, thus potentially allowing more reports to be displayed. Selected by default.
Share styles per report: Excel styles are shared only by pages and fetches of a single report in a workbook.
Share styles by group: Excel styles are shared by all reports in the workbook using the same group name.
To format graph (chart) reports
a Under the General folder, expand the Chart folder.
b In the Default Chart Type area, you can select from the drop-down menu to set the default chart type. This is useful when a report definition does not contain a chart type or if there is no equivalent Microsoft chart type.
c In the Chart Refresh area, to use chart options from the report’s definition to format the chart when refreshed, select Apply
formatting from report definition on refresh. If cleared, refreshing updates the source data but retains any changes applied to the chart in Microsoft. Cleared by default.
In the Chart Display area:
d To display charts as static images rather than live charts, select Display charts as images. This is useful for charts not supported by Microsoft; to restrict user ability to modify a chart; or to match a chart exactly. Cleared by default. For details about adding charts as static images, see Adding graph reports as static images, page 183.
e Create 2D chart if chart contains a secondary axis: 3D charts defined with primary and secondary axes are displayed as 2D so both axes can be shown. (Microsoft does not support secondary axes in 2D charts.) If cleared, the chart is displayed as 2D but the secondary axis is removed. Selected by default.
f Display footnotes in graph title: Shows any footnotes within the graph title.
52 Formatting reports and documents added to Microsoft © 2013 MicroStrategy, Inc.
In the Data Table area:
g Show data table: Values for each data series are displayed below the chart. Not applied to pie, xy (scatter), doughnut, bubble, radar, or surface charts. Cleared by default.
Show legend key: If Show data table is selected, this option is available. If selected, legend keys are displayed next to the series label in the data table. Selected by default.
h To determine scaling in PowerPoint, Word, and Excel, select the appropriate Microsoft product folder, expand the Scale folder, and in the Chart Scale area, select one of the following:
Percentage of original width x height: Scale non-chart reports by a percentage of their original size as displayed in your Microsoft product. By default, this is selected and set to 100 x 100 pixels;
range 0 - 300.
Lock aspect ratio: The aspect ratio of non-chart reports is preserved. Selected by default.
Fixed width x height: Define explicit width and height for non-chart reports in specified units. By default, set to 400 x 300 pixels; range 0 - 10,000.
Recalculate scale on refresh: This option is below the Chart Scale area. Chart sizes are recalculated when refreshing. If cleared, the current size is preserved when refreshing. Selected by default.
i To set AutoScale options, under the General folder, expand the Chart folder and select Scale:
Use the chart definition to define AutoScale: The chart AutoScale in Excel is determined by the setting stored in the report’s definition. Selected by default.
Always false: The chart AutoScale is not set.
Always true: The chart AutoScale is set.
To format Quick Grids
A Quick Grid is a report display type in which the report is run as a CSV and imported into Microsoft Excel.
a Under the General folder, expand the Quick Grid folder.
© 2013 MicroStrategy, Inc. Formatting reports and documents added to Microsoft 53 b In the Formatting area, from the drop-down list, select the type of
formatting that you want to apply to the Quick Grids. A preview of the formatting appears on the right.
c To select which elements of the Quick Grid to apply the formatting to, choose from the following:
Select Number to apply the formatting to numbers.
Select Font to apply font formatting.
Select Alignment to apply alignment formatting.
Select Border to apply border formatting.
Select Patterns to apply cell pattern formatting.
Select Width/Height to apply width and height formatting.
d To re-apply formatting automatically to a Quick Grid when the report is refreshed, select Apply formatting on refresh.
e To flatten Quick Grid reports, in the Layout area select Flatten Quick Grid reports. When a report is flattened, the results are displayed with all attributes and metrics side-by-side on one axis.
f To remove the column in the grid that displays the word “Metrics,”
select Remove extra column.
To format PivotTables
a Under the General folder, select PivotTable.
b To configure which columns and rows attributes and metrics appear on, in the Initial Layout area, select one of the following:
Empty: Place no attributes or metrics on any axis.
Compact: Place one attribute in a row and all metrics in different columns. Selected by default.
Report: Place attributes and metrics in the same position they are in on the report used to create the PivotTable.
c In the Formatting area, from the drop-down list, select the type of formatting to apply to the PivotTable. Pivot Table Classic is selected by default. A preview of the formatting is displayed.
d To display elements that have no data after a refresh, in the Refresh area select the Show items with no data check box. When cleared, elements with no data are hidden. Selected by default.
54 Formatting reports and documents added to Microsoft © 2013 MicroStrategy, Inc.
2 Click OK.