• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1086
  • Last Modified:

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?

Thanks!
  Larry
0
larrysun
Asked:
larrysun
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
use the rowcount function
0
 
larrysunAuthor Commented:
Tried the Reporting Services equivalent of rowcount, CountRows.
Tried:
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!

Thanks.
Larry

0
 
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!
  Larry
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now