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
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 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
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 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 Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 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-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 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 (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)

Group: General Forum Members
Points: 262263 Visits: 9673
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-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

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

= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))
JasonYousef
JasonYousef
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 695
is that's what you want?

SELECT
count (*)
FROM
(SELECT DISTINCT
PERSONID,
GENDER
FROM DATASET)
Titus Tyre
Titus Tyre
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 24
Thank you, it worked for me...
srilup2003
srilup2003
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

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