Tips & Tricks #1: How to filter more than one substring in the Find search box when selecting a long list of elements from a dynamic prompt list
There are cases in which a dynamic prompt is used to qualify attributes selected to be part of a report, but the attribute list is very long.
Attributes like ‘Street Address’ may contain keywords like ‘St’, ‘Ave’, ‘Dr’ that can be used to reduce the list of selected elements.
It is possible to filter the list using the find box and to include one or more substrings that can be logically ‘ORed’ (Will be included if any of the substrings is found) or logically ‘ANDed’ (Will be included if all substrings).
Pattern delimiters are ‘ ‘ (space), ‘%’ and ‘_ ‘
Space or blank = will be used for logical ‘OR’
‘%’ = Will be used for logical ‘AND’ usually in pairs (begin-end of string)
‘_’ = Will be used as wildcard in lieu of space (blank)
CASE 1: Filter all attributes that contains ‘z’ or ‘x’ strings:
In this example, all listed addresses contains string ‘z’ OR string ‘x’ in any position of the description.
CASE 2: Filter all attributes that contains first the string ‘Old’ AND then ‘Hwy’:
Observe that substring ‘Old’ and ‘Hwy’ can be part of other string and can start in any position. However, using %Hwy%%Old% will produce a different result.
In this example, this filter will produce an empty list as there is no address that have substring ‘Hwy’ before ‘Old’.
CASE 3: Filter all attributes that contains the substring ‘Old Hwy’:
Special character ‘_’ should be used to represent a single space. Cannot be duplicated for representing multiple space.
In this example, if the filter were ‘_Old_Hwy’, the last two elements will not be shown as they do not begin with spaces.
NOTE: Examples shown in MicroStrategy Desktop, but it apply also to the Web interface when using the find box in dynamic prompts.
I get a free pass to MicroStrategy World in Las Vegas which is the last week of this month. Last year, they gave us awards too. Not sure yet if they will do the same this year.
An Exploration of Tax Data
My dashboard is an exploration of tax data. It explores taxes rates for the top ten counties in terms of GDP.
I used horizontal stacked bar charts instead so that the viewer can visually see how social security and income tax rate add up to the total and explains visually why the countries are ordered the way they are on the dashboard. I also separated out $100K and $300K percentages into separate visuals.
In addition, I added the flags of the countries. Yes, I know, chart junk!
Now, you don’t see any numbers on the data points in this dashboard. The reason you don’t see them is because they appear when you mouse over a bar where you then see the country, category and the percent value as a tooltip.
Here is a screenshot of my entry. It was written with MicroStrategy v9.3.1, Report Services and the Visualization SDK.
Click on image to enlarge