SSRS - Count of Distinct People Where Gender =

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » SSRS - Count of Distinct People Where Gender...

SSRS - Count of Distinct People Where Gender =

Rate Topic

Display Mode

Topic Options

Author

Message

chris-736523

Posted Tuesday, November 25, 2008 2:06 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:22 AM
Points: 26, Visits: 140

If anyone could shed some light on a method for computing counts for this data in SSRS, i would sure appreciate it.

Fields:
Fields!PersonID.Value, Fields!Gender.Value

Data:
PersonID Gender
1 M
1 M
2 M
2 M
3 F
3 F
4 F
4 F
5 F
5 F
6 M
6 M

There are two rows for each person and a gender identifier on row (common to one PersonID)

I am trying to find a way to count distinct personid per Gender. There should be 3 Males and 3 Females. I do not mind counting them individually with hardcoded gender. I would do it in sql like this;
SELECT COUNT(DISTINCT(PERSONID)), GENDER
FROM DATASET
GROUP BY GENDER

I've tried
=SUM(IIF(fields!Gender.value = "M", 1, 0)) but i keep getting a count of 6 when it should be 3.

I've also tried a variety of Count functions with no luck. If i could dream the code that i want, it's =COUNTDISTINCT(Fields!PersonID.Value where Fields!Gender.Value = "M") which we all know does not work.

I thought i was close by putting the data in a table (within the footer) with grouping that would get the rows down to 6, but then when i try to use the table footer for counting, it counts the original data set, not the grouped one.

Thanks!!
Chris

Post #608689

chris-736523

Posted Friday, December 05, 2008 1:30 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:22 AM
Points: 26, Visits: 140

Just thought I would followup on this one in case anyone else has the same issue.

I'm pretty sure this can't be done with an expression without custom code, maybe that will get me a response :)

What i did to fix it is to alter the Stored Procedure that was providing the data. I only include a value for gender on what i have called the 'master row' for an individual person, otherwise it's left null. Then a count of the value is accurate because the duplicate rows do not also contain the gender. I knew all along that this was one way to do it, but i wanted to do it in SSRS!

Post #614940

antonio.collins

Posted Friday, December 05, 2008 2:25 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:31 PM
Points: 437, Visits: 883

add another dataset based on the sql you already mentioned. then reference the first row and last row of that dataset in your expressions (if sorted by gender, Females will be first row).

Post #614980

shilpareddy2787

Posted Tuesday, December 09, 2008 2:38 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 6:19 AM
Points: 92, Visits: 66

Hello,

One way is,

Group on the gender Name,
and write =COUNTDISTINCT(Fields!PersonID.Value)

This will give you the Exact Count.

Hope this helps

Post #615988

Ninja's_RGR'us

Posted Wednesday, December 10, 2008 12:14 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357, Visits: 9,539

Or another one :

SELECT Sex, COUNT(*) as Total FROM (SELECT Id, Sex FROM dbo.Table GROUP BY Id, Sex) dtName GROUP By Sex
--ORDER BY Total DESC

Post #616704

fausto gonzalez

Posted Tuesday, December 14, 2010 8:04 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:25 AM
Points: 62, Visits: 158

Based on the original question posted, this is what you needed:

= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))

Post #1034441

JasonYousef

Posted Thursday, December 16, 2010 10:27 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:28 AM
Points: 68, Visits: 558

is that's what you want?

SELECT
count (*)
FROM
(SELECT DISTINCT
PERSONID,
GENDER
FROM DATASET)

Post #1035966

Titus Tyre

Posted Monday, July 18, 2011 6:12 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 6:22 AM
Points: 1, Visits: 24

Thank you, it worked for me...

Post #1143825

srilup2003

Posted Wednesday, November 23, 2011 2:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 23, 2011 2:44 AM
Points: 1, Visits: 1

fausto gonzalez (12/14/2010)

Based on the original question posted, this is what you needed:

= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))

Post #1210810

mjbriggs03

Posted Friday, November 25, 2011 8:48 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, January 26, 2013 11:29 AM
Points: 57, Visits: 93

Only filtered through a couple of the resposnes, but I like this one the best. Adding datasets is always a simple solution around problems like these. I use them a lot when adding parameters.

Yeaaaaaa buddy!

Post #1211996

« Prev Topic | Next Topic »

Permissions