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