Skip to main content

MicroStrategy - Understanding Level Metric

Many people have hard time understanding the Level Metrics in MicroStrategy - including me. Here is a quick cheat sheet I put together for reference.

Consider the following data layout: Country, Regions, Call Centers
Geographical Hierarchy: Country --< Regions --< Call Centers
Total 6 call centers: Region1 (R1C1, R1C2), Region2 (R2C3, R2C4), Region3 (R3C5, R3C6)
Report Filter: Call Center in (R1C1, R2C3, R2C4)


'The Cheat Sheet'
TARGET
ATTRIBUTE
FILTERING
GROUPING
Revenue Summary Outcome
Region
STANDARD
STANDARD
Apply Report Filter to Call Center output on report and SUM all Revenue for Call Centers displayed on report and Group By Region
Region1: R1C1
Region2: R2C3 + R2C4
Region
STANDARD
NONE
Apply Report Filter to Call Center output on report and SUM all Revenue for Call Centers displayed on report and do NOT Group By
Region1: R1C1 + R2C3 + R2C4
Region2: R1C1 + R2C3 + R2C4
Region
ABSOLUTE
STANDARD
Apply Report Filter to Call Center output on report, SUM all Revenue within each Region while ignoring the Filter
Region1: R1C1 + R1C2
Region2: R2C3 + R2C4
Region
ABSOLUTE
NONE
Apply Report Filter to Call Center output on report, SUM all Revenue for the Regions on the report while ignoring the Filter, do not break by Region
Region1: R1C1 + R1C2 + R2C3 + R2C4
Region2: R1C1 + R1C2 + R2C3 + R2C4
Region
IGNORE
STANDARD
Apply Report Filter to Call Center output on report, SUM all Revenue for each Call Center and Group By Region
Region1: R1C1 + R1C2
Region2: R2C3 + R2C4
Region
IGNORE
NONE
Apply Report Filter to Call Center output on report, SUM all Revenue for entire Country, ignore filters and group by
Region1: R1C1 + R1C2 + R2C3 + R2C4 + R3C5 + R3C6
Region2: R1C1 + R1C2 + R2C3 + R2C4 + R3C5 + R3C6
  

What does it mean when 'Target' is set to 'Report Level'?
Presence of the default Report Level target on a metric tells the MicroStrategy engine to group by all the attribute IDs found on the template.
By removing the Report Level target from the metric and selecting None for the grouping of any other available target attribute, MicroStrategy Engine understands that there should not be a Group By clause in the SQL that calculates the metric. You can use any attribute for this purpose. You do not need to add more than one attribute unless specific filtering behavior is required for the metric.

If specific filtering behavior is required, you need to add other target attributes, but you should always select None grouping.

There is a lot more to Level Metrics than this and easily understood when combined with examples.


Labels:

Comments

Anonymous said…
nice explanation
thanks
September 23, 2014 at 3:18 AM
Anonymous said…
It is not an easy work to collect the information of all the branches and make the sum of the revenues but essay writing technique is the one which has been made wuite easy for the students by the authorities.
April 25, 2016 at 1:06 PM
samala swathi said…
Well it was nice post and very helpful information onMicrostrategy Online Training Hyderabad
August 8, 2017 at 11:57 PM
Jessica Ellis said…
Presence of the default Report Level target on a metric tells the MicroStrategy engine to group by all the attribute IDs found on the template. The same situation is about translation services reviewed in the article above. By removing the Report Level target from the metric and selecting None for the grouping of any other available target attribute, MicroStrategy Engine understands that there should not be a Group By clause in the SQL that calculates the metric.
January 16, 2018 at 4:51 AM
Cara H said…
Interesting thoughtss
November 27, 2021 at 11:09 PM
Post a Comment

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...
25 comments
Read more

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...
5 comments
Read more

cannot create a column accessor for OLE DB provider "ibmdasql" for linked server

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.
Post a Comment
Read more