SQL, VB, MicroStrategy & Other Topics

Here is a pretty simple request from my client - " Our work week starts on Monday and ends on Sunday. Can you make sure that the calendar in date prompt also starts from Monday? " Me - "Sure why not, MSTR is pretty flexible". I was so wrong! There is no [easy] way to do this in MicroStrategy. One solution that 'kind of' worked for me: 1. In preferences, change the locale for date and numbers to European. This will change the display to show Monday first. Issue: Date format changes to DD/MM/YYYY and here in the US we need MM/DD/YYYY. 2. To address the 'side effect', based on TN 12819 ( https://resource.microstrategy.com/Support/MainSearch.aspx?tnkey=12819&formatted=1 ) I made appropriate changes to format_config.xml. This now changed the date format to US format when selected from Calendar Date prompt. But default values still showed up in European format. Furthermore, within the report the dates showed up as YYYY-MMM-DD and using Advanced ...

Have you wanted to do this? Very easy to so  with version 9.x. Accessible from Project Configuration > Project Definition > Communications.

Ever come across a situation where you need more real estate on the chart but those large dollar amounts on your Y axis running in to hundreds of millions just take up all of the white space? Or simply make your grids too wide? Solution is very simple! Go to the metric number formatting, select Fixed  number format and change Abbreviation value to desired format - thousands, millions  or billions . Another option is to select Custom  number format and enter this formula:   #,##0,.00"K";(#,##0,.00)"K" Once this formula is working fine, modify it to suit your needs.

See the screenshot below. Is that the issue you are facing? Incorrect Row Height when exported Instead you want it like this...? Expected Layout of Row Header If so, it is a pretty simple change. Within document design, select the document part where the grid is located (eg Detail Header etc). Go to Format > Properties (MSTR 9.x) Select Layout tab Under Excel section, select the checkbox  that says 'Automatically fit rows' Problem fixed. Please excuse the absence of data in screenshots for obvious reasons!

Error: SQLEngine got an Exception from DFC: [DFCENGINE] Engine Logic: Fact does not exist at a level that can support the requested analysis. Fact: "Visitor Count". Level: "Visitor ID, Visitor Center Name and ID". Error in Process method of Component: SQLEngineServer, Project VUE Reporting, Job 43595, Error Code= -2147212800. If you have worked with MicroStrategy, you have definitely come across this error at least once! First things first - did you recently change any attributes? If you did, did you update the schema? If no, then that is most likely your problem. Update the schema from MSTR Desktop > Schema > Update Schema or Ctrl+U. If that does not fix your problem, I may not be able to give you an exact answer but can guide you through troubleshooting the problem. Below, I will use my error mentioned above related to Visitors to guide you through troubleshooting. The steps mentioned below may be a little long for those of you who are already famil...

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

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