SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS - Count of Distinct People Where Gender =


SSRS - Count of Distinct People Where Gender =

Author
Message
chris-736523
chris-736523
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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
chris-736523
chris-736523
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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 Smile

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!
antonio.collins
antonio.collins
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2314 Visits: 921
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).
shilpareddy2787
shilpareddy2787
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69859 Visits: 9671
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
fausto gonzalez
fausto gonzalez
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 292
Based on the original question posted, this is what you needed:

= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))
JasonYousef
JasonYousef
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 676
is that's what you want?

SELECT
count (*)
FROM
(SELECT DISTINCT
PERSONID,
GENDER
FROM DATASET)
Titus Tyre
Titus Tyre
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 24
Thank you, it worked for me...
srilup2003
srilup2003
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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))

mjbriggs03
mjbriggs03
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 102
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search