1

I have SSRS report with 3 groups in Tablix

-- Group A  
----Group B  
------Group C

If group C has 0 records then I do not want to show any of Group A, B and C.

I tried to set the visibility property for Group A and Group B:

=IIF(Count(Fields!Field.Value)=0, true ,false)

But it is still showing the Groups A and B.

I Tried InScope of group C and above condition, but this is still showing the Groups A and B when record count is 0.

Also Group B is toggled by Group A and Group C is toggled by Group B. Not sure if that is an issue

Data Table

GroupA------------------- GroupB ------------------- GroupC ----------------%Detail
School 1 --------------- Grade 4 ------------------- student1 ------------- 50%
School 1 --------------- Grade 5 ------------------- student2 ----------------70%
School 1 --------------- Grade 4 ------------------- student3 ---------------- 60%
School 2 --------------- Grade 4 ------------------- student4 ----------------70%
School 2 --------------- Grade 5 ------------------- student5 ----------------60%

I have filter on GroupC to return the records has %Detail<=50
In this case I in Group B i want to see only Grade 4 and in Group C student 1.
But if In group B I see Grade 4 (drill downs to student)and Grade5 (which does not drill but i don't want to see grade 5 at all in group B for this filter)

If I add this filter on GroupB I do not see anything not grade 4 and not grade 5

TD2013
  • 97
  • 1
  • 3
  • 13
  • 1
    When you say "group C has 0 records" what do you mean exactly. Apparently some records are getting returned by your dataset, otherwise the report wouldn't even know what to display for values for Group A, B or C. Can you provide a sample of your data? – Jamie F Aug 22 '13 at 21:08
  • Detail section after group C has no records. – TD2013 Aug 23 '13 at 02:51
  • If you are using a SQL query, it is returning some rows that are in Group A, Group B, and Group C simultaneously. Otherwise, SSRS would not show that hierarchy. A row returned from the dataset counts as a record for the report. If you are filtering these records out at the detail group level (or hiding them) then you should move your filter towards the source in the dataflow. Set a filter on the dataset for example. – Jamie F Aug 23 '13 at 03:08
  • That is correct I have a filter on the last group which has aggregate function. I can not add the filter with aggregate on the data source – TD2013 Aug 23 '13 at 17:42
  • I believe you can use the same visibility or filter expression on the parent group, and there you can use the aggregates. – Jamie F Aug 23 '13 at 19:38
  • I added same filter on parent group but did not return anything. I have update my question with some sample data. – TD2013 Aug 23 '13 at 20:44
  • I resolved the issue.Thanks for the suggestions. I changed my dataset so that the aggregates calculated in the storedproc itself instead of calculating in the report. As per your suggestion added the filter on the dataset and it worked as expected. Thanks a lot for help. – TD2013 Aug 23 '13 at 23:06

1 Answers1

0

For your case, I used the following: Under Groups (Row or Column), select Filter / Add:

Expression: =len(trim(Fields!MyField.Value))

Operator: >

Value: 0

In essence,it will evaluate the length of X field. If its cero (null), then the rule will filter out said Group.

Hope it helps in your case.

alejandrob
  • 603
  • 8
  • 6