Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS - Count of Distinct People Where Gender = Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 2:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 12:41 PM
Points: 26, Visits: 143
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
Posted Friday, December 5, 2008 1:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 12:41 PM
Points: 26, Visits: 143
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
Posted Friday, December 5, 2008 2:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909
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
Posted Tuesday, December 9, 2008 2:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Wednesday, December 10, 2008 12:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 20,584, Visits: 9,624
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
Posted Tuesday, December 14, 2010 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 4:56 AM
Points: 143, Visits: 206
Based on the original question posted, this is what you needed:

= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))
Post #1034441
Posted Thursday, December 16, 2010 10:27 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 70, Visits: 628
is that's what you want?

SELECT
count (*)
FROM
(SELECT DISTINCT
PERSONID,
GENDER
FROM DATASET)
Post #1035966
Posted Monday, July 18, 2011 6:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, November 23, 2011 2:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, November 25, 2011 8:48 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 5:16 AM
Points: 60, Visits: 98
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 »

Add to briefcase

Permissions Expand / Collapse