Earlier (actually long back), posted a topic about placing Object prompt in the RS document/ RS Grouping;
Here is the similar trick to use object prompt in the dataset created from Cube;
- Create an object prompt with metrics (A, B, C, D)
- Create a cube with attributes and with above A, B, C, D metrics and create a data set using this cube
- Drag and drop object prompt from MicroStrategy Desktop window to the cube
- And it works…..I tested this in 9.3 and 9.2.1 version.
This is an old trick concerning sorting in reports and templates.
Let’s say your report uses an object prompt containing a number of attributes and you want the results to be ordered by those attributes. If you simply go to the ‘Advanced Sorting’ menu you will see that the attributes are not there, obviously because they are not part of the report itself.
Here comes the trick. Just add the attributes to the report, define the sorting and then remove them. Go again into the ‘Advanced Sorting’ menu and you will see that they are still there. Neat!
So now, when you execute the report and select your attributes from the object prompt in any combination, the Analytical Engine will know how to deal with them. Even if you play with the position of the attributes (rows, columns or page-by) the sorting options will be maintained.
Last month Jeremy wrote an excellent article on how filters can return unexpected results. My colleague Oznur found a somewhat similar issue and he was kind enough to allow me to share it with you.
So, there was this tiny little report that used an element prompt, and that prompt contained a list of default answers. Nothing fancy so far. However, it has come to my colleague’s attention that this report had turned rogue… How else could we explain that it started returning result sets for attributes that weren’t even chosen by the user when answering the prompt?
Well, there is a simple explanation, though it did required a little bit of outside of the box thinking. MicroStrategy stores the prompt default answers (ID and DESC) exactly as they were the day the prompt was created or modified. But what if something untoward happens to the lookup table, something like modifying the lookup value for a certain ID?
What happens is that while you distinctly remember choosing “Books” you end up seeing the sales for “Beverages”, and that is because the SQL Engine will write the WHERE clause with its mind on the ID not on DESC field. Since you selected “Books” and this lookup value corresponded to “1001”, then the SQL will filter on “1001”. Then, in the last step it will perform a join with the lookup table and get the description of “1001”, which now is “Beverages”.
Have you ever checked placing a report with object prompt in report service document? if so, then the object prompt won’t appear in document.
- Place all the attributes (which present in object prompt) in to the report
- Add as a data set into report service document.
- Drag and drop the report(grid) into the document.
- Save and close the document.
- Open the report, remove all the attributes and place the object prompt into the same place (column or row) where attributes are placed.
- Save and close the report.
- Now, execute the document.
No wonder, it will ask you to select the attributes from the list.