larrysun
asked on
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!D
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
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!D
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
SQL_SERVER_DBA
use the rowcount function
ASKER
Tried the Reporting Services equivalent of rowcount, CountRows.
Tried:
CountRows(Fields!Deficienc
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
Tried:
CountRows(Fields!Deficienc
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
Vee_Mod
Community Support Moderator