SQL, VB, MicroStrategy & Other Topics

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

Behavior : Within MicroStrategy 9.x in Web environment, Hierarchy prompts time out and throw 'Request timed out' error. Solution : There are two issues to address here: 1. See the underlying query for prompts and make sure it is well optimized. A prompt should not take more a few seconds to load. Underlying SQL query can be obtained from Jobs monitor or a tool like SQL Profiler. 2. If all is well under the hood and no further optimization is possible, increase the request time out. This could be achieved by changing the value for "timeout" property in FrameProcessor.xml file on the web server. This file is generally found under C:\Program Files (x86)\MicroStrategy\Web ASPx\WEB-INF\xml\blocks. Remember, the value represents time out in milliseconds. <!DOCTYPE block SYSTEM "../dtds/blockLibrary.dtd"> <!-- HTTP Processors --> <block name="FrameProcessor" inherits="BaseModel"> <definition...

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