Microstrategy Reporting in 9 (I).

During the last days I've been validating the functionality of MicroStrategy 9 in regard to reporting, trying to go for the more general and simple to the more specific and complex, trying to go through all the components and developing examples of analysis based on the dimensional model we have seen in previous blog entries.

Before starting, I recommend the video posted by Microstrategy to become acquainted with the work environment (see here ).

As initial consideration, indicate that the Microstrategy reporting is somewhat limited in terms of design, it will always work with tables where you can be tabulated by putting the various elements of the report (attributes, metrics, filters, prompts, custom groups, etc).In addition to the reports, we can build what is called in Microstrategy Document, which will allow us to include several reports and articles that we design ourselves (such be used to build dashboards, interactive boards, etc). This part we will see when we look at the design of dashboards.

In the picture you can see the report editor. It's where the design is made of the reports. We can see 5 distinct sections, namely:

Editor de Informes

Editor in Microstrategy Reports 9

  • Report Objects: the top left.In this section displays all the elements we have included in the report table. We'll have attributes, indicators, calculated fields, etc., We have inserted in the report layout. From here you can modify its properties. For example, we change the name under which the item appears in the report, the representation, if it appears in the report or is hidden, etc.
  • Report Details: In the top right.Displays information about the report when run.
  • Filter report: in the upper right. It's where we associate the report filters or prompts that will be used to restrict the information returned.
  • Object Explorer: in the bottom left. Is the tree from which you can navigate through the various elements defined in the MicroStrategy metadata: user hierarchies, attributes, metrics, etc. From this window you can drag the filter section or the report template to include the selected item in the report.Is contextual and only leaves inserted in each place the elements that can be used
  • View Report: is where we design the layout of the report. We really are creating a template that is associated to the report. Is a pivot table where we including elements for which the information be broken down.

Building Simple Reports

Let's start by designing a simple report. We analyze the net amounts and unit sales by year and region. In addition, we want you can select the year / s of analysis. To do this, open the Report Editor and select, in the object browser, the following elements:

  • Filter: we navigate the object explorer in the folder objects public select filters which we had defined as to select the year, dragging a filter Section Report.
  • Editor de Informes de Microstrategy

    Sales by year and region

    • Attributes and indicators, then select from the object browser attributes Year and Region and took him to the ranks of the report template. Finally, from the indicators folder, perform the same operation with the Net Amount indicators and units, positioned in the columns of the table.

    Then change the style of one of the available report (specifically the issue Cakes). See later that the styles can be customized at all levels (colors, fonts, font, cell color, color of subtotals, etc.). With a few mouse clicks we built our first report of analysis. We ran and we obtain the following result:

    Attributes and indicators "src =" / files/uploads/32image/Respinosamilla_bi/ventas-por-ano-region.jpg "/>

    Sales by Year-Region

    Observe the result. We displayed all sales of the year, broken down by region, with details of amounts and units. In the report Details section, we listed the criteria against which you ran the report (filters). In this place we will have the reference that restrictions are being applied to the report. Object Windows Object Explorer report and continue to appear if we go by "on the fly" changes to the report (adding new attributes or indicators, changing descriptions, etc.).These windows can be hidden with no problem (because users will not have to perform such tasks).

    Objetos de Microstrategy

    Objects Microstrategy

    From this simple report, let us see the different properties that can be modified to customize its appearance and features according to our needs.

    Formatting Reports.Customize rows, columns, titles, fonts, colors.

    Estilos predefinidos de informes de Microstrategy

    MicroStrategy has approximately 20 pre-defined report styles, as we see in the image.

    Each style has its own set of colors, fonts, patterns of squares, etc. In addition, the modifications that the design we can keep creating our own custom styles.

    Some of the things that you can set the format of our reports are:

    - Format titles of rows and columns, font, font size, effects on the letter (bold, italic, underline), font color, box color, borders, alignment.

    - Format of values ​​(indicators): font, size, effects on letter, number format, font color, box color, borders, alignment.

    - Display of attributes: when we are customizing the box has an attribute, we can select which value is displayed in the (ID, DESC or both).For example, if we attribute to the material, we can see only the ID (code will be material), or just the ESC (is the description of the material), or both, in the order you want.

    - Format the subtotal: in the same way, we can change the properties we have seen previously for subtotals of an attribute (both header and securities).

    With this parameter at the level of the elements of a report, you can give the look you want to highlight certain values ​​or to make them more intuitive to the user.For our sample report, we have made several changes. We included the indicator% range and we have changed the display color and format your values ​​(blue font color and bold) to highlight them on the rest of the columns. We have also changed the colors of the cells in subtotals and totals to differentiate based on the attribute to which they relate.The amended report would look like:

    Informe básico de Microstrategy con cambio de formato

    Basic report with format change

    The format change is as simple as selecting the box header attribute or indicator for which we want to change the format, and with the right button context menu will appear.The format change we can perform just as well from the report design or run time, interacting with the report presents information. The context menu is as follows:

    Propiedades del cambio de formato en un informe de Microstrategy

    Contextual menu for format change

    In the example, we have checked the box header attribute Year. We may change the format of the header (headers Year), values ​​(values ​​Year), head of the subtotals of the attribute (Total box) and the format of the subtotal values.We see that from the contextual menu able to change the representation of the attribute, remove it from the grid or report, lock the object to stay fixed when we have reports with many rows or columns or even access to the modification of attributes, metrics, etc. from this place (you will require the appropriate permissions. Changes in schema objects from this option does not refresh yourself until you run the report.)

    For indicators, we can also provide category-level formatting (numbers, fractions, scientific notation, percent), number of decimal places, separating thousands, custom formatting, etc (more or less the same formatting options in the typical application Spreadsheet).

    Definition of thresholds.Types of thresholds.

    Noting the previous report, we see that we have highlighted the value of the indicator% margin over other indicators in the report. In this report we want to focus on the analysis of this indicator of business. To focus on analyzing the values ​​of a more agile, we will use another feature of Microstrategy reports, which are the thresholds. Thresholds allow us to set a custom format to the indicators according to these values.With thresholds can highlight certain values ​​as we are interested, so that the user of the reports go to the "grain" when analyzing the information displayed, so that there is something that catches your eye directly and notify you (negative return values , for example). Might be a way to set alarms in the data.

    We have 4 types of thresholds, which are:

    • Format: modify the format of the values ​​under the conditions indicated. We can change the font, color, highlight, background, etc, to highlight the values ​​that meet certain criteria.
    • Replace the text: we can change the value by acertain text to let us know about something (eg, the revised text to indicate that there is something wrong that should be analyzed or EXCEEDED text to indicate that sales have exceeded budgeted at a certain level of analysis).
    • Quick Symbol: replace the values ​​that satisfy the conditions for a particular symbol, which also can give a distinct color.
    • Image: replace the values ​​that satisfy the conditions for an image.

    Editor de umbrales de los informes de Microstrategy

    Editor thresholds

    As you see in the image, after selecting a particular indicator by selecting thresholds we set the criteria for each threshold.In the example, we created 4 ranges. For each range of values, we have determined a different format for the values, so that negative amount of returns we will see highlighted in red, with yields lower amounts (between 0 and 10) will see them in yellow, and so on until the values over 25%, which we will draw in black considering the normal values.

    Applying these threshold values ​​to our report example, we get the following result:

    Informe de Microstrategy con visualización de umbrales

    Rate thresholds report format and symbol rate

    Notice how the values ​​in red indicate a negative return to us and are elements that should be studied.In the report I have included in a new column a threshold using symbols. It can also be an easy way of drawing attention to a certain value.

    Thresholds can be set to level indicators, level indicators and sub-totals or subtotal level only.

    Advanced Management.

    It may happen that the reports have great information and we are interested in this sorted by certain criteria (for single values ​​that we deem relevant analysis).For example, we may want to analyze the sales amount from highest to lowest, or lowest to highest returns (to analyze the lowest).

    For this, MicroStrategy has the Advanced Management editor, which allows us to set complex sort criteria on the data. In our example, we sort the data by cost from lowest to highest, but not in general for all data in the report, but in the analysis of each year to appear in the report.

    Editor de Ordenación avanzada de Informes de Microstrategy

    Advanced Management

    You can see as we have stated two criteria for sorting.First, to sort the data by year in ascending order. Second, for each year's data, which in ascending order based on the value of the indicator% margin. Thus, for each year, we at first the regions with lower margins, which are what we want to analyze. The report would look like:

    Criterios de ordenación en Informes de Microstrategy

    Report per year and margin management

    Advanced management can be established at both rows (as in the example), column-level or page level.We will see later that is paging.

    Filters indicators. N values.

    You may also be the case that the reports be presented with a lot of irrelevant information (for example, customers with very low sales in a detailed analysis by customer) and we are interested in filtering on the values ​​retrieved from the database.This filtering would be a posteriori, and would occur at the time in which the analytical engine to display the information.

    We have two types of filtering on a metric:

    • N values: where we select, for example, the first N values, the last N values, excluding the N first values ​​or exclude the N past values ​​of the display. We may also indicate, in the ranking, the values ​​are or are not within a certain range (eg, values ​​between 10 and 30 position in the ranking), or a set value ranking (the twelfth in the rankings).
    • Condition: equally, we can set a filter indicating a condition that must meet the indicator values ​​(in the case of not complying will not be displayed).

    Filtro de Indicador en Informes de Microstrategy

    Filter Indicator (10 first values ​​of sales)

    To test the filter on an indicator, we modified our sample report replaces the attribute by the provincial region.Similarly, we sorted the data in descending order by the Net Sales Amount column. In this report we listed a lot of data and want to be left alone with the first 10 values.Applying the filter that we have seen, the resulting report would be:

    Informe de Microstrategy con filtrado de indicadores

    Sales Report by Year and Province - 10 First import

    Keep in mind that when you set the filtering, subtotals values ​​are altered, and reflect only the sum of the values ​​shown on the screen.


    To explain the concept of paging, we will modify our report example and we will replace the attribute Year by the region attribute.We analyze sales by region and within each region, see the details of each of the provinces. The amended report would read:

     Informes de Microstrategy con paginación

    Sales Report by Region and Province

    To facilitate analysis, we see only one region can be displayed and moving for the different regions. For this, Microstrategy provides paging functionality.One or more of the attributes are removed from the grid and appear in the Page section. In this section the selected attribute appears as a menu with different values ​​and to apply it in the report as a dynamic filter that can be changed for different values. The same report page in the region attribute is as follows:

     Informes de Microstrategy con paginación

    Sales by Region and Province - with pagination

    You can include all the attributes of page you want within the reports.It's as easy as dragging a page section or from the contextual menu, clicking on the attribute box, select the Move -> To Page.

    Defining subtotals and totals. Types of aggregation.

    Let's create a new report to explain the possibilities of aggregation that provides Microstrategy. Where we will build an information report by the attribute Year, then by quarter, and finally by the sales representative assigned to customers. As indicators of business, we analyze the net amount of sales, units sold, the range and number of orders for each one of them.The report would be something like:

    Informe de Microstrategy sin totales

    Sales by Year, Quarter and Commercial (without Totals)

    In a report like this, where we have no aggregation, we can add a grand total at the end of the report, and include subtotals for each level of detail of information. In addition to the subtotal, which is a sum of values, Microstrategy provides other aggregation functions such as Total, Average, Minimum, Maximum, Product, median, mode, standard deviation, variance and Geometric Mean.

    To include a total, select the option Data -> Total General and we will see a total for the report.It is important to note that we have defined the indicators (when created) so that subtotals can not be made with them. For example, in our project, the indicator% range is configured in this way does not make sense because the sum of individual values ​​of percentages (is data that tells us nothing is more, we can be misleading).

    In addition, we can include subtotals indicating the menu Data -> Subtotals.We see the subtotals editor where you can select what type of subtotal want to use, and place in the table that show subtotals for each row, column or page. Look at the example where we have put a subtotal at the end of each level and an overall total. In addition, we selected the minimum and maximum of each column, we indicate the minimum and maximum values ​​of each indicator at each level. It may be useful to have identified certain values ​​or calculations quickly.

    Informe de Microstrategy con totales y subtotales de ventas

    Sales by Year, Quarter and Commercial Subtotals

    In addition, we have some subtotals that allow us to other totaled more out of the ordinary.For example, we indicate that we attribute to obtain a global subtotal and display the final report. Or set different levels of grouping attributes for subtotals. In our example, the global subtotal commercial attribute looks like this:

    Informe de Microstrategy con subtotales de ventas

    Sales by Year, Quarter and Sales to Total by Commercial

    We define as many as we wish to include subtotals summarization desired by the desired attribute in the desired location.

    Hierarchical display of results.

    When we have a lot of information on screen, with many levels of detail, MicroStrategy allows us to organize the display of hierarchical information (what they call Show results indented).Another technique is different from the page that we saw earlier. To see more detail, we will build a report that includes for each year, product lines and detail of the materials in each product line, with analysis indicators previously seen.When the report runs, we see that you have more than 400 lines and is difficult to move information.

    To change the display hierarchy, select the Grid menu -> Show Results indentandos, and change the look of the report the following:

    Informe de Microstrategy con visualización jerárquica de resultados

    Report with indented results

    Note that in the top of the grid there are 3 tables (with values ​​1, 2 and 3).In our case, we selected the value 2, and we see the data broken down to the level of product line. If you choose the value 3, we would see all the information plus materials. And if we were at level 1, we only see the data broken down by year.

    It can be a useful way of presenting information at a level not too broken to see the data as clearly and change hierarchical level in the case of wanting to delve deeper into certain aspects.