SQL, VB, MicroStrategy & Other Topics

I noticed while executing a document with four underlying reports that only 2 reports were executing at a time. There was no reason that the server cannot handle more than 2 jobs at a time. Think of this scenario - there are 50 users in your organization who need to run report. If only 2 jobs execute at a time on iserver, it while be a while before all users get their data! Here's how to change that: Right click on project Go to Project Configuration Go to Database Instances > Select your DB provider Select Database Instance and click Modify button Go to Job Prioritization tab On the bottom change the number of simultaneous jobs that can be executed Consult your system administrator for any 'side effects'. I work with 9.3 version.

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

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

To fill up my database with test data, I needed to generate dummy text, phone numbers, social security numbers etc. There are data generators out there, but i wanted to write my own, so here it is... ALTER FUNCTION dbo . fn_WORD_JUMBLER (       @Text VARCHAR ( MAX ) = '' ,       @DataType INT = 0 , -- 0 = random text, 1 = random number       @DataLenth INT = 10     ) RETURNS VARCHAR ( MAX ) AS BEGIN /* USAGE: SELECT dbo.fn_word_jumbler('ankeet Is Awesome!', DEFAULT, DEFAULT) SELECT dbo.fn_word_jumbler('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', DEFAULT, DEFAULT) -- ALPHABETS SELECT dbo.fn_word_jumbler(DEFAULT, 0, DEFAULT) -- NUMBERS SELECT dbo.fn_word_jumbler(DEFAULT, 1, DEFAULT) ============================================================================= Created By: Ankeet Shah Purpose: Accepts an input and Returns the same text in reorganized/jumbled/randomized order. Cannot 'de-randomize'. Thi

When I take a break from work, I pretty much just find a personal project to get busy with! This time it was the Network Widget in MicroStrategy 9.3.0 that got me busy. I have been working on my family tree using Legacy Genealogy software. Being of the Indian (East) ancestry, I wanted to represent the relationships using terms from Indian languages. For example, mother's brother in English is Uncle  but in Gujarati/Hindi is Mama . Father's brother in English is Uncle  but in Gujarati/Hindi is Kaka  and Chacha  respectively. I wanted to make a 'dashboard' of my own that would represent these terms and allow me to filter by family members and individuals. So I started with a database. Since this is just a proof of concept (to myself), I decided to keep the design pretty simple. Database Design The design comprises simply of 3 tables. First and foremost are two 'primary' tables - tblRelationships and tblFamilyMembers. tblRelationships defines relationship nam

Recently I ran across the following error while trying to run a report. My "mini" data warehouse is stored in Microsoft Access. Report: Transactions for Heatmap Job: 836 Status: Execution failed Error: SQL Generation Complete QueryEngine encountered error: Execute Query failed.  Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42000:-1809: on SQLHANDLE] [Microsoft][ODBC Microsoft Access Driver] Cannot modify the design of table 'TTLOR2BS4MD000'. It is in a read-only database . Connection String: DSN=AnkeetFinancials;DBQ=C:\Users\ashah\Documents\AnkeetPersonalTransactions\FinancialTransactions.accdb;DRIVERID=25;FIL=MS Access;MAXBUFFERSIZE=2048;PAGETIMEOUT=5;UID=admin;. SQL Statement: create table TTLOR2BS4MD000 ( Transaction_Category TEXT(255),  Statement_End_Date TIMESTAMP,  Account_ID LONG,  WJXBFS1 LONG,  WJXBFS2 DOUBLE) ..  QueryEngine encountered error: Report [Transactions for Heatmap]: Drop table error: DBInstance = A

Here is more comprehensive blog regarding silent installation.  Ignore/Excuse the document format as it is a copy/paste from Microsoft Word. 1.                Required Components 1.        Setup.exe along with supporting installation files for MicroStrategy product 2.        Response.ini 3.        Setup.iss – file containing responses to required input by InstallShield during installation 4.        Batch File - .bat file containing the command to be executed 1.1.          Setup.exe Setup.exe is shipped with other MicroStrategy installation files. Usually downloaded as a zip file and can be unzipped to any location. For purposes of Silent Install, this file could be located locally or on a network drive. 1.2.          Response.ini Response.ini file contains responses to input requested by MicroStrategy during installation. Response.ini can be used to install/uninstall MicroStrategy, apply hotfixes as well as to create Project Sources. Although the file cou