SQL, VB, MicroStrategy & Other Topics

Here is a function that will return first day of the current quarter. This can be used within MicroStrategy via ApplySimple functionality as pass through function. CREATE FUNCTION dbo.udf_GetFirstDayOfCurrentQuarter (@getdate as datetime ) RETURNS DateTime AS BEGIN RETURN CONVERT ( DATETIME , convert ( varchar (2), case ( month (@getdate)%3) WHEN 0 THEN ((( month (@getdate)/3) - 1) * 3) + 1 ELSE ( month (@getdate) - ( month (@getdate)%3)) + 1 END ) + '/1/' + convert ( Varchar (4), YEAR (@getdate)) ) END

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 + R

Here is a simple MicroStrategy formula to derive first day of quarter based on current date: MonthStartDate(AddMonths(CurrentDateTime(),(((Month(CurrentDateTime()) - 1) - ((Quarter(CurrentDateTime()) - 1) * 3)) * -1))) I am sure there must be easier ways than this. Comment with your versions of the formula.

One of my clients came up with a request - We need to create a report that prompts the user for a date and a second prompt that prompts whether the user would like to see MTD or YTD metric based on the answer to the date prompt. Really simple solution! If you haven't gotten in to this mode of thinking then do so now - Divide and Conquer. There are two separate requests here: Date Prompt Object Prompt Create a report (MicroStrategy Tutorial) with Year, Month and Day in the template. Define a report filter - Day. Easy Peasy! This step assumes you have already created n  metrics that you would like to offer in the prompt selection - in this example Revenue, Revenue - MTD and Revenue - YTD. Next, create a new prompt using Prompt Wizard. Make it an Object Prompt and offer the above mentioned three metrics as objects available for selection. Save and close the prompt wizard. Simply drag and drop the prompt to report template area (not report filter), save and execute.

Using MicroStrategy, you can produce some really nice dashboards that are easy to read and get straight to the point. Anybody who looks at a lot of charts ( like this site ) immediately knows when they see some extra - unwanted labels on the chart. Those are some major distractions. Look at the Before and After charts below. With the chart legend, Metric name kept on showing up. If a chart was regarding your customer demographics - gender and age, it would be so annoying when you keep on seeing Male Age , Female Age , Working Male Age , Working Female Age - you get the idea. You already know it is a chart displaying ages, so no need to keep on hammering your audience with the world 'age'. Below screenshot shows you easy the cause for such behavior of MicroStrategy and the fix for the same. Fix can be applied in MicroSrategy Desktop design view. Right-click on the chart > Graph Option > Options > select the checkbox "Supress last level labels".