Count Unique rows within a group

Posted on 2009-04-30
Last Modified: 2012-05-06

I have a report with data similar to this

Group1 = is a header
Gp2      Gp3         Gp4       Detail
A           i               1           Some data
             i               2           Some Data
             i               3            Data
B           ii               1           Data
             ii                2          Data
             ii                3          Data
             ii                4          Data
                                           Data    (second row of 4)

What I need is within Group1 a count of disctint rows grouped by groups 1,2,3 (i.e. I need a count of 7, but all counts are giving me 8 (i.e picking up the second 4 in the B)

I suspect I need some code to manage this but unsure of it
Question by:Kelvin Sparks
    LVL 41

    Accepted Solution

    Issuing following commands:

    create table grp (g1 char(1), g2 char(1), g3 char(2), g4 int, dta char(5))
    insert into grp values ('H', 'A', 'i', 1, 'da1')
    insert into grp values ('H', 'A', 'i', 2, 'da2')
    insert into grp values ('H', 'A', 'i', 3, 'da4')
    insert into grp values ('H', 'B', 'ii', 1, 'da5')
    insert into grp values ('H', 'B', 'ii', 2, 'da6')
    insert into grp values ('H', 'B', 'ii', 3, 'da7')
    insert into grp values ('H', 'B', 'ii', 4, 'da8')
    insert into grp values ('H', 'B', 'ii', 4, 'da9')

    select sum(dcnt) from (
    select g1,g2,g3, count(distinct g4) dcnt
      from grp
     group by g1,g2,g3 ) a

    returned 7

    The inner subquery returned two rows - the first one has dcnt 3, the second 4.
    LVL 22

    Author Comment

    by:Kelvin Sparks

    But I need to be more generic than this. Is going to be needed for each header (possibly up to 10), but at the 3 lower group levels we could be looking at hundreds of records. In a SSRS code module, can we do something like your select based on the reports dataset - with an overall WHERE based on the Header - will be used to populate a text box with the return value

    LVL 41

    Expert Comment

    I've just installed SSRS 2008 but never created reports before, so my answer was the best I could offer at the moment. Hope somebody else here will help more.
    LVL 29

    Expert Comment

    by:Kumaraswamy R

    RowNumber(scope)           Parameters

    (String) The name of a dataset, data region, or group, or null (Nothing in Visual Basic), that specifies the context in which to evaluate the number of rows. Nothing specifies the outermost context, usually the report dataset.

    RowNumber returns a running value of the count of rows within the specified scope, just as RunningValue returns the running value of an aggregate function. When you specify a scope, you specify when to reset the row count to 1.

    scope cannot be an expression. scope must be a containing scope. Typical scopes, from the outermost to the innermost containment, are report dataset, data region, row groups or column groups.

    To increment values across columns, specify a scope that is the name of a column group. To increment numbers down rows, specify a scope that is the name of a row group.

    Including aggregates that specify both a row group and a column group in a single expression is not supported.

    Code Example
    The following is an expression that you can use for the BackgroundColor property of a Tablix data region detail row to alternate the color of detail rows for each group, always beginning with White..

     Copy Code=IIF(RowNumber("GroupbyCategory") Mod 2, "White", "PaleGreen")
    LVL 22

    Author Comment

    by:Kelvin Sparks
    Thanks rkworlds

    I am using a Tabilix

    Group1 is the parent with groups 2,3&4

    Asume Group1 = POE

    I have 8 rows of data bit at group4 level there are 7 rows (one of these groups has 2 detail rows - but in reality when I get past testing each group 4 will have many detail rows)

    I need to be aboe to return to a text box in the group 1 scope a value of 7

    Using RowNumber("Group1") returns 8 (as does Count)
    Using RowNumber("group4") gives an error as the scope must be a containg group (I assume this to mean Group1 or higher)

    I rteally need something equivalent to

    Count * FROM dataset where Group1 = 'POE' GROUP BY Group2, group3,group4

    LVL 22

    Author Closing Comment

    by:Kelvin Sparks
    Wasn't the solution I started looking for but was able to use this in the SP returning data to add fields to it so the data I needed came through on the SP

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
    Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    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