How to show aggregated 'count distinct' values?
Hi guys, I created a simple list with only a few columns, each row represents each units and the total number of distinct students who enrolled into this unit. I want to show the aggregated numbers of distinct students for all units at the bottom of the list, it is now using [Sum(DISTINCT_STUDENT_COUNT)] which is just simply adding all the numbers together. However, one studnet can enrolled into 2 units, so even it shows 1 distinct student headcount for unitA and unitB, the total number should be 1 rather than 2. I tried using CountDistinct at the summary level, but it doesnt work, can someone please help? Regards, JKind Regards, J
July 17th, 2012 8:21pm

Hi There Thanks for your posting. Are you doing sum or count? I have test in my test environment and if you do the CountDistinct then it will be ok Please have a look the screenshot for your help you can either use this =COUNTDISTINCT(Fields!StudentID.Value) or =COUNTDISTINCT(Fields!StudentID.Value,"DataSet1") where DataSet1 is your data set name If you have any questions please ask Many thanks Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2012 9:23pm

Hello, I tried both methods, both returned a number of '108' which doesnt make sense and no where near the number I am expecting...Here is an example of my report layout: Each row has the year, code and measure (which is referenced from the expression created in the dataset, COUNT(DISTINCT F_LOAD.SK_STUDENT) AS DISTINCT_STUDENT_COUNT). In the summary line, if I use SUM, it will only add up all the figures shown in the list. I tried both countdistinct methods, it wont display the number that I am expecting, it suppose to be 17,539, instead it only shows 108. Kind Regards, J
July 17th, 2012 9:46pm

Hi There Thanks for your posting again. As I really dont know about your data structure as well as you report layout I can only suggest you If you are using SSRS 2008 you can use lookup function to get the data from another dataset. So create another dataset for your distinct count for all you student and you can only use lookup when there is one-to-one relationship between source and destination. The syntax for lookup function is look like this So for example you second dataset might have this query Select All , count(distinct studentID) as totalStudentCount From yourtable Lookup Fields! Code.Value, Fields! Code.Value,Fields! totalStudentCount.Value,"Dataset2") Where the first Fields! Code.Value is your first dataset field ("Dataset1") and Fields! Code.Value is the field in the second dataset ("Dataset2") and both have one to one relationship with each other Fields! totalStudentCount.Value is the value which you are getting from second dataset ("Dataset2") If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. PS: if you dont want to use this approach and you dont mind please send your rdl file to my email address as sqazafi@hotmail.com So that I can have a look
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2012 11:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics