Reporting has gone beyond grid and graph to more much more higher level where Business wants more flexible and easy approach to view their data. Widgets and Visualizations play a crucial role in not only for jazzy looks but also to quickly view data in an organized manner.
Tree visualization is an out of box feature in Microstrategy, which is described in the below section.
Let’s start our discussion assuming there is no Tree Visualization in Microstrategy and the steps involved in the fulfilling the requirement without Tree Visualization.
Consider aRegion Hierarchy with Region, Country and State attributes. Business wants to view the sales across State based on the Region and Country selections.
The normal approach to fulfill this by creating 2 selectors and target them to report (Day vs Sales)
Now we will see to implement the same requirement using Tree Visualization.
- 1. Create a report having hierarchical elements (need not belong to any user defined hierarchy).
- 2. Place the report on a Document and right click on the grid – Properties and Formatting -> Widget -> Microcharts. We can select the required mode of execution here ,DHTML/Flash/iPad
- 3. Execute the dashboard in the mode selected in above step.
- 4. Right click on the widget and choose properties and uncheck below setting
- a. Sparkline – Show Sparkline graph
- b. Bar – Show Bar graph
- c. Bullet – Show Bullet graph
The choose mode from the dropdown and select Tree Display. With these changes a Tree Visualization is creation.
Steps to make Tree Visualization as a selector
- 1. Create a report with lowest level attribute i.e, State and add sales metric.
- 2. Edit the dashboard in design mode and add the above dataset.
- 3. Right click on Tree Visualization report and click on “Use as Selector” and choose the data set in step 1 as target.
Tip #1: Get all the Months in Year irrespective of data availability
Requirement: Business want to see ‘0’ values for the future months in the current month. But the fact table has data till current month.
Normal approach to resolve this requirement
- 1. Insert only dummy record in the fact table for each of the future month.
- 2.Tweak MSTR VLDB settings to get a left join with Dimension table.
But above approaches are not feasible at most of the times because of either privileges or complex joins involved in the report.
- 1. Create a transformation metric using Last Year transformation.
- 2. Use the normal metric and transformation metric in the report and make join type as “Outer”
Data -> Report Data Options -> Metric Join -> Outer.
Since there will be record for the previous year, MSTR shows ‘0’ values for normal metric and metric value for transformation metric