Reporting Services: summarize the results of an expression

Reporting Services: need to summarize the results of an expression.
This expression, in the detail line, properly, returns a 1 where there are no values in Deficiency.
It returns a 0 for each record with a deficiency:
iif(countdistinct(Fields!Deficiency.Value)> 0, 0 , 1)

If I could summarize these results, or capture them in a field where they could be summarized, or increment a counter, I would have the results I need:  the count of records with no deficiency.

I've not had luck encapulating the IIF inside a sum, runningvalue, etc.  I've not had luck incrementing a calculated field with the results, so I could summarize later.

I see where I could put some VB code in the report, but I'm used to working within events, and the books I've reviewed so far haven't been much help.  How would I trigger the code?

Who is Participating?
larrysunAuthor Commented:
Stumbled onto the solution:
Working code for group total and report total:
(CountRows("table_group") - count(Fields!Deficiency.Value))
(CountRows() - count(Fields!Deficiency.Value))

First count includes all rows in the group.
Second count includes all non-null rows
All - non-null = compliant

I'll close this record with this info.  Hope it helps someone somewhere.

I appreciate all the help!
use the rowcount function
larrysunAuthor Commented:
Tried the Reporting Services equivalent of rowcount, CountRows.
CountRows(Fields!DeficiencyId = "")
CountRows(where Fields!DeficiencyId is null)

Both of them result in this error:
Error      1      [rsInvalidAggregateScope] The Value expression for the textbox textbox29 has a scope parameter that is not valid for an aggregate function.  The scope parameter 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 data set.

The format of the CountRows function in Reporting Services is:
Format:  CountRows(Scope as String) as Single
Scope: Name of a DataSet or the name of a Grouping or DataRegion that contains (directly or indirectly) the report item that the aggregate function is used in.

If I could:
CountRows(where Fields!DeficiencyID is null)
it would give me what I need.  The "where" is not valid.
The "null" is not valid.

These expressions in Reporting Services don't accept an aggregate within an aggregate.  Everything I've tried in the way of limiting my count to "field is null" or equivalent has been unacceptable to Reporting Services.  As queries, these same statements work fine.

Other ideas?  I really appreciate your help!


Closed, 500 points refunded.
Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.