CODEWITHSUNDEEP
reporting-serviceshide
JeniQ
JeniQ

Reputation: 117

SSRS: How to hide column only on some grouped sheets

I have a report that reports people who attended events. Each row represents an attendeed record and the report output is chunked into individual spreadsheets so that each spreadsheet represents event attendees by BusinessEntity and EventType. This is achieved by a single group with the following grouping: Group on BusinessEntityName and then group on EventTypeCode And there is a page break between each instance of the group.

That means that for every combination of BusinessEntity and EventType there is a single spreadsheet, with each spreadsheet containing 0+ rows of Event Attendees. In my particular scenario, I may have spreadhsheets like this:

(My report runs against a stored procedure that pulls in mostly concatenated data, so very little data manipulation is going on in the report itself.)

For each row, there is a field called TechnicalCenter which may or may not be NULL. An Event might be associated with a TechnicalCenter and if it is I want to display that information. To continue with my example above, the data might look like this:

So if spreadsheet #A2 has no rows that have a TechnicalCenter, then I'd like to hide the Technical Center column on that spreadsheet.

I guess that's a long example of saying, Can I hide a column if there is no data in the specific set I'm displaying in that group? I know I can conditionally hide a column by using NOROWS or IF(COUNT), but I'm not sure how to do that in just a limited set of data on a single group/spreadsheet. I also know how to hide the data based on a parameter selection, but this is different.

I hope I'm making sense. Thanks in advance and please ask questions so I can fill in the gaps I'm sure I've left.

~~~~~~~~~~~~~~

OK, thanks for the pointer to using Scope with Count. I can see how that will work, but am not quite there yet. When I run the report with this expression

=Count(Fields!Center.Value, "BusinessEntityName") in a detail row, I am shown the correct count value of how many detail rows contain a TechnicalCenter. (Where Center.Value is the name of the Technical Center and BusinessEntityName is actually the name of my row group which is grouping by both BusinessEntity and EventType.)

This makes me happy.

Then I try to apply the same logic to hiding my column. I set the Column Visibility to show/hide based on this expression:

=IIF(Count(Fields!Center.Value, "BusinessEntityName")=0, "True", "False")

When I attempt to preview the report, I get an error: The Hidden expression for the tablix '' has a scope parameter that is not valid for an aggregate function. The scope parmeter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

I fail to understand the error because I thought I was using the name of a containing group, BusinessEntityName. Can someone help explain to me the logic that I am clearly missing?

~~~~~~~~~~~~~~~~~~~

http://msdn.microsoft.com/en-us/library/ms159673(v=sql.100).aspx

I am trying to learn more about scope, and I think the problem is that "You must use the default scope or specify a scope higher in the containment order." So, I am trying to apply a scope that is lower in the containment order (row group) than the data I'm applying it to (data region, I think).

I can conditionally hide a cell in the column I want to hide with this expression: =IIF(CountDistinct(Fields!Center.Value, "BusinessEntityName") < 1, True, False)

However, if I try to apply that same expression to the column visibility, I get the scope parameter error. So I say to myself, how do I fix this containment order problem?

I tried to change the scope to the Dataset name (EventMemberAttendance). Column Visibility is based on this expression:

=IIF(CountDistinct(Fields!Center.Value, "EventMemberAttendance") < 1, True, False)

When I run the report preview, I no longer get an error and on the off chance that the Center value is null for the entire dataset, the column is actually hidden. But if there is at least one Center value in the entire dataset, then the column is visible on every sheet, not just the sheets where Center data exists. I also tried changing the scope to the tablix itself with similar results as just described.

How do I specify that the scope should be the output of a single tab that is the result of a group combined with page breaks? How do I get around this requirement: "When a built-in function states that you must specify the current scope or a containing scope, you cannot specify a scope that is lower or at the same level in containment order than the current scope."

Thanks!

Upvotes: 1

Views: 7559

Answers (1)

JeniQ
JeniQ

Reputation: 117

Because this question was complex to begin with and has even more complex notes added at the bottom, I'm considering this question is about how to write the VB expression for determining whether or not a value exists in a given group of results. The note about Scope was tremendously helpful and lead me to learn how to write this: =Count(Fields!Center.Value, "GroupName")

Therefore, I consider this question answered.

I opened a new case with a more clear example of the conditional visibility problem that is at the root of my questions: Hide a column conditionally based on the values of a group that display on a single tab sheet

Upvotes: 0

Related Questions