Top 5 grid design hacks — Performance Indicator, Custom Label, Arrows in Grid, Metric Group, Currency Converter
Working with data is fun! Recently I found myself obsessed with MicroStrategy’s grid tool. A grid is an essential element of data analysis. Information is arranged in rows and columns, presented in a range of possible configurations from basic and straightforward to fairly complex. A grid visualization can be used as an intermediate tool to help understand a dataset, and can also be used to prepare data for display in other types of visualizations.
A grid can be more than numbers in a table. I enjoyed experimenting with the following elements to add visual interest to grids:
- Functions —These are powerful tools used in expressions to define MicroStrategy objects and initiate complex user-defined calculations.
- Advanced color threshold — The Advanced Threshold Editor displays a list of all thresholds currently defined for a visualization. These can be used to enhance a grid’s ability to convey richer meaning
- Custom number format syntax — If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box.
This article will help you learn to use these elements to create more powerful and unique grids.
All right, let’s get started!
Performance Indicator
Imagine you are presenting a dataset containing ‘sales’ and ‘sales goal’: wouldn’t it be great to display progress toward a specific objective through a percentage bar displayed directly in the grid? Similar visual presentations can be achieved with a microchart, but using this design hack will provide you with more control over the look and feel.
To prepare the element for the bar indicator, you need to first create a derived metric with Case() and Trunc() functions:
Case((Trunc((([TOTAL SALES]/[SALES GOAL])*10))>10), "10", Trunc((([TOTAL SALES]/[SALES GOAL])*10)))
This is to ensure all bar indicators are the same length (10 blocks).
Next, create another derived metric to display the bar by utilizing Concat() and RepeatStr() :
Concat(RepeatStr("■", [Truncated %]), RepeatStr("□", (10-ToNumber([Truncated %]))))
You will be able to find more unique codes from this webpage.
The final step is to apply advanced thresholds to the metric based on the conditions for individual color formatting.
Custom Label
Labels or Tags are types of classification components that help to categorize elements. Creating a custom label on the grid requires a basic understanding of HTML. Ideally you can design an attribute/metric label in any style; in this example we are making a basic capsule style label.
First, let’s create a dummy dataset with an attribute like this:
<span style="color:#fff;border-radius: .4em;font-weight:bold;background-color: #000;padding: 0.2em 0.6em">
You can then define the attribute data type to HTML tag, then combine the HTML with attributes by:
- Returning the attribute string in metric form with Max(). e.g.:
Max(Label){~+} - Connecting the metric to an html metric with Concat(). Set conditions with Case(). e.g.:
Case(([_(Service)]="Internet"), Concat(label_black, [_(Service)]), ([_(Service)]="Phone"), Concat(label_grey, [_(Service)]), "--")
You can also take the advantage of the Replace() function to quickly create more labels in different colors and apply them to the other attributes.
Arrows in Grid
The up/down arrows conveniently provide insights at a glance. Whether it is to display the changes between two metrics, to show the KPI performance over time, or to present ranking among employees, these simple arrows can tell strong stories. Additionally, the custom number formatting and advanced threshold editor allow us to use colors and symbols to create a richer grid presentation.
- Custom number format on a metric to display arrows:
[<-0.01]- #,##0%" ▼";[<=0]-#,##0%"";[>0]+#,##0%" ▲" - Advanced Threshold editor:
“When value is greater than 0, …”, “When value is less than 0, …”, “When value is equal to 0, …”,
More of formatting syntax can be found in Custom number formatting examples.
Metric Group
Complex grid can be very messy. Sometimes you want to make metrics more organized with modern grid. A modern grid is a visualization that allows you to analyze different aspects of data across a common dimension. By adding simple dummy attributes you are able to arrange the columns in a much more tidy form without even touching the original data.
In this case, you would create a dummy derived attribute. e.g.:
ToString("Coal")
Then add this attribute to the compound grid Columns group:
You can find settings under the FORMAT panel, such as Merge repetitive cells or Middle alignment, to fine tune the grid style. (special thanks to Suresh for the tips.)
Currency Converter
Finally, I would like to show how easy it is to build a simple currency converter with a grid and selector. The currency converter calculator will convert the money based on current rates from around the world.
First, you will create a dataset in a similar format :
Next, create a derived metric like this:
Profit*rate
You can then apply an Element / value selector to the grid as a drop-down.
Alternatively, if you are trying to add currency symbols to the metric, use multiple derived metrics with individual number formatting and A/M selector instead.
Conclusion and reference
These are just a few ideas to visually and functionally enhance your grid visualizations. Who says big data has to be boring? By mastering derived attribute/metric formulas, custom number syntax formats, and advanced thresholds, you can design a more dynamic dossier just with simple grids.
The techniques were inspired by the following online articles :
Solution for showing Bar Charts and % Change Arrows inside Grids
DOSSIER GRID STYLING TIPS — MSTR DOSSIER
MicroStrategy Functions Reference Help
Dossier sample : https://www.dropbox.com/s/7evafnqow1h3f3m/modern%20grid.mstr?dl=0
