1

I have an SSRS matrix fed with data like this:

Country                                  Region                                   Term Count
---------------------------------------- ---------------------------------------- ---- -----------
United States of America                 Georgia                                  SU04 5
United States of America                 Indiana                                  FA12 4
Germany                                  Unknown                                  FA14 1
United States of America                 Maryland                                 FA18 6
United States of America                 Missouri                                 WI03 2
United States of America                 Texas                                    SU07 21
United States of America                 Oregon                                   SP07 4
United States of America                 Wyoming                                  FA10 1

There's a drill-down from Country to Region. A multi-valued parameter allows the user to select specific terms.

Here's a typical result with SP12, SU12, and FA12 selected:

SSRS Report Preview

Empty columns are hidden with a filter on the column group:

<Group Name="matrix1_Term">
<GroupExpressions>
  <GroupExpression>=Fields!Term.Value</GroupExpression>
</GroupExpressions>
<Filters>
  <Filter>
    <FilterExpression>=Fields!Term.Value</FilterExpression>
    <Operator>In</Operator>
    <FilterValues>
      <FilterValue>=Parameters!Terms.Value</FilterValue>
    </FilterValues>
  </Filter>
</Filters>
</Group>

However, adding a similar filter to the rows group doesn't work:

<Group Name="matrix1_Country">
<GroupExpressions>
  <GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
<Filters>
  <Filter>
    <FilterExpression>=IsNothing(Fields!Count.Value)</FilterExpression>
    <Operator>Equal</Operator>
    <FilterValues>
      <FilterValue DataType="Boolean">false</FilterValue>
    </FilterValues>
  </Filter>
</Filters>
</Group>

I've tried the above (IsNothing(Fields!Count.Value) = FALSE) and also integer comparison ([#Count] >= 1). I've also tried a bunch of variations, like putting these in the row visibility expression.

Nothing I do seems to hide empty countries. Specifically, if I de-select SU12, Argentina should go away.

Of course, the underlying query is still returning Argentina for Count in hidden columns, so I tried filtering the main dataset with a parameter and found that table-valued parameters are a hot mess.

How can I hide row that appear empty based on the displayed columns?

bendodge
  • 470
  • 5
  • 12
  • This is lame but try using an IIF with 1 and 0 instead of Boolean values. There are some places where Booleans don't work logically in SSRS. Expression `=IIF(IsNothing(Fields!Count.Value), 1, 0)` Type `Integer` ` = ` Value `1`. – Hannover Fist Apr 26 '19 at 17:59

0 Answers0