Prompting a View Filter in a Cube Report

by bryan · October 17, 2012

Here is a very valuable tip that not everyone is aware of.  While you normally can’t add prompts into the View Filter of your report, you can if you’re using a View Report, which is a report that is explicitly linked to an Intelligent Cube.  You can use existing Element and Expression Prompts in it’s View Filter,  which allows you to create prompted reports off of cubes.

Step 1 – Create your View Report

Create an Intelligent Cube, right click it, and choose Create Report.  This will create a report that is explicitly linked to this cube and never hit the database.

Step 2 – Add your Prompt

Simply click to add a new qualification in the View Filter and you’ll find an option to Select a Prompt:

Click that, and then you can choose either an Element Prompt or Expression Prompt:

I chose an Element Prompt for Regions. 

Conclusion

Now when you run this report, it’ll have an Element prompt like normal.  Another nice advantage of this method is that the prompt is sourced from the cube itself.  This is the only place in MicroStrategy where this happens.  The difference between this and Element Caching is that Element Caching only caches the initial list of elements returned to the prompt.  If you search the prompt for a particular element, that query always goes back to the database.  However, for a View Report Prompt, an element search on the prompt will query the cube, which is fast and in memory.  If you’re having prompt performance issues, you may want to consider making your data sets instead View Reports and using this method to improve prompt searching performance.