InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 07:46:26 GMT20http://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxOnly 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!Fri, 25 Nov 2011 08:48:39 GMTmjbriggs03http://www.sqlservercentral.com/Forums/Topic608689-150-1.aspx[quote][b]fausto gonzalez (12/14/2010)[/b][hr]Based on the original question posted, this is what you needed:= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))[/quote]Wed, 23 Nov 2011 02:45:04 GMTsrilup2003http://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxThank you, it worked for me...Mon, 18 Jul 2011 18:12:26 GMTTitus Tyrehttp://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxis that's what you want?SELECT count (*)FROM (SELECT DISTINCT PERSONID, GENDERFROM DATASET)Thu, 16 Dec 2010 10:27:14 GMTJasonYousefhttp://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxBased on the original question posted, this is what you needed:= CountDistinct(IIF(Fields!Column1.Value="ValueOfInterest", Fields!ColumnForDistinctCount.Value,Nothing))Tue, 14 Dec 2010 08:04:42 GMTfausto gonzalezhttp://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxOr 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 DESCWed, 10 Dec 2008 00:14:36 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxHello,One way is,Group on the gender Name, and write =COUNTDISTINCT(Fields!PersonID.Value)This will give you the Exact Count.Hope this helpsTue, 09 Dec 2008 02:38:25 GMTshilpareddy2787http://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxadd 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).Fri, 05 Dec 2008 14:25:52 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxJust 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!Fri, 05 Dec 2008 13:30:53 GMTchris-736523http://www.sqlservercentral.com/Forums/Topic608689-150-1.aspxIf 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.ValueData:PersonID Gender1 M1 M2 M2 M3 F3 F4 F4 F5 F5 F6 M6 MThere 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)), GENDERFROM DATASET GROUP BY GENDERI'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!!ChrisTue, 25 Nov 2008 14:06:38 GMTchris-736523