SQL, VB, MicroStrategy & Other Topics

If you are coming across the problem with Conditional metric and Drill Down, you will want to kick somebody's behind after reading the solution(s)! We have a hierarchy defined as Region - Country - State - City - Zipcode - Branch. For each Branch there is a count of Vistors. The report starts with simply Region and Count of Visitors. You should be able to drill down all the way to Branch from Region. Count of Visitors is a conditional metric based on certain filters. So, the requirements sounds pretty simple, right? Yes, and it is simple. But for some reason when I built out the report in less then 5 minutes the drill down would not work. Clicking Americas to drill down and expecting to see Canada, USA and Mexico, I was seeing all European and Asian countries as well. I thought there was something major  was wrong with relationships or data or even the ETL. This is a pretty simple report so obviously the issue has to be a major one for this report to not work, right? Right? Wr...

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 + R2C...

Creating Dynamic Filter Based on Tab Selection Use Report Studio to get the following end result. (Yesterday) (Month to Date) (Life to Date)   Create a Query Create a new query to be used solely to hold the tab values. Call it “TimeFrame Query” and create data items for every time frame that you’d like to appear. The expression definition should be a string encapsulated in single quotes. Create a String Variable Next let’s go ahead and create a string variable called “Tab Condition”. Define the expression as shown below: if (paramvalue('paramTabChoice') is null) then ('LTD') else (paramvalue('paramTabChoice')) This expression will set a default tab value for the report. Next, we will need to create a string value to correspond to every data item we created in the query. Note: The values of this variable must exactly match the expression definition of the data items we created. It is not necessary to use single quotes when ...