MICROSTRATEGY TUTORIALS

Introduction

A report is a MicroStrategy object that represents a request
for a specific set of formatted data from the data warehouse.
Reports are the focus and goal of business intelligence. They
allow users to gather business insight through data analysis.
The different parts of a report include: attributes and facts
from the warehouse, filters that determine how much data is
used to generate the report, and metrics to perform
calculations on the facts.


Report design versus report creation

Report design is the process of building reports from basic
report components in MicroStrategy Desktop and Web.
While report design is the most generic method for defining a
report, it also requires the most in-depth knowledge of the
project. In general, this method should be available only to
the select group of advanced users and report designers who
will design reports for others to use.

Report creation is the process of building reports from
existing, predesigned reports either in Desktop or in Web.
Given the wealth of reporting functionality that you can make
available to your users, you have the ability to design reports
that provide a wide range of options for users to create their
own reports.

Report creation is different from report design in that it
provides a more guided experience and does not require your
users to have a thorough understanding of the project. This
allows your users to create their own reports in a controlled,
user-friendly environment.

Designing reports
You create reports in the Report Editor of Desktop, which has
four report view modes:
• Design View describes the report definition and allows
you to create and edit reports. The attributes, metrics, and
other objects to be used in the report are displayed. You
do not have to execute the report to view or modify the
report structure.
• Grid View offers a formatted, cross-tabular display of the
actual report data after the report is executed.
• Graph View is similar to Grid View, but the display is in a
graphical format instead of cross-tabular.
• SQL View displays the SQL generated by the
MicroStrategy Engine and executed in the warehouse. It
also includes various execution statistics.

MicroStrategy Web provides the same report view
modes, although the equivalent of SQL View is called
Details.
You design reports in Design View, which allows you to select
the metrics and attributes to use on the report. You can also
define report filters, which determine the data used for
metric calculation.
You can add various formatting options, such as fonts and
styles, in either Design View or Grid View.


Interactive report editing
Once a report is saved, you have the option of allowing your
users to edit it interactively while viewing the results without
re-executing the report against the warehouse. This means
that the changes are performed in Desktop or the Intelligence
Server, rather than in the warehouse.
• Pivoting and page-by reorganizes report data by swapping
objects within an axis or by moving objects from one axis
to another.
• Sorting allows you to specify an ascending or descending
order to present the report data for a particular row or
column.
• The View filter restricts the amount of data displayed on
the report, by controlling the subset of data to be
displayed from the data retrieved from the database.
• Derived metrics are calculations defined on-the-fly with
the data available in the report. They are based on existing
metrics in the report to provide simple column math
capability.
• Report Objects contain all of the objects available for
display on the report. Use Report Objects to interactively
modify the content of the report while viewing the report
results. It displays the level of the report data definition.

• Thresholds and stoplights allow you to highlight data that
meets conditions by using different cell formats, symbols,
and images or replacement text.
• Subtotals permit you to add, remove, and edit the
subtotals at different levels for metrics on the report.
• Aliasing is the temporary renaming of objects for the
report display.
• Outline Mode creates an indented grouping of related
attribute elements, allowing you to expand and contract
sections of related data.
• Exporting is rendering the report in different formats or
applications, such as a spreadsheet or a word processor.


What is a filter?
A filter is used to select the data for calculating the metrics in
the report. It also restricts the attribute elements included in
the report. In our example, we use the Month filter, which
does not allow April, May, and December data to be included
in the metric calculations. For our purposes, these months
are not representative of the normal business cycle, so the
filter excludes them from calculations.

In summary, a filter affects the nature of the metric
calculation by restricting the information used to compute
the report metrics.


What is a report limit?
After all the metrics are calculated, you may need to further
restrict the data, without changing how the calculations were
performed. For example, you want to see only the top ten
employees from a report that ranks all the employee sales. If
you apply a report limit, the data used to calculate the sales
rank is not affected.
A report limit specifies a set of criteria used to restrict the
data returned in the report data set after the report metrics
are calculated. Because it is based on the report’s metric
values, a limit is applied after all of them are calculated.
The Report Editor allows you to set limits on any metric you
want to apply to the report. Report limits are defined using
operators such as between and less than.


What is a metric qualification?
A metric qualification is a filtering condition based on the
value of a metric. It contains an output level, which
determines the level at which the metric is calculated and to
which attributes the metric applies. Like every filter, a metric
qualification changes the nature of the metric calculations,
unlike a report limit, which is applied after the metrics are
calculated.


What is report as filter?
Report as filter allows you to create a report and use it as a
filter to generate another report. It is a different way to
achieve the same results as a metric qualification, but it is
easier to understand and create. Because the logic used to
generate the final report is clearer, MicroStrategy
recommends using it rather than the metric qualification.


Understanding how a report is executed



These four steps are the data definition section of the report
execution. The data definition establishes how the data is
accessed and manipulated in the data warehouse.
The other functions noted in step 4 comprise the view
definition, which represents how the data is viewed and
manipulated in the Intelligence Server.

Data definition and view definition objects

The following tables are samples of the information stored in
the data definition and the view definition.



Report designers are generally concerned with data definition
while report creators usually focus on view definition. Report
designers work on the details of reports to create a context or
environment for the report creators to work in. This
environment allows report creators to work within defined
limits, ensuring that only reasonable queries are submitted to
the database. Reasonable means that irrelevant data sets
cannot be created, nor can huge amounts of data be retrieved
from the warehouse. This designer-versus-creator convention
allows a group of report designers to be trained about more
advanced report functions while report creators can
manipulate reports without needing to understand the report
execution details. Through privileges, you can assign
different levels of functionality to different users.


Intelligent Cubes
The Intelligent Cube takes advantage of the separation of the
data definition and the view definition. The cube is a shared
copy of the report data saved in memory and is used for
manipulation of the view definition. The division allows
multiple reports with different views to share a common data
definition. This division allows the Analytical Engine to
perform highly interactive analysis against a set of data
without accessing the data warehouse. In practical terms, you
can modify reports and navigate through the data without
leaving the executed report or waiting for an additional
execution against the data warehouse.
The following diagram represents the Intelligent Cube,
different views that can use the Intelligent Cube, and its
underlying report cache.



The report view is an in-memory representation of the
current view of a report, based on the view definition of that
report. Each user running the same report has a unique
report view on the Intelligence Server. Manipulating the
report views is done after the report has been executed and
uses Intelligent Cube technology. Intelligent Cubes are
automatically instantiated whenever a report is executed; you
do not have to manually create Intelligent Cubes.

The MicroStrategy Intelligence Server leverages the
Intelligent Cube technology for in-memory report
manipulation, such as formatting and sorting. You can
exploit Intelligent Cubes when you design reports by allowing
report creators to use Intelligent Cubes when they
manipulate the view definition of reports.

The report cache is created by an Intelligence Server
schedule based on the data definition and the caching
properties of the report. It contains pre-processed report data
and is stored on disk. The Intelligent Cube is identical to the
report cache but is stored in the Intelligence Server memory.
However, the Intelligence Server uses sophisticated memory
management rules to decide if and when to move an
Intelligent Cube to disk.

The Analytical Engine has been enhanced to use Intelligent
Cubes to allow manipulation of the data displayed in the
report view.



What is a view filter?
A view filter is a quick qualification applied in memory to the
report data set. Because it affects only the view definition, the
report does not have to be re-executed in the data warehouse.
A view filter works in a manner similar to sorting and
subtotals, with which you are already familiar.
A view filter, just as the report limit, is always applied at the
level of the Report Objects list. However, the report limit and
the view filter are not interchangeable. A report limit restricts
the size of the report data set returned from the data
warehouse. In contrast, the view filter is applied to the report
data set without altering its size, allowing you to view a subset
of that information. It retrieves the information quickly
because a view filter dynamically accesses data already in
memory.

When considering how to build a report, a report designer
must balance the memory usage and the processing power of
the data warehouse and the Intelligence Server. A report limit
is more efficient in terms of data size because it does not
return unnecessary information from the data warehouse.
However, the data that a view filter does not display may not
be unnecessary, but rather the user does not need to display it
currently. He may need to display it later.

If a report limit is too restrictive, its utility is reduced because
users must more frequently redefine their data definition to
find the information they want. A view filter is more flexible,
allowing users to refine the analysis after the report is
executed, but it is more demanding on the Intelligence
Server. A view filter is intended to be a post-report execution
function to provide further investigation and refinement of
the report data set. As such, it is aimed at a report creator,
whereas the report limit is usually used by the report
designer.

A report designer must consider the following:



MicroStrategy provides the flexibility to choose report filters,
report limits, as well as view filters on a report-by-report
basis. Each has a different role in the execution and business
meaning of a report.


What is a derived metric?
While viewing a report, you may want to perform calculations
between columns. For example, a quick comparison, such as
Metric1 - Metric2, may be useful. Derived metrics allow you
to perform such column math, or math between metrics, in
the report. A derived metric is a calculation based on the
metrics already in the report. Derived metrics are generated
based on the report data set.
Derived metrics are always evaluated in memory, so they do
not need to access the data warehouse. Although they only
present the data already available on the report in a different
way, they are a powerful and easy-to-use tool. For example,
you can use derived metrics to quickly perform on-the-fly
analyses such as margins, contributions, and differences
between metrics already on the report


What is dynamic aggregation?

Dynamic aggregation occurs when you move an attribute
from the grid to Report Objects. The metric values roll up to
the new level of the grid. Dynamic aggregation occurs
whenever the attributes in Report Objects are not the same as
the attributes on the grid. Dynamic aggregation happens on
the fly, in memory.
The Analytical Engine selects the best aggregation function to
use, by default. However, you can also specify the function for
each metric. You can use any of the standard predefined
subtotal functions or the user-defined subtotals.



What are subtotals?
Totaling is another function of the report view that you can
define. Subtotals reflect data rolled up to the selected
attribute levels and can be applied dynamically to any report.
You can apply subtotals using one of many standard subtotal
functions such as, total, count, minimum, maximum,
standard deviation, and others. If these simple aggregation
functions do not satisfy your particular needs, you can create
a customized user-defined subtotal using the Subtotal Editor.
For more information, see the What are user-defined
subtotals? section that follows.
You can apply the subtotal by position, across a level, or using
group by.
• Applying a subtotal across a level calculates a subtotal
across the selected attributes. The subtotal is applied to
particular levels-rows, columns, and pages. This really
means “group by attributes to the left of the selected
attribute.”
In other words, if you have Region and Employee, in that
order, on a report (as on the Basic Report), selecting
across Employee means group by Region. A subtotal for
each Region, totaling the individual Employee-Region
values, displays on the report. Likewise, across Region
means group by none since there is nothing to the left of it
on the report. The result is a grand total. However, if the
report is pivoted and the order of the attributes changes,
the totals also change. If Employee is pivoted to the left of
Region, the across Employee subtotal means group by
none.

• The by position option means applying the subtotal based
on its location on the report. The subtotal is calculated
across all attributes and hierarchies on the report. It
provides the same behavior as across level, but without
selecting a level. Instead, the level is selected dynamically
so these subtotals change as you alter the layout of the
template. The two choices for by position are All
subtotals, meaning “across all attributes,” and Grand
Total, translating to “across the leftmost attribute.”
For example, you can choose to subtotal on rows and/or
columns. The Basic Report contains the columns Region,
Employee, Revenue, Cost, and Profit. You can subtotal by
both rows and columns, which provides totals at the
employee and region level for each metric.

By default, the by position option is selected.
• Group by applies the subtotal by the selected attribute
across all other attributes on the template, regardless of
position. Group by effectively allows you to use both
subtotal and sort by attributes that are not the furthest to
the left. The Grand Total check box allows you to also add
a subtotal grouped by nothing, effectively calculating a
total of all attributes on the template.
If a report contains Region, Category, and Quarter and
you group by Region, a Region subtotal always appears,
regardless of where Category and Quarter are located with
respect to Region. You can also group by multiple
attributes. For example, grouping by Region-Category on
that report provides a subtotal every time a new
Region-Category combination occurs.

Group by works best if the report is sorted by the same
attribute used to group the subtotals, regardless of
position.



What are custom subtotals?
By default, when you use subtotals in a report, the same
subtotal function is used for all metrics in the report. The
name of the subtotal is displayed in the subtotal line items
that appear in the report. You can use custom subtotals to
give you more control over the characteristics of a subtotal.
Custom subtotals allow you to define custom subtotal line
items that appear on your reports. Custom subtotals allow
you to do the following:
• customize the subtotal name that appears in the subtotal
line item.
• define different subtotal functions to be used on different
metrics in the report.
• specify the level of each total.
• turn off subtotaling for specific metrics on the report.

You can make the subtotal name dynamic by typing special
characters in the subtotal name field as listed in the following
table.




What are smart subtotals?
A compound metric, at a high-level, is composed of two
metrics, such as Metric1/Metric2. The subtotal of a
compound metric can be calculated in two different ways:
• Calculate the sum of all parts of the compound metric,
then perform the compound metric. This formula is
represented by Sum(Metric1)/Sum(Metric2).
• Calculate the compound metric for each row of the report,
and then roll up the data to the correct level. The formula
for this is Sum(Metric1/Metric2).

The first case uses smart subtotals, which calculate subtotals
on the individual elements of a metric. For example, the
Profit Margin metric is calculated as the Profit metric divided
by the Revenue metric. The Profit Margin metric can be
totaled as follows:
• Add all the profit values together. Add all the revenue
values together. Divide the two sums. This is a smart
metric.
• Divide each profit value by each revenue value. Sum up
these ratios.


What are shortcut metrics?
Shortcut metrics are based on metrics already included in a
report and provide a quick way to add new metrics to a
report. They are really just special derived metrics. Shortcut
metrics are available when you right-click on a metric column
or metric header and are based on the selected metric.
Shortcut metrics can be found in the Desktop only.
Shortcut metrics belong to one of the following categories:
• Percent-to-total metrics: display a percent in relation to a
selected total of each item affected by the metric.
• Transformation metrics: apply offset values, such as “four
months ago,” to the selected attribute.
• Rank metrics: apply a ranking number to the metric
values for a given attribute.


Advanced sorting
Sorting allows you to order the report data set to present your
business information in a more informative way. For
example, you can alphabetically sort country and region on a
report, allowing you to quickly find a particular region. The
Basic Reporting Guide discusses such quick sorting, which is
selecting a column or row to sort on.
Advanced sorting allows you to create your own, more
complex sorts for rows and columns. You can select the object
to sort by, the sorting order (ascending or descending), the
sorting criteria, and the position of the totals. The options for
the sorting criteria depend on the sort object. For example,
Employee can be sorted by last name, first name, Social
Security Number, or the attribute ID. The sorting criteria do
not have to be displayed on the report.
Multiple-key sorting, or hierarchical sorting, allows you to
sort data according to multiple sorting criteria in a
hierarchical manner. This means that the first criterion is the
basis for sorting. Any ties are resolved using the second
criterion, any remaining ties are resolved using the third
criterion, and so on. If a tie remains after all the criteria are
used, the default sort order is used as the tiebreaker. In a
simple example, you can sort by ascending employee last
name, then ascending employee first name. If two employees
have the same last name, their first names are compared to
alphabetically sort them. You can, of course, create more
complex multiple-key sorting.
Sorting metrics hierarchically allows you to use group totals
for sorting.


Custom Formats
Custom formats allow you to create your own formats for
data in a report. You can format text, numbers, and date and
time using custom formats. Once you create a custom format,
you can use it in other metrics and report objects as well.
Each custom format can have up to four optional sections,
one each for:
• positive numbers
• negative numbers
• zero values
• text
You can specify these sections, separated by semicolons in
the order listed above. If you specify only two sections, the
first is used for positive numbers and zeros, and the second is
used for negative numbers. If you specify only one section, all
numbers use the same format. The following paragraphs list
the different custom formats that you can apply for text,
numeric data, and date and time with examples of each type
of formatting