VLookup kind of Dynamic Custom Group in Dashboard/Dossier

Custom groups enable users to view specific elements based on a metric or specification. For example, you may want to see all the customers in a retail store that bring in over $1,000k in revenue per month, or a teach may want to only view data on students who scored over 2000 on their SATs.

The Scenario

Let's assume you have a table that clusters your Employees by the Cost brackets. Based on those brackets a Rating is defined (like A, B, C...). This table can be imported from Excel file or from a table in relational database. You will notice there is a flag column that contains value '1' for each row. If you don't have this column in your data don't worry! Just use the Upper column instead in the formula (see formula of 'Rating Metric' below). Continue on with the process.

User-added image

Methods for creating a custom group

Currently, your Dashboard or Dossier displays Cost per Employee, but it is missing Rating information. Continue on through the numbered steps that are labeled with images to help you along the way.

The images will act as a guide through the process.

User-added image

As a first step import your Rating table.

Import Rating as an attribute.

Create a metric 'Rating Metric' with the following formula:

Max(IF(((Cost>Lower)And(Cost<Upper)),Flag,0)){~+}

User-added image

Create a grid that contains Employee, Rating (attribute) and Cost.
The data comes from two unrelated data sets so you will get a cross join of data.

Add "Rating Metric" to Filter Pane and set a filter 'Greater than or Equal to" 1. You will see a proper Rating assignment and you don't even need to display 'Rating Metric" on the grid.

Final Report

User-added image

Note: This assignment is dynamic, meaning that if you change the Rating brackets in your data set the new Rating rules will be applied automatically (assuming, you are using a live connection to a database).

You may need to change Level of your metrics if there are more attributes in your initial data set.

Comments

Microstrategy Custom number formatting symbols

Custom number formatting symbols 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. Select Custom as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the Project Design Guide . ...

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps. Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI. Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for where 2 is Category ID for "Books" Report will display result as below. Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as Max(Category) {~ } and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this Case((Books = 1), Revenue, 0). Report result would now display as expected as shown below

Microstrategy Dossiers explained

Microstrategy Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has evolved dashboards to the point that they are more than dashboards - they are interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so the've renamed VI dashboards to ‘ dossiers ’. Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

Update the data on an Intelligent Cube without having to republish the entire cube in MicroStrategy

Update the data on an Intelligent Cube without having to republish the entire cube in MicroStrategy MicroStrategy has introduced a feature known as, Incremental Refresh Options, which allow Intelligent Cubes to be updated based on one or more attributes, by setting up incremental refresh settings to update the Intelligent Cube with only new data. This can reduce the time and system resources necessary to update the Intelligent Cube periodically versus a full republish. For example, if a user has an Intelligent Cube that contains weekly sales data, the user may want this Intelligent Cube to be updated at the end of every week with the sales data for that week. By setting up incremental refresh settings, he can make it so that only data for one week is added to the Intelligent Cube, without affecting the existing data and without having to reload all existing data. Users can select t...

Types of prompts in Microstrategy

Types of prompts in Microstrategy The different types of prompts allow you to create a prompt for nearly every part of a report. Prompts can be used in many objects including reports, filters, metrics, and custom groups, but all prompts require user interaction when the report is executed. The correct prompt type to create depends on what report objects you want users to be able to base a filter on to filter data, as described in the list below. Filter definition prompts allow users to determine how the report's data is filtered, based on one of the following objects: Attributes in a hierarchy : Users can select prompt answers from one or more attribute elements from one or more attributes. The attribute elements that they select are used to filter data displayed on the report. This prompt lets you give users the largest number of attribute elements to choose from when they answer the prompt to define their filtering criteria. For example, on a repor...

Prompt-in-prompt(Nested Prompts) in Microstrategy

Prompt-in-prompt(Nested Prompts) in Microstrategy Nested prompts allows you to create one prompt based on the other and other bases on another, nested prompts allows us to prompt the highest level(Like year) to middle level(like Quarter, then to the low level(like Month). Here you can see how to create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter. Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts . The following procedure describes how to achieve this: Create the highest level filter. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute "Year." Click "prompt on attribute element list" and click "Next" through the rest of the screens to accept the default values. Do not set any additio...

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

Custom Tooltips in Microstrategy developer and Web

Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...

Clean uninstall MicroStrategy Analytics Enterprise using the Uninstallation Cleanup Utility

How to clean uninstall MicroStrategy Analytics Enterprise using the Uninstallation Cleanup Utility CONSIDERATIONS/GUIDELINES : This Uninstallation Cleanup Utility is designed to remove the obsolete or leftover services, files and registries after uninstalling MicroStrategy products. The goal of this cleanup utility is to remove the leftover files and registries after uninstallation to make the machine "cleaner" for a second time installation (it can solve some known downgrade installation issues). The basic assumption for removing files is that the user installed MicroStrategy in a path that contains the string "MicroStrategy" . If not, the files and certain registry keys will not be removed. Using the cleanup utility incorrectly can cause system-wide problems that may require re-installation of the Operating System. This utility should always be tested in a test environment before being run in a production environment. Lin...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Microstrategy Analytics

Search This Blog