Count Unique rows within a group

Posted on 2009-04-30
Medium Priority
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 43

Accepted Solution

pcelba earned 2000 total points
ID: 24275823
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
ID: 24275861

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 43

Expert Comment

ID: 24275928
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.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

LVL 29

Expert Comment

by:Kumaraswamy R
ID: 24275949

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
ID: 24276009
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
ID: 31576715
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

ID: 25694772

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

807 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