Monday, December 16, 2013

Only 2 Jobs Running at a time

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:

  1. Right click on project
  2. Go to Project Configuration
  3. Go to Database Instances > Select your DB provider
  4. Select Database Instance and click Modify button
  5. Go to Job Prioritization tab
  6. 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.

Wednesday, November 27, 2013

Setting First Day of Week in Prompt Calendar display

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 Format didn't fix anything.

In summary, the issue still remains an issue. Please comment if you have suggestions to solve.

Think - calendar.js java script.

Wednesday, October 23, 2013

Quick Tip - Display Project Status on project's home page

Have you wanted to do this?


Very easy to so  with version 9.x. Accessible from Project Configuration > Project Definition > Communications.



Tuesday, October 15, 2013

Quick Tip - Showing a metric as hundreds or thousands

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.

Monday, August 5, 2013

Issue with Row Height while Exporting from MicroStrategy Document

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!

Friday, August 2, 2013

Fact does not exist at a level that can support the requested analysis

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 familiar with MSTR and object development.

1. Make a backup of your report
2. Remove all other metrics from the report template except for the metric in question - in my case Visitor Count
3. Run the report. Problem fixed? No, go to step 4. Don't forget to add these metrics back to the report template once the problem is fixed. If Yes, one of those metrics was the problem maker.
4. Remove the attributes mentioned in the error from report template - in my case [Visitor ID] and [Visitor Center Name and ID]. Does this fix the problem (keeping in mind you did remove the two attributes that are needed on your report)? Yes? Well, those attributes were your problem.
5. Check the relationships between those attributes - that is where the source of your troubles is.
6. Don't forget to add the other metrics back on the report, if you removed them.

Hope this helps! If you have more specific questions feel free to ask in the comments.

Thursday, August 1, 2013

Problems With Conditional Metric and Drill Down

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? Wrrrrongg!

I had been scratching my head for about 2 hours until somebody pointed out to me to check the Advanced property for Metric Condition. Voila - that's where the issue was; a checkbox that should be unchecked was still checked!


Metric Editor - Condition
Metric Condition > Advanced Options
Uncheck the above checkbox "Remove related report filter elements" and your problem will be fixed.

Now, there is another way to work this out as well. Create a Custom Group with the filters defined within the group definition. Also create a simple metric which is Count of Visitors without any kind of filters. You should be able to use this without any problems as well. Just remember, Custom Groups have more limited functionality than actual Metric.