SQL Reporting Services linking values from different tables to perform calculation
Posted on 2007-09-27
In MS SQL Server 2005 Reporting Services I created a matrix report with the following groups: School, Teacher, Class as the rows and grade level in the columns.
I have two parameters. One parameter is dynamically populated from a dataset (Course), the other parameter is hard coded for the grading period, i.e. Quarter 1, Semester 1, Year, etc. My detail line provides a count of students per class.
To run the report users check the requests grading periods from the multi-value grading period parameter dropdown, then check the number of requested courses from the multi-value course dropdown parameter and view report. The report works great.
I had a request today for the report to identify the percentage of students taking a particular course. For example if the report shows 28 students at a school enrolled in the selected course, i.e. Algebra what percentage of those 28 students represent the total population at the school. I can easily do a count of the total number of students listed based on the parameters select but I don't know how to link to a table that would give me the total school population. I created another dataset called studentpop that pulls from the same view the report is pulling from and grouped by school:
SELECT School, COUNT(DISTINCT S_SSN) AS studentpop
GROUP BY School
If I could do something like this in the reports school group:
=Fields!School.Value & " Percentage (" & (COUNT(Fields!S_SSN.Value) / schoolpop.studentpop.value)*100 & "% )"
That would be great but I can't I need to first link to the appropriate school and then divide by that's school's total population.
I hope I have provided a clear enough explanation. I am trying to do this in the report with datasets without messin' with SQL. However, I am opening to any suggestions.
Thanks in advance!