MicroStrategy Reporting

BASIC REPORTING

MicroStrategy Desktop is a business intelligence environment designed to meet today’s sophisticated business intelligence requirements. It brings integrated query and reporting, powerful collaborative analytics, and investigative workflow to every desktop.

MicroStrategy Desktop enables project designers, report designers, and system administrators to design, create, and maintain an entire business intelligence system from a single interface.

Report

A Report is a visual presentation of data. A report sends a query to and accesses data from a relational database and presents it in a visually pleasing manner so that you can analyze the resulting information. A Report Services document (called a document) is a type of report that presents a broader set of information by including data from multiple reports in a boardroom-quality presentation.

Report Components

Rows and Columns - On a typical report, each row represents a business concept, such as products, employees, customers, stores, and so on. MicroStrategy calls these business concepts “attributes,” because they represent the many important attributes of a business.

-While the rows represent business concepts, the columns of a typical report represent financial calculations that are important to the business, such as inventory, sales, costs, profits, salaries, and so on. MicroStrategy calls these calculations “metrics.”

Attribute: A business concept that provides a context in which data is relevant. In the example of a report that shows sales in the Southeast, Southeast is the attribute.

Metric: A calculation that shows the numbers you want to see. In the example of a report that shows sales in the Southeast, sales is the metric. Metric calculations can show information at simple levels as well as at complex levels, such as displaying sales trends, growth patterns, percent-to-total contributions, and profit analysis.

Cells - Each individual cell on a report contains a single value that is produced by calculating data gathered from somewhere in your organization’s data source. That data is the focus of business analysis in a reporting environment.

Report Types

You can view a MicroStrategy report from different perspectives, depending on the type of work you wish to perform.

Grids - A grid report is the most commonly used type of report. Grid View displays grid reports using a formatted, cross-tabular display of the report data. Most business intelligence analysis is performed using this view. The following figure displays the Grid View of a report.

Graphs - A graph report is a representation of data in a visual format that can help you see overall trends easily, identify medians and exceptions, and so on. You display report data as a graph using Graph View. There are many different graph styles you can choose from to display your report data most effectively. The following figure displays the Graph View of a report in the bar graph style.

Inclined to build a profession as MicroStrategy Developer? Then here is the blog post on, explore MicroStrategy Training

Grid and Graph - Grid Graph View is a combination display of the Grid View and the Graph View of a report, side by side. The following figure displays the Grid Graph View of a report.

SQL View - SQL View displays the SQL used to generate the report. This view provides a good way to troubleshoot and fine-tune the selection of data that is retrieved from your data source and displayed in reports. SQL View also includes various execution statistics for a report, such as the number of rows, number of columns, the time it took to execute, and so on. The following figure displays the SQL View of a report.

Report Services Document

MicroStrategy Report Services documents (called documents) are available with the MicroStrategy Report Services product. Documents are a display of data from multiple reports with special formatting added, as shown below:

Report Services documents can be viewed, analyzed, and created in both MicroStrategy Desktop and Web. A document can contain data from one or more MicroStrategy reports. Documents can appear in almost as many ways as you can imagine and are generally formatted to suit your business needs, in a single display of presentation quality. Documents allow you to display your business data in a user-friendly way that is suitable for presentation to management for boardroom-quality material.

MicroStrategy Interview Questions

HTML Documents

A document can contain data from one or more MicroStrategy reports. Documents can appear in almost as many ways as you can imagine and are generally formatted to suit your business needs, in a single display of presentation quality. Documents allow you to display your business data in a user-friendly way that is suitable for presentation to management for boardroom-quality material.

OLAP Services

MicroStrategy OLAP Services is a MicroStrategy product that lets MicroStrategy Desktop, Web, and Office users make use of features that slice and dice data in reports without re-executing SQL against your data source.

View Filters

A view filter is different from a report filter, which restricts how much data is retrieved from the data warehouse. A view filter dynamically restricts the data being displayed on the report without re-executing the report against the warehouse. This capability provides improved response time and decreased database load.

It is important to note that you can use a report filter and view filter on the same report. The report filter returns a set of data for the report, which the view filter then further restricts.

Derived Metrics

Derived metrics perform calculations on-the-fly with the data available in a report. They are an easy way to present data already available on the report in different ways, providing further analysis of data. You can use derived metrics to quickly perform on-the-fly analyses such as margins, contributions, and differences between metrics included in the report.

These metrics are created based on existing metrics in the report. Since derived metrics are evaluated in memory, their computation does not require any SQL execution in the database.

Since derived metrics are created within a report, they can only be used for the report in which they are created. Derived metrics cannot be saved as individual objects in the project, and therefore cannot be applied to other reports in the project.

Derived Elements

A derived element is a grouping of attribute elements on a report. These groups provide a new view of report data for analysis and formatting purposes. For example, you can group data for the months of December, January, and February into a single element that combines and displays the data for the entire winter season.

Rather than having to define consolidations or custom groups, you can use derived elements to create these groups on-the-fly while viewing a report. Derived elements are evaluated on the report dataset without regenerating or re-executing SQL. Derived elements are defined by using a list, filter, or calculation to combine attribute element data.

Dynamic Aggregation

Dynamic aggregation allows you to change the level of report aggregation on-the-fly, while you are reviewing the report results. This feature allows metric values to be aggregated at different levels depending on the attributes included in the report without having to re-execute the report against the data warehouse. Dynamic aggregation occurs when the attributes included in the report layout changes. The attributes included on the report layout changes when you move an attribute or attribute form off of the report layout to the Report Objects pane, or when you move an attribute or attribute form from the Report Objects pane back onto the report layout. As included in the report layout changes, metric values are dynamically aggregated to the new level of the report.

Formatting a Report

Formatting a report involves highlighting certain data to enhance analysis, as well as changing the overall display or look and feel of a report. You can:

• Highlight important numbers
• Put the focus on specific sets of data
• Rename an object on the report, such as a business attribute or a metric calculation
• Apply your corporate look to a report for a business presentation.

Using the banding option, you can group rows or columns of report data using colors to enhance readability and make it easier to identify business concepts on which you would like to focus.

MicroStrategy’s auto styles are collections of formatting choices that can all be applied at the same time with a single click.

Thresholds

Thresholds are cells of data that are formatted differently from the rest of the data on a report; the formatting is applied dynamically, whenever the report is re-executed. Thresholds highlight particular data in a report by displaying special cell formats, symbols, images, or replacement text. Thresholds highlight particular data in a report by displaying special cell formats, symbols, images, or replacement text.

Threshold images cannot be viewed in Desktop, although all other threshold formattings, such as symbols and replacement text, can be viewed in Desktop. To see threshold images you have added to a report, view the report in MicroStrategy Web. Threshold images are also visible if the report is placed in an HTML document or Report Services document in Desktop or Web.

Creating a Threshold

Open a grid report

• From the Data menu, select Thresholds.
• From the drop-down list in the toolbar, select the metric to which you want to apply the threshold.
• Click on New Threshold, and name your new threshold.
• 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.

• Next, you define the formatting you want to be 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.

-Quick Symbol: Select this to replace the value that meets your threshold condition with a symbol. Choose the symbol from the selection in the box to the right.

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.

 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.

If you chose Quick Symbol earlier in this procedure, select the Allow users to display and hide the thresholds checkbox 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 the Hide Threshold or Show Threshold from the Data menu.

Click OK to save your new threshold definition.

Renaming Row and Column headers

You can give a report’s row and column headers meaningful names by renaming them. By creating an alias for an object on a report, the object can be displayed on that report with a different name, without changing its name in the MicroStrategy project.

Hiding a metric column

You can hide a metric object (usually a column) on a grid report, yet that metric’s data will still be included in any subtotals and grand totals you might have displayed.

With OLAP Services:

If you have the MicroStrategy OLAP Services product, you can hide any metric column by simply dragging it off the grid report into the Report Objects pane to the left of your report.

Without OLAP Services:

• Open a grid report.
• From the Data menu, select Report Data Options. The Report Data Options dialog box is displayed.
• Expand the Display category.
• In the Alias subcategory, click in the Alias column next to the metric you want to hide.
• Enter a ‘.’ (period), as shown in the image below.
• Click OK.
• From the Grid menu, select Options.
• On the Columns and Rows tab, in the Columns area, select Fixed.
• From the Select from the list drop-down list, choose the period (which is the aliased name of the metric you want to hide.)
• In the Pixels field, enter 0 (zero).
• Click OK.

Banding

Banding is a method of organizing or grouping data values in a grid report according to certain criteria. You can band rows or columns in several ways. You can band based on the number of rows or columns (for example, alternating color every 5 rows). You can also band based on the row and column headers (for example, sorting the Units Sold column in order, then applying alternating colors to sets of values).

The auto-style of a report provides the report’s default banding options, but you can define custom banding or even turn off banding for a report, regardless of its auto-style.

Auto styles

MicroStrategy comes with several presentation styles for displaying reports. These are called auto styles. Each auto-style automatically applies a set of formatting that includes color scheme, font style, and font type to a report.

Creating custom Autostyle:

• Open a grid report.
• Format the report as desired
• From the report’s Grid menu, select Save Autostyle As. The Save Autostyle As dialog box opens.
• Specify a name for the new auto-style in the Save Autostyle As dialog box. The name should be descriptive so you or other users can be aware of what formatting changes it will make when applied to a report.

Analyzing Data

When reports return large amounts of data, it can be difficult to easily understand what the data is telling you. Several MicroStrategy tools can help you analyze large amounts of data more quickly.

 Sorting

Sorting lets you move data so you can analyze that data more effectively. You can sort on any column or row that is on a grid report. When you sort, you determine the sorting order, either ascending or descending:

Ascending sort order arranges the data alphabetically, from A to Z, or lowest to highest, such as from 1 to 10.

 Descending sort order arranges the data in reverse alphabetic order, from Z to

A, or highest to lowest, such as from 10 to 1.

Sorting is processed by the MicroStrategy Analytical Engine, which means you can sort and organize the data on a report without taking up the time and resources to re-execute the report against your data source.

Searching

Whenever you want to quickly locate a specific data value in a grid report, or you want to jump to a section of a large report, use the Find feature. You can also use the Find feature to locate a string in the SQL syntax when viewing a report in SQL View.

The Find feature is not available in the Graph view.

Outlining

You can create an indented grouping of related data on a grid report by organizing the data into a standard outline style. Using an outline style, you can collapse and expand sections of related data.

Outlining is particularly useful when information displayed would otherwise involve repetitive entries. For example, you want to display sales for three years, 2004, 2005, and 2006. You also want data listed by month within each of the years. Rather than having all data visible for every month of every year, you can use an outline to expand and view just that data you want to see immediately and keep other data collapsed, to be expanded later for quick comparisons.

Multiple-page reports work in the same way. For example, if you are on page 4 of a multiple-page report and you want to collapse the data to the second level, then you will only be collapsing data that is displayed on the fourth page of the report.

Page­By

When you have a very large set of data on a report, it can be easier to handle that data by grouping the report data into logical subsets and viewing only one of the subsets at a time. To group data into subsets, you can use the page-by feature.

To retain page-by display, when saving the report, follow the below procedure.

• Open a report that contains a paged-by object
• Click the page-by field at the top of the report, and, from the page-by drop-down list, select the page you want to be displayed the next time you execute the report. The report refreshes, displaying the page of data you selected.
• From the Data menu, select Report Data Options. The Report Data Options dialog box opens.
• Expand the General category, and select Advanced.
• From the drop-down list called Retain page-by selections when you save this report, select Yes.
• Click OK to save your change and close the Report Data Options dialog box.
• Save the report and then reopen it. The page-by field you last selected is now the first page displayed.

Pivoting Data

Data pivoting enables you to rearrange the columns and rows in a report so you can view data from different perspectives. With data pivoting, you can do the following: Move an object (a business attribute or a metric calculation) and its related data from a row to a column. Move an object (a business attribute or a metric calculation) and its related data from a column to a row. Change the order of objects in the rows. Change the order of objects in the columns.

Report Limits

After a report’s results are displayed, you may need to further restrict the data displayed without changing how the calculations were performed. You can limit the data displayed in a report by specifying maximum and minimum values for a given metric. These maximums and minimums determine which rows of a result set are displayed in the report, and are called report limits. For example, the image below shows you a report that ranks all employee sales.

You want to see only the results of the top ten employees. If you apply a report limit to restrict the data displayed to the top ten employees, the data used to calculate the sales rank is not affected. Only the employees displayed changes, as shown in the image below.

Setting a Report Limit:

• Open a Grid Report.
• From the Data menu, select Report Data Options. Under the Calculations category, the Report Limit subcategory displays any report limits that may already be applied to the report.
• To apply new limits to the report data, click Modify. The Report Limit Editor opens.
• In the Object Browser, navigate to locate the metric to which you want to apply the limit. Double-click on the metric to add it to the Limit Definition pane of the Report Limit Editor.
• From the Operator drop-down list, select the operator you want. Examples include Between, Greater than, Less than, Exactly, and so on.
• Enter the Value that you want the operator to apply to.
• Click Save and Close to save the reporting limit.
• Click OK to return to the report.

If the report has a filter, the filter is applied to the report data first, then the reporting limit is applied to further restrict the data returned in the report.

 Calculating Data

Within a single set of data that is gathered from your data source in response to a reporter’s query, the results of calculations on that data can change drastically depending on a number of considerations, such as

Metric join types: These determine how tables of metric data (usually numerical data, such as sales, costs, or profits) are joined to each other.

The image below shows the metric join typesetting in the Report Data Options dialog box.

The different types of joins are

Default: This option sets the metric to use the join typeset for that individual metric when that metric was created with the Metric Editor. If no join type was determined this way for the metric, this option sets the metric to use the join typeset at the project level.

Inner: This option displays only the data common to all data source tables from which data is being gathered for this metric.

Outer: This option displays all of the data from all data source tables from which data is being gathered for this metric.

Attribute join types: These determine how tables of attribute data (business concepts, such as year, store, or item) are joined together.

Evaluation order: This determines in what order the various objects on a report are calculated. Objects that can affect the calculation of data to be displayed on a report include such things as metrics, report limits, and subtotals. Which object is calculated first, next, and so on can completely change the report’s results?

The default order of calculation is as follows:

• Compound smart metrics

• Consolidations, which are evaluated by their relative position on the report template:

Rows, from left to right

• Columns, from top to bottom

• Report limits

• Subtotals

Subtotals: These allow you to total metric data using a selected mathematical function.

 Answering Question about Data

Report Filters

A report filter sifts the data in your data source to bring back the information that answers exactly what you require.

The report filter will be displayed in the Report Details Tab as shown below.

You can configure the report details for a specific report, with the Report Details Formatting option in the Report Editor, or for the entire project, with the Project Configuration Editor. Settings configured at the report level override settings configured at the project level.

 Hierarchies

A hierarchy is made up of a group of related business attributes that are conceptually related to each other.

The creation of hierarchies is described in the previous chapter.

Examples of Time Hierarchy

 Drilling

Drilling allows you to view report data at levels other than that displayed in the report. You can investigate the data in your report quickly and easily with the help of drilling. Drilling automatically executes another report based on the original report to get more detailed or supplemental information.

You can set various options that determine how drilling works on a given report. These allow you to control how other users drill on the report when they execute it, or to preserve your own most useful drilling paths and drilling behavior for later reuse on a given report.

Enable or disable drilling from the below procedure.

• Open Grid report.
• From the Data menu, select Report Data Options. The Report Data Options dialog box opens.
• Expand General, then select Drilling.
• Select one of the following options:

Drill anywhere: Users can view data associated with the object they drill from, no matter which direction in the attribute’s hierarchy they drill: up, down, or across. Examples are provided above.

Drill down only: Users can only view data associated with objects lower in the hierarchy than the attribute on which they are drilling. Examples are provided above.

• Click OK to save your settings and close the Report Data Options dialog box.

When you drill on a report, you can have the page-by fields of the original report appear in exactly the same state in the drilled-to report, with all the same paging choices available.

 Refresh data on the report

There are several ways that data shown on a report can be refreshed so that the report reflects the latest values in the data source.

 Report Cache

A cache is the stored results of a report query that has already been executed. When the report is executed again, the system can quickly access the cache to display report data, rather than putting a load on the system to re-run the request to the data source.

The following methods can be implemented to ensure that refreshed data is accessible when a report is re-executed:

Delete a report’s cache: A system administrator can delete a report’s cache to ensure the data on that report is refreshed the next time it is executed. If the stored cache is deleted, the system is forced to submit the request through the data source again, thus gathering the most recent data.

Disable caching for a report: A system administrator can disable caching for a specific report so that a cache of results is never created in the system when that report is executed. In this way, every time the report is re-executed, the query goes through your data source and thus returns the most recent data.

You can see whether the results on a report have come from a MicroStrategy cache in two ways:

Check the Report Details section.

Using SQL View.

Answering Report Prompts

Any report can contain prompts. A prompt is a question presented to the user who runs the report. Depending on the answers the user provides, the report brings back and displays different data from the data source.

Answering a prompt report differently each time it is executed is one way to ensure that a report is executed against your data source and displays the most recent data. This is because different prompt answers usually require different data than what is stored in the report’s cache.

Prompts store defaults answers in Report Cache. To refresh the latest data from warehouse prompts, should not be saved as Static reports.

Saving Prompted Report:

• Open a prompt report.
• Answer the prompts and execute the report.
• From the File menu, select Save As. Navigate to where you want to save the report, and provide a name for the report.
• Click Save. The Save Options dialog box opens.
• Select whether you want to save the report as static or prompted:

Static: The report is saved with the currently displayed report and filter information. When you execute the report in the future, you are not prompted again.

Prompted: The report is saved with active prompts. The next time that you run the report, the report prompts you for answers again.

If you select prompted, you can choose whether or not to use the current prompt answers as the default prompt answers when you run the report again, as described below:

To save the current prompt answers as the default prompt answers, select the Set the current prompt answers to be the default prompt answers checkbox. When you run the report again, you will be prompted, and you can choose to use the default answers or change them.

To use the default prompt answers defined in the prompt, clear the Set the current prompt answers to be the default prompt answers checkbox. When you run the report again, you will be prompted, and you can choose to use the displayed default answers or change them. If default prompt answers have not been defined in the prompt, no default answers are displayed.

• If you select the Remember options next time checkbox, your selections above (in this Save Options dialog box) become the default method for saving all prompted reports when you save prompted reports in the future.
• Click OK. Your selections are applied to the report and it is saved.

 Table of Features

Create sample report

This chapter provides various methods to create new reports. When you create a report, you take existing objects in your MicroStrategy project and place them on the report. The objects represent the data in your data source.

Report Builder

This section describes how to create a report using the quick report creation tool called Report Builder. MicroStrategy’s Report Builder steps you easily through the process of quick report creation and lets you access a wide variety of report objects in your project to define your report.

To create a new report using Report Builder, navigate to File -> New and select Report.

From the New Grid pop-up select, Report Builder.

Report Builder asks for four specific pieces of information from you:

• The attributes to include on the report
• The metrics to include on the report
• Information to create a filter for one or more attributes; the filter is optional
• Information to create a filter for one or more metrics; the filter is optional

Select the attributes and facts and move them to right side. Click Finish to execute the report.

For Indepth understanding of MicroStrategy click on

• MicroStrategy Architect
• MicroStrategy Reporting
• DataWarehousing fundamentals
• Microstrategy Advanced Interview Questions
• General Database Queries And Practices