In order to avoid circular references, the usage of calculation variables or aggregators in filter expressions is prohibited.
When using iif (...) statements in the table cell definitions it is possible to hide the values that do not fulfill the if condition - however, the empty result lines are not shrunk and appear on the report.
The visibility of individual table rows cannot be toggled with them means of the Report Template Editor, but using Microsoft BI studio you could hide individual table rows based on a calculated expressions. The values are not erased like in a filter, but simply hidden.
If you do not have a BI studio development system at hand you could use the following procedure to make use of the feature:
Procedure:
- Set up a helper table to calculate your aggregator: e.g. average area
- Hide the entire helper table
- Add a second table you would use for the result output
- Use a dummy column where you would set up a filter condition like:
= iif(Peak_Area>Avg(area),Peak_Area,"")
- Upon saving the template this syntax gets translated to the correct RDL syntax
=iif(Fields!PEAK_AREA.Value>Code.area.Avg(),……
- Open the template in an XML editor and navigate to Body > ReportItems > List > ReportItems > Table > Details >TableRows > TableRow
- In the section
</TableCells>
<Height>0.21in</Height>
</TableRow>
Add a section like
</TableCells>
<Height>0.21in</Height>
<Visibility>
<Hidden>=iif(Fields!PEAK_AREA.Value>Code.area.Avg(),False,True)</Hidden>
</Visibility>
</TableRow>
The output of the filter must be …,False,True
(originally posted in OpenLAB Forum: https://zohodiscussions.com/openlabcdsforum#Topic/68030000000172001
2015 April 13 by betrich)