Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2661
  • Last Modified:

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!
0
shogun5
Asked:
shogun5
  • 2
  • 2
1 Solution
 
Jai STech ArchCommented:
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
 
PFrogCommented:
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
 
shogun5Author Commented:
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
0
 
PFrogCommented:
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
 
shogun5Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now