Dashboard and Scorecards in Microstrategy 9. Using Report Services documents (II).

Example Dashboard. Analysis of Commercial Agents, products and regions.

In our first example of document type board, we will try to build a scorecard summarizing the evolution of a particular year to see information from different areas, namely Commercial Agent at first, level level and finally product Geo. For each of these areas, we will have a tab (and a set of reports and graphics on each).

To do this, we prepared a batch of reports, appended to the document as data sets will allow us to display the information as desired.

Agents Tab

In this section we will have 5 components of the different types we can use in documents, which are:

• Selector (type list box): This control will be associated with attribute values ​​trader.In the control will display the list of all the sales agents are in the period and an attribute Total (for the total of all of them). This value will be marked by default. Evolution Controls Monthly Sales Agent and Annual Sales by Region will be linked to the selector, so that changing the set value, the information from the other controls vary dynamically.

Agent Selector

• Evolution graph Monthly Sales Agent: Vertical line type chart where we see the monthly performance of sales amount, units and room for the selected agent.
• Annual Sales by Region Table: A table where we will, for the selected agent, the sales volume and margin units broken down by region.

In addition, we have two more components will not depend on the agent selected:

• Annual Sales table by Agent: Includes sales of all agents, with the percentage of total sales, ranking.the mark and an indicator thresholds to detect low or negative returns.
• Annual Progress Chart and Line by Agent Product: Vertical Area chart type to comparatively analyze all the agents and the weight of the different product lines sales.

The five controls described are all linked to two sets or data sources (Business Report - Month the first four and the last graph the report Commercial - Product Line).

The end result of design, including all the elements mentioned is as follows (we see the execution in interactive mode).

Example Board - Commercial Agents Information

You can see that in the switch box is marked agents Total value.To be changing the set value will be updated monthly evolution agent and the sales table by region, the other two controls remain fixed (as shown global information).

Product tab

In this tab you will have 4 components, with the aim of seeing how fast the annual sales by product attributes of the dimension.

• Chart / Table Changes Monthly Sales Product Line: Vertical Area chart type to analyze the monthly performance of sales by product line.Also included is the display of the data table to give context to the chart.
• Varietal Table Income: Annual income for a variety of grape.
• Chart / Table Income Target: Target annual revenues Product Market. The Circular shall be a graph (pie) for the weight of each element in the total. Besides displaying the graphic information in tabular form to give context to the chart.

The display of the tab to run is:

Sample board - Product Information

All components of this tab are related to the data set Product - Month (is a report that draws information from sales attributes product line, month, target and varietal).Thus, with a single report we are seeing 3 different views of the information on the board.

Region tab

In this section we analyze the report annual sales information from a geographic standpoint.To do this, include the following components:

• Tables annual sales by region: visualize table where the amounts of sales and margin units broken down by region. Also, include the weight of each region in each indicator (percent of total). As a new element, we will make the region attribute value is a selector, so that some components of the tab is updated as the selected region.
• Anu sales tableales region - material: This table will appear the 10 top-selling products in the selected region.
• Annual sales by province Table: Tabular appear, for the selected region, the breakdown of sales for each province, including the percentage of sales over the total.
• Graphic annual sales by province: Like the previous component, but in a pie chart (to see the weights of each province), we see the sales amounts and units.
• Table annual sales region - Client: This table will appear the 10 clients with more sales in the selected region.
• Annual summary table Geographic Areasas: This table will appear the annual sales volumes to the classification of regions by area (using elements derived from them).

The Region tab display to run is:

Example Board - Information Regions

For the construction of the tab controls we used 3 reports (Sales by Region - Client for the top 10 customers, sales by region - the top 10 Handout materials and the rest with the sales by Region - Province).As we go changing the selection of the main control region, they will be amended detailed information by province and the top 10 customers and materials from that region.

Example Dashboard. Evolution of Product Line Sales.

The following example is a checkerboard analysis of sales trends, but focus on the analysis of the Product Line attribute within the Product dimension. Also, on this board are going to use Flash widgets that passage will help us see some drawbacks of the ability to view documents in multiple formats (problem in the use of components and different display formats.)The period of analysis is a range of dates, which indicate to execute the document (instead of an annual period as in the previous report).

The board is governed by a control bar type selector buttons, associated with attribute values ​​Product Line (you can see at the top of the board, under the title). This control will determine the information displayed in all other controls the board, which are:

• Graphic Monthly sales Evolution Product Line: for the selected product line, we see the evolution of sales per month, both revenue and units sold, in a vertical line chart.Included in the chart the indicator values ​​at each point of the line.
• Scrollbar graphic time series: it is a flash widget that allows us to move through a series and go see the results graphically. In our case, the series will determine the attribute the day, and every day, we see the evolution of amounts and units sold. The information displayed will correspond to the selected product line. We can get moving down the series in the upper guide graphic, the same way that we can go bigger or smaller by the period of analysis to be visualized in the chart below).
• Set of markers for analysis of target: using flash widget type indicator, we will build a set of them to see the evolution of sales by Target Market on one side and on the other hand, the comparison between actual and budgeted sales in the period (in markers on the right).In the same way as in previous tests, we see only the information of the selected product line.
• Table analysis of monthly returns: for the selected product line, we will see a detailed analysis of various concepts per month. In particular, we will see sales and overall weight of the total, the average sales price, the absolute margin, the percentage andweight of the global total, and finally the number of orders and the average amount of each order.

The flash mode execution of the document would be as follows (it is necessary to correctly display the flash widgets).

Example Board - Product Line Analysis

In this case, we used three sources of information (product line - Month to month changes in sales and profitability analysis, product line - Date of daily time series and product line - Target for all markers .)

You can download Flash export from the board here .You could see how it works dynamically to the report (as if you were running, but with a particular dataset). You must download the file to disk and then open it with Internet Explorer (allowing the execution of ActiveX controls that carries including Flash).

Some thoughts on the use of documents.

In our case, we used the Desktop tool for the initial design documents.In this work environment, build documents and do a PDF preview mode or Flash mode to scroll through as is the presentation (with limitations on the formatting settings or control properties widgets, for example).

When the objects included in the report, we always have the contextual menu with which they can modify the general properties (size, colors, relative to other objects, effects, titles, filter on the element (only in terms of attributes), sets of data that is associated with the item, etc.).. The context menu will vary depending on what we selected, showing different options depending on the type of component.To include graphics, tables or widget controls, double-click on the item (or selecting the Modify option in the contextual menu), enter edit mode and can change on particular aspects (eg, a table and attributes can indicators included in the style of the table, calculated indicators, subtotals, thresholds, display format (font size, colors, fonts), derived elements, organization, size of rows and columns in the display, etc. In a graphic editing mode you can select the chart type, titles and all specific attributeschart, depending on the type of this, etc). When we are in edit mode of an object in the main menu options appear for the object (for example, a grid displayed the Insert, Format, Data, Grid, Move, etc.)..

Web Design Mode Neighborhood

If we use a graphic into a document (like when we conducted reports) or by making a widget, so we pass the object table to add attributes and indicators needed (remember that each type of graphic or widget has some attributes requirements and indicators in rows and columns to work.)After setting these items, we will move to a graphical display to see what the object in the previews. This is not necessary for some widget that are not related to data (for example, the RSS reader or editor selection of dates).

When documents have quite elaborate, it's time to make adjustments to the format, and it is advisable to do so from the Web environment. In this environment, as we saw, we have several kinds of display. Display types are as follows (in addition to the design mode):

• Editable Mode: Similar to the design, but the controls are displayed with information so that we can get an idea of how it will format the results.In this mode you can make all the settings of the design views. The flash widgets do not work and can not change the display attributes of this type of object. For example, this mode is ideal for making adjustments in the sizes of rows and columns, as we are seeing the controls with real information (this setting is also done in design mode, but blindly, because we see the result with data).

Editable Mode - Resize rows and columns

Both the design mode as the mode is still editable designer-oriented documents.The following modes are modes of execution:

• View mode: is a static, like we're seeing the result of implementation of the report in a PDF. You can not make any adjustments and flash widgets do not work.
• Interactive mode is a mode of execution and can not change any aspect of the document layout.Only we can change aspects of the components that make up the document, the run-time modifiable. For example, a table, as we see in the picture, we can navigate through the attributes in the event that you have allowed to perform management, aggregation, set view filters, move or remove items, format the fields, representation rename, create derivative or resize rows and columns (it would be similar to when a report ejecutabamos reporting module, as we have almost the same options for adjusting the results of a report).And also allows the use of derivative elements, that the reporting was exclusive reports on intelligent hubs.

• Flash mode: it is also a mode of execution. The adjustment options of the components are very limited (almost nonexistent). How much you can sort or move columns to the left and thus right. if they work all around and you can configure the properties of them. In Flash mode dimensional navigation does not work or any activity can be performed on the data (creating indicators, creating derived elements, formatting, aggregation, etc).But if we give interactivity to reports, as the selectors if they work. When we export a document in Flash Mode, with the generated file integrating all the data in the execution of the document (there will be taken into account, as we may see a small part of the data in controls, but the data sets has lots of information, which will make the flash file is very large). In the image, setting the scrollbar series.

Flash Mode - Temporal Properties Widget Series

The default mode in which a document is executed and the available modes are properties that are listed at the document level, as we see in the picture below.Therefore, we will determine which mode the display will be available. If, for example, use widgets controls, one would see in Flash mode. Likewise, if we use graphics that are not going to view certain Flash mode, one would use in interactive mode.

Document Properties - Display Modes

In the examples we have not included navigation within reports or graphics, nor the call to other reports by linking attributes.But the functionality is available and could have taken advantage of it to delve deeper into the data (especially for analyzing certain aspects that draw attention, such as trends, negative values ​​or below expectations, etc.).

Conclusions

During the preparation of the samples, based on our project Enobia (I remember is a deficit and therefore nonexistent), I noted how important it is to be clear to be analyzed, to clearly define the scope of this analysis and indicators key that needs to be studied.The examples described are simple and are not covered in depth by the requirements of analysis of sales area of ​​any company, but try to see the possibilities of what can be built using the MicroStrategy tool, combining different elements, giving momentum to the reports and reusing all the features of OLAP reporting and navigation that we saw in previous blog entries.

After working for a week the elaboration of documents using board I can say that is not the product of the most accomplished Microstrategy suite.It is difficult to make the documents of the board, giving the desired format and to easily understand the overall functioning of the documents. Also the documentation is not very clear, although there are examples of design in the sample repository, you can not run.

The main weaknesses identified are:

• Different ways of implementing the document. At each site, you can change one thing and not all the controls work in all modes (the widgets do not work in interactive mode and not all the graphics work in flash mode).This will limit the design possibilities and also complicates the process, it will be with this in mind all the time depending on the type of component to use and in what type of display is going to execute the document.
• Formatting settings: What it is formatted in a way not listed as otherwise. For example, in terms of fonts, graphics, tables, etc. Also, when you export documents (eg Excel), do not always get acceptable results.
• Slow Web interface design and editable mode.It is tedious to the positioning of the components within the grid and use the dropdown menus.
• You can not put limits on grid level indicators or chart (must be put in the original report). This sometimes can force us to build several reports to get subsets of data.

Regarding benefits, could indicate:

• Data sets from a single data set (included in the document through a predefined report), we can construct multiple views of data, as we have seen in the examples.
• Multiple items available for use in designs.They can be used to build visually attractive and reports (even very nice in Flash mode, even with its limitations).
• It leverages all the functionality of reports and OLAP navigation, allowing us to jointly run several reports and also reuse predefined elements.
• Documents can be run from the Office environment, embedded within the Microsoft suite. The export of documents via PDF, Excel, HTML or Flash can be a way to generate static content to send or to an intranet (within the limits specified format.) As an example, the flash generated in our sample board. Flash document obtained is of high quality and gives you many possibilities to make things interesting.

As a final curiosity, during the construction of Report Services documents, evaluation license expired Microstrategy 9 of 30 days I had provided Microstrategy.Again I licensing the Microstrategy Reporting Suite and take me to the pleasant surprise of the features seen are still active until now. Only on the number of licenses available to 2 (except for users of Web reporting and Intelligence Server, which are 25), limiting the use of 1 cpu (of 4 having my server). Besides, I had to uninstall the product Command Manager, Enterprise Manager, Object Manager, SDK and Web MMT to complete the registration. It evaluated theproduct (all included in the Reporting Suite) can be a very interesting option for businesses that begin in the BI world.