Grouping/Summing/Distinct confusion!
-
Good morning all!
I am developing a report that displays "Professional Development" (read: Training) for Teachers. I have written a SQL statement that pulls out all of the data for me, but I am having trouble getting totals of hours spent on the Groupings I've set.
Example: Scenario
- Campus - Damo's College
-Category - Mechanics
- Event - Trade Expo 2008
- Course - Engine Rebuilding
- Course - Servicing
Each Event can have multiple Courses and each category can have multiple Events and so forth. What I _want_ to do is this:
The problem that i am having is, because the kspkey (primary key) of the course can appear multiple times because it has multiple participants, if i SUM the course length I get the for example 5 hours of the course PLUS another 5 hours for each person that attended! I'll link my dataset;
My Event "c06EXPOMID" has a course assigned "c06_EXPOPPM" that goes for 6 hours. If I sum course_length grouped by event I get 42 hours! The event only contained 1 course that went for 6!! I want to be able to have two courses in an event of 6 hours each and get 12 hours for the course!!
I dont know if I need to change my select statement? or if I need to create another dataset with the totals and link them into the report?
I hope someone can help me with this because it is doing my nut!
- Damien 😀
-
Perhaps a Matrix might be better suited to your requirement, rather than the data table.
[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font] -
I don't completely understand all of your data, nor what you are looking for, but lets see if this helps at all...
I'm guessing that you want to see a list of attendees for the class, but only the class length in the footer, not the total sum of everyone's time spent in class? In that case, try using FIRST(course_length) rather than SUM(course_length) - that will just pull the course length from the first record rather than summing them all up. Since the course_length is the same for each attendee, it doesn't matter whether it is pulled from the first, last, or some random record in the middle.
Hope this helps!
Chad
-
What do you want the total hours to be for? If you want it to be for teacher, your top level group should be teacher then you can do sum of hours at that level and your report would look like:
Teacher: Corbett, Jack
Campus: Jack Tech
Category: SQL Server Report Writing
Event: Tech Expo 2008
Course: Advanced Reporting Techniques in SSRS Professional Development Hours: 3
Course: Using Custom Code in SSRS Professional Development Hours: 3
Event Hours: 6
Event: SQLSaturday
Course: Data-Driven Subscriptions in SSRS Professional Development Hours: 3
Event Hours: 3
Category Hours: 9
Campus Hours: 9
Teacher Hours: 9
Is this the idea?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
G'day all 🙂
A bit bad form getting back onto this so late, no disrespect intended to everyone that's chipped in to help! I'm sure I could get a bit of sympathy around here of deadlines changing and priorities getting blown out of the water....
I've made some headway on this project... What I ended up doing was adding grouped totals to the views that I was querying.
So for this example, I nested this query to find the event length, and then join that to my result data.
SELECT k.pdeventkey, k.description, rs1.ev_length
FROM [Cool School].dbo.KPDEVENT AS k LEFT JOIN
(SELECT k.[PDEVENTKEY], SUM(s.[course_length]) AS ev_length
FROM [Cool School].dbo.ksp AS s LEFT JOIN
[Cool School].dbo.kpdevent AS k ON s.event = k.pdeventkey
GROUP BY k.pdeventkey) AS rs1 ON k.pdeventkey = rs1.pdeventkey
This isnt a bad solution in my view, because the totals are always being calculated in the SQL tables and they are easy to use when it comes to Reporting time 🙂
Thanks to all who steered me in the right track!
- Damien
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply