Creating a Better Create Report Template
There’s nothing more satisfying that empowering a user. In addition to the old adage, “… Teach a man to fish and you feed him for a lifetime”, the same is true when you can give a savvy Business User the ability to answer their own questions. When a BI project is initially rolling out, there are always some analysts that are a little uneasy and feel like they are being replaced, despite your best efforts to convince them that this is just a better tool for them to do their jobs where they will spend more time analyzing and less time performing Excel gymnastics. The same assurance is true for us, where by enabling the users to directly perform more analytics without our direct involvement allows us to focus more on platform administration and pushing the envelope than building the same remedial reports over and over on demand. The adhoc report tools available for the masses out of the box leave a lot to be desired, so today I’ll show you how to easily build your own template that users can use as a launch pad to on demand analysis.
The most common method of adhoc analysis would be to drill on an existing report. Unfortunately, if the user doesn’t have a Web Professional license (which allows them to access the All Objects tab as well as Design Mode), then drilling can only take them so far. For one, they can only drill to Attributes you’ve defined in the Drill Hierarchy, and they can’t add more metrics to the report unless you’ve defined templates to drill to. This requirement of precognitive drill design defeats the intention of on the fly analysis.
Existing Create Report Templates
For the rest of the users, MicroStrategy offers two out of the box templates for creating reports which are available from the Create Report link across the top of MicroStrategy Web. They come in two flavors: Report Wizard and Report Builder.
Report Builder is basically a template that contains object prompts for each of the primary areas: Attributes, Metrics and then a Hierarchy Qualification and Metric Qualification prompt to build your own filter. To make it worse, these prompts start off in the root folder, which means to even get to Attributes or Metrics, you have to browse down 3 or 4 levels of folders (which a business user is not going to be able to navigate). The interface for the Qualification prompt is the most confusing in the whole system, so it’s even more baffling for a user to try to build out a filter and much more likely for them to filter as little as possible, increasing the odds they build a bad report.
Report Wizard is even more of a disaster, as you now only have two object prompts: Template and Filter. If you’ve already designed Templates and Filters, why would you need a user to use this tool instead of just running the reports you’ve built them for? Not to mention, you still start out in the project root, so the hunt is still on for the appropriate objects for the user to choose.
Both of those tools are incredibly scary for a brave business user and even if they try to make sense of them, there’s almost no chance of success. I’ve never once seen a user create a competent report using either of those, and only met a couple that even dared to try. After no doubt selling your users on the power and flexibility of MicroStrategy, it’s not the ideal way to welcome analytic adventurers.
Building a Better Create Report Template
It turns out that to fix these tools and make them very powerful is very easy. The two existing options are basically just reports with Object Prompts. We can easily build our own version of this with a much more focused approach. At the very least, build your own Attribute and Metric Object Prompts, but be sure to set their default starting folder (an option in the Prompt Editor) to the actual Attribute and Metric folders. You can hide any “goofy” objects that a Business User shouldn’t mess with, or set their Browsing ACL to Default, which will let them use the object but not see it in a list. You now have lots of options in this prompt to guide users quickly to the objects they would be interested in.
Next, every report is going to need a Date Prompt, so just go ahead and put your standard Date prompt on this report. Personally, my standard Date Prompt is an Object Prompt of standard filters, one of which is a Custom Date Range (Date Between) filter, so I just add this one. This prompt I have set to required, because I never want a user to run a report with no Date filter. If you have any other equally system wide critical prompts, add them directly as well.
Next, I add an Object Prompt of Prompts. I use standard Element Prompts for just about every dimension in my project, so I add them all to a single Object Prompt. This is a much better way for a user to build their own filter than the Qualification prompt offered by the default template. The user can now speak business terms and choose the “Region Prompt” as well as the “Product Prompt”, and then on the next prompt page they’ll see those two specific element prompts. I also sneak a Qualification Prompt in at the very end of that list so that advanced users aren’t completely handicapped. However, this is a buried option that I can show advanced users later instead of leading off with such a confusing interface.
The deployment is actually even easier. Make sure you enable hidden objects in Desktop (Tools -> Desktop Preferences -> Browsing -> Show Hidden Objects) and navigate to the Object TemplatesReports folder. All you have to do is place your Object Prompted Report here and users will see it when they go to the Create Report option in Web. It’ll also display as a starting point when you do New Report in Desktop, assuming you’re still showing that window (it can be toggled in Desktop Preferences). I like to set the old two to hidden so that they aren’t chosen, and I usually call my new one “Custom Report”.
Another little trick will be to set the Description of this template to “Created by the Custom Report Builder” (or something similar). Now you’ll be able to easily see when users are creating content and saving it using this tool. (Of course there are better ways to keep track of that, but that’s a neat little effortless method that simultaneously advertises the features for other users).
This is always one of the first things I modify in any of my projects, and it always gets such a great reception from the users. I even use it myself for some quick adhoc reports because it’s faster than starting from the Blank Report template sometimes.