Reporting Services: summarize the results of an expression

Posted on 2007-10-03
Last Modified: 2008-01-09
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?

Question by:larrysun
    LVL 16

    Expert Comment

    use the rowcount function

    Author Comment

    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!



    Accepted Solution

    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!
    LVL 1

    Expert Comment

    Closed, 500 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now