November 25, 2008 at 2:06 pm
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
December 5, 2008 at 1:30 pm
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!
December 5, 2008 at 2:25 pm
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).
December 9, 2008 at 2:38 am
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
December 10, 2008 at 12:14 am
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
December 14, 2010 at 8:04 am
Based on the original question posted, this is what you needed:
= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))
December 16, 2010 at 10:27 am
is that's what you want?
SELECT
count (*)
FROM
(SELECT DISTINCT
PERSONID,
GENDER
FROM DATASET)
July 18, 2011 at 6:12 pm
Thank you, it worked for me...
November 23, 2011 at 2:45 am
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))
November 25, 2011 at 8:48 am
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!
February 1, 2018 at 11:37 pm
February 1, 2018 at 11:38 pm
February 1, 2018 at 11:55 pm
This is a very old thread, I suggest to avoid confusion or being ignored you create a new thread with your question.
...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply