Steps to Format MicroStrategy Reports
Threshold formatting ideas:
To create a report that looks like a scorecard, use thresholds and assign different colors to different ranges among the values in the report. This allows you to classify the information into broad classes.
With thresholds you can do the following:
- Define an unlimited number of conditions for each metric calculation on the report.
- Define the following formatting properties for the cells that meet the threshold condition:
p Font type, size, style, and color
p Cell background color (fill)
p Alignment
p Number format
p Borders and patterns
- Replace the cell value with any text you want for each cell value that meets the condition specified. For example, you can replace all values where Dollar Sales is greater than $5000 with the text “Exceeded Sales.”
- Replace the cell value with an image when the condition is true.
- Replace the cell value with a symbol, selected from a predefined list of symbols. You can also set symbols to be switched back and forth between the symbol and the underlying value.
To create and apply a threshold to a grid report in Desktop:
1. Open a grid report.
2. From the Data menu, select Thresholds.
3. From the drop-down list in the toolbar, select the metric to which you want to apply the threshold.
- You can easily copy a threshold to multiple metrics on a report, after you create the threshold for one metric. Information is at the end of this procedure.
4. Click on New Threshold, and name your new threshold.
5. Define the condition that the value must meet to have the threshold formatting applied. Click the text Click here to start a new qualification. The parts of the condition appear, each of which must be defined:
- Click Field first, to select the business attribute or metric calculation that is part of your condition.
- Click Operator to select an operator, such as In List, Not in List, or Where.
- Click Value to specify the elements from the available list.
6. Next you define the formatting you want displayed for cell values that meet the condition you have defined above.
First, select one of the following from the Format drop-down list:
- Format: Select this to change fonts, colors, styles, sizes, and so on. This formats the values that meet your threshold condition.
- Replace Text: Select this to have your own text appear. This replaces the value that meets your threshold condition with text. Type the text in the (text) text field. The text should be limited to 255 characters.
- Image: Select this to replace the value that meets your threshold condition with an image file. Specify how the image is stored by clicking Absolute path and selecting one of the following from the drop-down list:
Absolute path (the default), for example, c:\images\img.jpg
Relative to HTML Document directory, that is, a relative path from the document directory where the image is stored, for example, images\img.jpg
On the network, that is, a path on the local area network, which is in a UNC (Universal Naming Convention) format, for example, \\ machine_name\shared_folder\img.jpg
On the Web, that is, a URL to an image file, for example: http:/ /www.microstrategy.com/ images/img.jpg
7. If you selected any option except Image above, click Edit the threshold formatting on the toolbar to apply formatting to the metric values, replacement text, or symbol.
8. Select whether to apply the threshold formatting to subtotals. Click one of the following icons on the toolbar:
- Metric Only: Applies the threshold condition only on metric values.
- Metric And Subtotals: Applies the threshold condition on metric and subtotal values.
- Subtotals Only: Applies the threshold condition only on the subtotal values.
Find out how to perform MicroStrategy Report optimization in this insightful blog now.
9. If you chose Quick Symbol earlier in this procedure, select the Allow users to display and hide thresholds check box if you want to allow other analysts to change between the metric value and the symbol that you have specified for the threshold value. Users can switch between the symbol and value using the F12 function key or by selecting Hide Threshold or Show Threshold from the Data menu.
10 Click OK to save your new threshold definition.
Copying a threshold across multiple metrics in Desktop:
You can use copy/paste functionality to quickly add the same threshold to many metrics in a report. You can then edit the thresholds as necessary, for any of the metrics.
To copy a threshold in Desktop:
1. Follow the steps above to create a threshold for a metric on a report.
2. In the Threshold Editor, select the threshold to copy, and click Copy in the toolbar.
3. In the drop-down list at the top left, select the metric that you want to copy the threshold to.
4. Click Paste on the toolbar. The threshold is copied to that metric.
5. By default, the copied threshold is named “Copy of [original threshold’s name]”. Click the threshold name to change it if you choose.
6. Edit the threshold, if necessary.
7. Save the thresholds and return to the report by clicking OK.
To display the thresholds on your report in MicroStrategy Web:
1. Run a report.
2. From the Tools menu, select Report Options. The Report Options dialog box opens.
3. On the Grid tab, select the Show thresholds check box.
4.Click OK at the bottom of the dialog box. Any thresholds in the report are displayed.
To display quick thresholds on a grid report in MicroStrategy Web
1. Run a report.
2. Select a metric header on the grid report. To select multiple metric headers, hold CTRL and select the headers.
3. Select Data on the menu bar.
4. Do one of the following:
- To highlight cells in green and red, click Quick Thresholds on the toolbar.
- To have a symbol replace metric values that fall within a range of values, click the arrow next to Quick Thresholds and select the symbol to display on the report, according to the following:
– To replace all metric values with green, red, and yellow symbols, select a yellow symbol. – To replace only the top and bottom 20% ranges with green and red, select a red or green symbol. The middle-range metric values are not replaced with a symbol.
Creating a threshold for a metric based on a metric: Visual thresholds
Visual thresholds are thresholds defined for a metric, which are based on that same metric or on another metric. Visual thresholds use range expressions
Applying gradient colors to visual thresholds
A gradient blends two colors to create a gradual color change in the background of a visual threshold. You can select the two colors, as well as the direction of the blending. The direction is called the Gradient Variant, and your options are:
- Left to Right
- Right to Left
- Top to Bottom
- Bottom to Top
To apply a gradient to a visual threshold in MIcroStrategy Web:
1. Create a threshold
2. From the Data menu, select Visual Threshold Editor. The Visual Threshold Editor opens.
3. Click the Format icon. The Format dialog box opens.
4. On the Color and Lines tab, open the Color drop-down menu and select Gradients. The Gradients dialog box opens.
5. Select a color from the Color 1 drop-down menu.
6. Select a color from the Color 2 drop-down menu.
7. Select the shading style from the following:
- To shade horizontally, select Horizontal . Then select the Variant to apply (you can choose to shade from left to right or from right to left).
- To shade vertically, select Vertical. Then select the Variant to apply (you can choose to shade from top to bottom or from bottom to top).
8. Click OK. The Gradients dialog box closes.
9. Click Apply to apply the gradient color to the visual threshold.
10. Click OK. The Format dialog box closes.
Creating a threshold for a multiple metrics: Advanced thresholds
You can use advanced thresholds to apply formatting to a report or a grid in a document, based on multiple metrics and more complex expressions than a visual threshold.
To replace a null value with a specific value:
1. Open a grid report.
2. From the Data menu, select Report Data Options.
3. Expand the Display category and select the Null Values subcategory.
4. To replace a null value for the final report display only, enter the replacement value (such as a zero) in the Null Value Settings area for any of the situations listed:
- An empty value is retrieved from your data source.
- An empty value is calculated in the cross-tabulation process.
- An empty value appears when the report is sorted.
5. To replace a null value during the calculation of report data, enter the replacement value in the Aggregation Null Values box.
6. Click OK.
Creating an alias:
You can create an alias by renaming a row or column header. Use the appropriate procedure below, depending on whether you are using MicroStrategy Desktop or Web.
To alias an object name in MicroStrategy Desktop:
1. Open a grid report.
2. If the Formula toolbar is not displayed, from the View menu, point to Toolbar, then choose Formula.
3. Click the column or row heading of the object on the report that you want to rename. The name of the selected heading is displayed above the report on the Formula toolbar.
4. Type in the new name for the object.
5. Click the green checkmark (called Validate and apply) and save the new name.
To alias an object name in MicroStrategy Web:
1. Open a grid report.
2. From the Data menu, choose Rename/Edit Objects. The Rename/Edit Objects dialog box (DHTML) or panel (HTML) is displayed.
3. Select the object to rename from the Object drop-down list.
4. Type the Name for the object and click OK to save your changes.
Determining whether aliases are used on a report:
You can review any report to determine whether a column or row header is an alias or shows the original column or row name that comes from your MicroStrategy project.
To determine whether an alias is used on a report:
1. If you have not already done so, open a grid report.
2. From the Data menu, select Report Data Options. The Report Data Options dialog box opens.
3. Expand the Display category, and select Alias. Compare the Object name with any Alias name that appears. In the image below, aliases used for the objects Call Center, Non-Web Sales, and Web Sales are Store, Main-Street Revenue, and Internet Revenue, respectively.
Changing from aliases to original names:
You can change all row and column names from their alias to their original name. Use the appropriate procedure below, depending on whether you are using MicroStrategy Desktop or Web.
To quickly reset all names in Desktop:
1. open a grid report
2. From the Data menu, select Report Data Options.
3. Expand the Display category. In the Alias subcategory, all aliases are listed.
4. Click Reset All to change all column and row headers back to their original names.
5. Click OK.
To quickly reset all names in MicroStrategy Web
1. Open a grid report.
2. From the Data menu, select Reset Data.
To format the display of a custom group:
1. Open a grid report.
2. From the Data menu, select Report Data Options.
3. Expand the Display category and select the Object Display subcategory. Any custom groups on the report are listed here.
4. Click in the Display column next to the custom group you want to format. From the drop-down list, select Flatten or Hierarchical.
5. Click OK.
To hide a metric column on a report:
1. Open a grid report.
2. From the Data menu, select Report Data Options. The Report Data Options dialog box is displayed.
3. Expand the Display category.
4. In the Alias subcategory, click in the Alias column next to the metric you want to hide. For this example choose Revenue
5. Enter a ‘.’ (period), as shown in the image below.
6. Click OK.
7. From the Grid menu, select Options.
8. On the Columns and Rows tab, in the Columns area, select Fixed.
9. From the Select from the list drop-down list, choose the period (which is the aliased name of the metric that you want to hide).
10. In the Pixels field, enter 0 (zero).
11. Click OK.
When you run the report, the metric you chose in the procedure above (Revenue) is now hidden and does not appear on the report.
Resizing a column or row: Column handles and Fixed Column Width mode
Use the appropriate procedure below, depending on the degree of control you want when resizing rows and columns:
- Use the column handles procedure if you want to simply drag column handles around to resize a column.
- Use the pixel height and width procedure to enter a specific number of pixels for column width, to assign a different width to specific columns, and to enter a specific number of pixels to adjust row heights.
To display column handles in Desktop;
1. Run a grid report.
2. On the Grid menu, select View Column Handles.
3. Column handles appear along the top of the columns of the report. In the following image, the report on the top has no column handles displayed. The report on the bottom shows column handles displayed.
4. However your cursor over a column handle until the cursor changes to a double arrow.
5. Drag the handle left or right to make the column narrower or wider.
To create custom banding in Desktop:
1. Open a grid report.
2. From the Grid menu, select Options. The Grid Options dialog box opens
3. On the General tab, select Custom banding.
4. Click Settings. The Banding Settings dialog box opens.
5. Set the banding options as desired. For details on each option in the dialog box, click Help. In the example above, to band by columns, you select the banding criteria By column header and select the banding color.
6. Click OK. Then click OK again to close the Grid Options dialog box.
To turn off banding in Desktop:
1. Open the banded report.
2. From the Grid menu, select Options. The Grid Options dialog box opens.
3. Select No Banding and click OK.
To enable or disable banding in MicroStrategy Web:
1. Open a grid report.
2. From the Tools menu, select Report Options. The Report Options dialog box opens.
3. On the General tab, select or clear the Show Banding check box.
Keeping row and column names visible: Locking headers
You can scroll side to side or up and down in a large report without losing sight of the row or column names, by following one or both of the following procedures.
To lock row headers:
1. Run a grid report.
2. From the Grid menu, select Lock Row Headers.
To lock column headers:
1. Run a grid report.
2. From the Grid menu, select Lock Column Headers.
To format report borders in Desktop:
1. Open a grid report.
2. In the top left-hand corner of the grid report, right-click in the empty area and select Formatting, then select Grid Borders. The Format Cells dialog box opens.
3. Select one of the Preset icons. The None icon on the left removes all borders from the report. The Outline icon on the right automatically adds a border around the outside of the report.
4. If you chose to add a border, select the type of line Style you want, such as solid or dashed, and then select a Color.
5. Click on a line in the Border area and you can see your selections appear on the sample page in the Border area.
6. Click OK to save your changes.
7. Some border effects can be seen well in Desktop, while others are more clearly seen in Print Preview mode. To view your border settings, from the report’s File menu select Print Preview.
Formatting a combined grid and graph report
Grid Graph view displays a report as both a cross-tabbed grid report and a graph report in the same screen. You can determine the size and position of the grid (and, in consequence, the size and position of the graph) in Grid Graph view.
To format Grid Graph view:
1. Open a report. For steps to open a report, see Opening a report, page 7.
2. From the View menu, select Grid Graph View.
3. From the Data menu, select Report Data Options. The Report Data Options dialog box opens.
4. Expand the Display category and select the Grid Graph Mode subcategory.
5. Adjust any of the following settings for the grid report:
- Grid Position determines where the grid is placed in relation to the graph. For example, if you set the position to Top, the grid is displayed on top of the graph. If you set the position to Left, the grid displays to the left of the graph, as shown in the image below.
- Grid Percentage specifies how much space the grid occupies in the display area. For example, if this option is set to 75, the grid occupies 75% of the report display area while the graph is resized to fit in the remaining 25%, as shown below.
- Minimum Grid Percentage is the minimum space that the grid can occupy.
6. Click OK.
Formatting a graph report:
You can format a graph report in many ways, including the following:
- The style of the graph (for example, a pie graph, a bar graph, or a scatter graph)
- The size and location of the graph, graph legend, titles, and axis labels
- The color of different sections of the graph (for example, the color of the pie slices on a pie graph)
- Whether a threshold (conditional formatting) appears on the graph
Read these top trending Micro strategy Interview Q’s that helps you grab high-paying jobs !
Viewing a graph report:
To view a graph report, you can open any report that was saved as a graph report. You can also convert almost any grid report to a graph report, using the procedure below.
To view a graph report in Desktop or MicroStrategy Web:
1. Open a grid report.
2. From the View menu, select Graph View (in Desktop) or from the Home menu, select Graph (in MicroStrategy Web).
3. If this is the first time you are viewing this report in Graph view, you are prompted to select a graph style. Select a bar graph style, since it suits most sets of data. For information on selecting other graph styles,
Changing a graph’s style
Perform the following steps to modify the graph style. Use the appropriate procedure below, depending on whether you are working in MicroStrategy Desktop or Web.
To change a graph style in Desktop:
1. Open a report in either Graph or Grid Graph view.
2. From the Gallery menu, select a graph style to use with the graph. The graph is updated with the new style.
To change a graph style in MicroStrategy Web:
1. Open a report in either Graph or Grid and Graph view.
2. From the Formatting toolbar, select a new graph style to use. The graph is updated with the new style.
Defining a graph color for metrics
You can define the color used for a metric when it is displayed as a series in a graph. By default, the graph color that you define for a metric overrides any color schemes for the graph report.
To define the graph color for a metric in a graph report:
1. Using MicroStrategy Desktop, log in to a project and navigate to a graph report.
2. Right-click the graph report and select Run. The Report Editor opens, displaying the report as a graph.
3. From the View menu, select Grid View. The report is displayed as a grid.
4. In the report results, right-click the metric, point to Formatting, and select Metric Name Headers. The Metric Name is the name of the metric. The Format Cells dialog box opens.
5. From the Chart tab, in the Series Color drop-down list, select one of the following options:
- <default>: Displays the default graph color that was defined for the metric using the Metric Editor.
- Solid: Displays a single color for the metric when the metric is displayed as a series in a graph report. You can select the color from the Fill color drop-down list.
- Gradient: Displays a blend of two colors in a gradual color change for the metric when the metric is displayed as a series in a graph report. You can select the two colors for the gradient from the Color 1 and Color 2 drop-down lists, and then click one of the squares to set the direction of the shading between the two colors. The colors can blend left to right, right to left, top to bottom, bottom to top, horizontally centered, or vertically centered.
- Pattern: Applies a pattern to the object’s background. Patterns can help to distinguish between the risers of different series in a graph without having to rely solely on color. For example, the three bar risers shown below can be distinguished by color as well as by the pattern that they use.
- From Graph: Displays the color defined by the graph’s default color scheme.
6. Click OK to save your changes and close the Format Cells dialog box.
7. From the View menu, select Graph View to display the report results as a graph.
Preset formatting: Autostyles:
MicroStrategy comes with several presentation styles for displaying reports. These are called autostyles. Each autostyle automatically applies a set of formatting that includes color scheme, font style, and font type to a report.
To apply an autostyle in Desktop:
1. Open a grid report.
2. From the Grid menu, point to Autostyle Selected and choose an autostyle from the available list.
To apply an autostyle in MicroStrategy Web:
1. Open a grid report.
2. Click the Grid menu. If DHTML is disabled, click Go.
3. Select an Autostyle from the drop-down list. If DHTML is disabled, click Apply Style.