April 3, 2009 at 10:29 am
Hello:
I'm new to all of this, so please excuse me if this is a stupid question. I have a matrix that counts the number of visits each student (as opposed to faculty, public, etc.) makes to each library on campus within a certain time period. The row group is grouped by StudentID, and the column group is grouped by LibraryName. The detail text box's value is =COUNT(Fields!CardSwipeID.Value); every time a student enters a library, they must swipe their card, and each swipe is assigned a unique ID number.
That part works fine, but now I'm trying to build another matrix on the same report that shows the average number of student visits per library during that time period. I thought I could use this:
=COUNT(Fields!CardSwipeID.Value, "matrix1")/COUNT(Fields!CardSwipeID.Value)
...but it doesn't work. Matrices are kind of blowing my mind a little bit, and I've always sucked at math--can anyone help me out?
Thanks in advance!
April 3, 2009 at 12:34 pm
Have you tried using the AVG() aggregate function in the second matrix? Or is there some reason why this doesn't meet the need?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2009 at 1:14 pm
Hi, Jack:
I did try using the AVG() function, but since I need to average a COUNT, it throws an error about not being able to nest aggregate functions. What would be perfect would be if I could do something like this:
=AVG(COUNT(Fields!CardSwipeID.Value))
...but alas, SSRS hates me and won't let me do it. I tried to do AVG(Fields!CardSwipeID.Value), but that just averages the CardSwipeIDs as if they were numbers (which they are, I guess, but they're ID numbers, not numbers to be used in calculations).
Am I making any sense? I don't even know what I'm talking about anymore. :blink:
April 6, 2009 at 1:33 pm
Hello again:
Well, I've made a bit of progress, and I feel like I'm almost there...but not quite. I'm using this as the detail for my second matrix:
=Format(CountDistinct(Fields!CardSwipeID.Value)/CountDistinct(Fields!StudentID.Value), "#.###")
...and the only problem now is that because I'm grouping by LibraryName, instead of dividing by the actual total number of students, it's dividing by the total number of students for that particular library.
For example, in January 2008, a total of 5337 distinct students visited the libraries, 2743 of whom visited Library A. I want the average for Library A to equate to =CountDistinct(Fields!CardSwipeID.Value)/5337, but it's appearing as =CountDistinct(Fields!CardSwipeID.Value)/2743 instead.
How do I keep the results grouped by library but divide by the total number of all students instead of only those who fall under each group? Any ideas?
April 7, 2009 at 8:50 am
Okay, so after wasting an inordinate amount of time on this issue, I finally figured it out:
=Format(CountDistinct(Fields!CardSwipeID.Value)/CountDistinct(Fields!StudentID.Value, "StudentVisits"), "#.###")
...where "StudentVisits" is the name of my dataset. Just in case anyone cares. 🙂
April 7, 2009 at 9:29 am
Thanks for posting the solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply