Filter not in Display

Here is another very common mistake in both relational and dimensional reporting. In this example a report contains Year, Month and Month No.

Somehow, there is another calculation/condition that chooses certain month values, like for example Month No is 10 or 11. In actual reporting, this may or may not be a fixed value. Most of the time it is calculated or derived from somewhere else, like a prompt value.

Now we insert this filter in our list and we get the correct result.

However, this filter is not needed in the display output so it should be removed. It is included in our query however so things should be just fine, right?

As seen in the previous image, removing the filter query item from the list makes it ineffective even if it is still included in the query. But you do not need this in the display, so how do you make it effective?

The solution is to select the whole list, then open the Properties property (sorry can’t help it), check the query items that are not included in the display that should take effect.

Now the output is correct.

The same technique can be used if a crosstab is used instead of a list. It is a little bit different however as the Properties propery is available for each item in the crosstab.