World 2024 | Where AI and BI Converge | Apr 29 - May 2 | Las Vegas | Register Today

Header
BitcoinLightning
Hero Background Image

Intelligence Everywhere

The MicroStrategy Blog

Top 5 grid design hacks — Performance Indicator, Custom Label, Arrows in Grid, Metric Group, Currency Converter

Photo of Keng Fu Chu

Keng Fu Chu

March 16, 2022

Share:

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.

blog-post_grid-design-hacks_lead_image.webp

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!

1. Performance Indicator

blog-post_grid-design-hacks_performance.webp

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.

blog-post_grid-design-hacks_threshholds.webp

2. Custom Label

blog-post_grid-design-hacks_call-center-case-details.webp

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:

  1. Returning the attribute string in metric form with Max(). e.g.:

Max(Label){~+}

  1. 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.

blog-post_grid-design-hacks_call-center-case-details-2.webp

3. Arrows in Grid

blog-post_grid-design-hacks_arrow-grid-1.webp

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, …”,
blog-post_grid-design-hacks_arrow-grid-2.webp

More of formatting syntax can be found in Custom number formatting examples.

4. Metric Group

blog-post_grid-design-hacks_metric-group-1.webp

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:

blog-post_grid-design-hacks_metric-group-2.webp

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.)

5. Currency Converter

blog-post_grid-design-hacks_currency-converter-1.webp

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 :

blog-post_grid-design-hacks_currency-converter-2.webp

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


Thought Leadership

Share:

Photo of Keng Fu Chu
Keng Fu Chu

Principal Design Consultant in BI, Data, and UX principles / Weekend Artist


Related posts

Video: AI+BI: The perfect love story
AI+BI: The perfect love story

A captivating love story unfolds between Artificial Intelligence (AI) and Business Intelligence (BI) in technology and business. Like any great romance, it’s a tale of complementary strengths, mutual enhancement, and transformative potential. This story is not just about the synergy between two technologies but about how they come together to revolutionize how we understand data, make decisions, and envision the future of business.

Photo of Catherine Farley

Catherine Farley

February 14, 2024

Video: Five Ways to Elevate Analytics with Generative AI
Five Ways to Elevate Analytics with Generative AI

Learn the five core improvements of how to impactfully interact with data through the combination of AI and business intelligence, brought to you by Ventana Research.

Photo of Brett Sheppard

Brett Sheppard

March 13, 2024

Video: Happy 1st Birthday, Education +!
Happy 1st Birthday, Education +!

As we blow out the candles on Education +’s first birthday cake, we’re excited about the journey ahead. We’re committed to continue providing high-quality, relevant, and engaging learning experiences for our students. As with all new parents, we’ve documented some key development milestones.

Photo of Lauren O’Connor

Lauren O’Connor

October 11, 2023

Video: 5 Reasons You Don’t Want to Miss MicroStrategy World 2024
5 Reasons You Don’t Want to Miss MicroStrategy World 2024

Find out the top 5 reasons to attend MicroStrategy World 2024, an event where AI and BI converge. Set in Las Vegas from April 29 to May 2, 2024, it’s an ideal destination for data professionals, tech leaders, and decision-makers.

Photo of The MicroStrategy Team

The MicroStrategy Team

January 22, 2024

Video: Embracing the Age of Data with Genesys: Transforming Contact Center Solutions Through Embedded Analytics
Embracing the Age of Data with Genesys: Transforming Contact Center Solutions Through Embedded Analytics

Genesys, a global leader in contact center solutions, stands at the forefront of a new era of cloud computing, reshaping its approach with a focus on embedded analytics.

Photo of Catherine Farley

Catherine Farley

January 25, 2024

Endless Possibilities. One Platform

Background Image