SQL Reporting Services linking values from different tables to perform calculation
Hello Experts:
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:
<<schoolpop dataset>>
SELECT School, COUNT(DISTINCT S_SSN) AS studentpop
FROM vwTeacher_Courses
GROUP BY School
If I could do something like this in the reports school group:
=Fields!School.Value & " Percentage (" & (COUNT(Fields!S_SSN.Value)
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!
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:
<<schoolpop dataset>>
SELECT School, COUNT(DISTINCT S_SSN) AS studentpop
FROM vwTeacher_Courses
GROUP BY School
If I could do something like this in the reports school group:
=Fields!School.Value & " Percentage (" & (COUNT(Fields!S_SSN.Value)
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!
Your best bet is to create a view that does the max population query you listed. You can then join your matrix dataset to this view so that every record also contains the population of the associated school.
You can then add an expression into the matix as
=Fields!School.Value & " Percentage (" & (Count(Fields!S_SSN.Value)
by using max, it doesn't matter how many records are included in each row, you will always get the correct population.
Hope this helps - let me know if you need any further clarification
Alex
You can then add an expression into the matix as
=Fields!School.Value & " Percentage (" & (Count(Fields!S_SSN.Value)
by using max, it doesn't matter how many records are included in each row, you will always get the correct population.
Hope this helps - let me know if you need any further clarification
Alex
ASKER
PFrog,
Thanks for the information. On Monday, when I return to work, I will give this a try. Still not sure it will work because I must somehow link the value in the school group to school value in the new view. Let's say I have 45 schools that populate down in the matrix will respective information how do I link say school A in one dataset with School A in another dataset?
You mention creating a view with the same syntax used for the dataset but now sure the process for 'joining' the matrix to that view as well. I currently use the view: vwTeacher_Courses. This view gives me all the districts, the schools in the district, the teachers in each school, the classes taught by each teacher, and the students per class. I created another dataset as mentioned before called studentpop which also uses the vwTeacher_Courses but this dataset groups the student count for each school. So in each iteration of the school group, if I can search the schoolpop dataset for the same school as the group school and perform the percentage calculation I would have my solution.
I hope this makes sense. Anyway, I'll try this on Monday and see if your max formula will work...just need some assistance on how I would 'join' the view to the matrix dataset (i.e. callled cdb which uses the vwTeacher_Courses view.
Thanks in advance!
Mike
Thanks for the information. On Monday, when I return to work, I will give this a try. Still not sure it will work because I must somehow link the value in the school group to school value in the new view. Let's say I have 45 schools that populate down in the matrix will respective information how do I link say school A in one dataset with School A in another dataset?
You mention creating a view with the same syntax used for the dataset but now sure the process for 'joining' the matrix to that view as well. I currently use the view: vwTeacher_Courses. This view gives me all the districts, the schools in the district, the teachers in each school, the classes taught by each teacher, and the students per class. I created another dataset as mentioned before called studentpop which also uses the vwTeacher_Courses but this dataset groups the student count for each school. So in each iteration of the school group, if I can search the schoolpop dataset for the same school as the group school and perform the percentage calculation I would have my solution.
I hope this makes sense. Anyway, I'll try this on Monday and see if your max formula will work...just need some assistance on how I would 'join' the view to the matrix dataset (i.e. callled cdb which uses the vwTeacher_Courses view.
Thanks in advance!
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PFrog,
Thank you for the explanation. Yes, this method works fine. Thanks for sharing the code as well. I certainly learned something new and it this method makes sense. No doubt I can use it on other scenarious in the future.
-Mike
Thank you for the explanation. Yes, this method works fine. Thanks for sharing the code as well. I certainly learned something new and it this method makes sense. No doubt I can use it on other scenarious in the future.
-Mike
if for 500 its 20
for 100 ?
100 * 20/500 = 4%
T = tota no. of students
N = no.of student in a particular group
% = 100 * N / T