Link to home
Create AccountLog in
Avatar of Mike Rudolph
Mike RudolphFlag for United States of America

asked on

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) / 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!
Avatar of Jai S
Jai S
Flag of India image

hi ...simple math on calculating percentage

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

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) / Max(Fields!StudentPop.Value))*100  & "% )"

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
Avatar of Mike Rudolph

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
ASKER CERTIFIED SOLUTION
Avatar of PFrog
PFrog
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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