Solved

# SQL Reporting Services linking values from different tables to perform calculation

Posted on 2007-09-27
2,651 Views
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.

0
Question by:shogun5

LVL 14

Expert Comment

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

0

LVL 18

Expert Comment

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
0

Author Comment

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.

Mike
0

LVL 18

Accepted Solution

basically you want to create a view, let's call it vw_StudentPop
SELECT     School, COUNT(DISTINCT S_SSN) AS studentpop
FROM         vwTeacher_Courses
GROUP BY School

In your dataset in the report, change
SELECT * FROM vw_Teacher_Courses
to
SELECT TC.*, SP.StudentPop
FROM vw_Teacher_Courses TC
INNER JOIN vw_StudentPop SP ON TC.School = SP.School

Then, in your matrix, you will have access to all your previous fields, but you will also have the StudentPop field which you can use to work out your % population using
=Fields!School.Value & " Percentage (" & (Count(Fields!S_SSN.Value) / Max(Fields!StudentPop.Value))*100  & "% )"

Hope this is clearer - let me know if it isn't!
Alex
0

Author Comment

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
0

## Featured Post

### Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…