Tagging Metric Formulas in Tooltips

A handy feature in MicroStrategy Web is the ability to show the description of an object as a Tooltip when you hover over it.  This can be a really useful way to get extra information on Metric in a report.  For example, you may add text to your Profit metric to say “Revenue – Cost” so that a user knows what the formula was.  I think most people do this at the beginning of a project since the information is still kind of new to everyone, and there are few objects.  But as time goes on, the project grows in complexity.  Also, as Developers get more comfortable with the terminology, it seems that Metric Descriptions stop getting populated as frequently.  Today I’ll show a handy way to maintain this helpful information or set them up in bulk for the first time if you’re late to the party.

The specific feature I’m talking about is the Description property of a Metric.  If you’re not familiar with it, you can right click a Metric, select Properties, and then type something in the Description field.  If you now view a Report in Web that uses that Metric, you’ll see your text in the Tooltip.  But who wants to maintain all of that documentation in every single Metric object?

Fortunately, MicroStrategy provides a product called Command Manager that lets you automate lots of repetitive tasks.  As of v9, an additional Java-Like layer was added to give you even more power in generating and executing these tasks.  Below is a script I’ve developed that will take the Formula of a Metric and put it’s text in the Description, automatically tagging every Metric in your project with useful Tooltip information.

DISCLAIMER:  Be sure to test Command Manager scripts in a Test environment in case the results are not what you expect.  There is no undo!

   //list all metrics in the project  
   String sProjectName = “PROJECT NAME”;
    ResultSet oMetrics = executeCapture(“LIST ALL METRICS FOR PROJECT ‘” + sProjectName + “‘;”);  
    oMetrics.moveFirst();    
    while (!oMetrics.isEof() )
    {
      //get name and path of this metric to list properties
      String sMetricName = oMetrics.getFieldValueString(DisplayPropertyEnum.NAME);              
       String sMetricFolder = oMetrics.getFieldValueString(DisplayPropertyEnum.PATH);
      //get properties of each metric
      ResultSet oMetricProps=executeCapture(“LIST ALL PROPERTIES FOR METRIC “” + sMetricName + “” IN FOLDER “” + sMetricFolder +”” FOR PROJECT “” + sProjectName+ “”;”);
        oMetricProps.moveFirst();
       //extract the formula
        String sMetricFormula = oMetricProps.getFieldValueString(DisplayPropertyEnum.FORMULA);
       //build the Alter statement
        String sQuery = “ALTER METRIC “” + sMetricName + “” IN FOLDER “” + sMetricFolder + “” DESCRIPTION “” + sMetricFormula + “” FOR PROJECT “” + sProjectName + “”;”;
        //execute it
        printOut(sQuery);
//      execute(sQuery);    
         oMetrics.moveNext();
     }

To execute the above code, you’ll need to open Command Manager and then go to New -> New Procedure.  Be sure to click on the Test Information tab to the right of the window and connect to your Project Source.  Now simply paste this code and execute it.  By default here, I’ve commented out the actual execute() command at the end, and instead it just prints the command so you can see what it’s doing.

At a high level, we first get a list of all Metrics in the Project, and then iterate through that list to get the Metric Properties of each.  For each one that we grab, we extract the Metric’s Formula, and then issue an update to the Metric to set it’s Description equal to the Formula.

For a Compound Metric like the one in my initial example, the Description will come out like “Revenue – Cost”.  But for a standard metric like Revenue, it would come out as the Fact and Aggregation function, “Sum(rev)”.  If your database has simple or silly names, and you haven’t renamed your Facts, then you may end up with something a little less than helpful like that.  Also, if you have a Metric that uses the ApplyAgg() function, that syntax with SQL will be shown.  You may also have Metrics where you really do what some alternate Description text, such as “This Metric’s data is updated every Tuesday” that is more valuable to you than the formula itself.  In those cases, you could use the printOut() function at the end instead of execute() so that you can copy/paste the list of commands into a Command Manager Script and execute only the ones you want.

You may also like...