Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
martinch
Starting Member
35 Posts |
 Posted - 2008-04-24 : 09:45:39
|
Hello all,I'm running into problems generating a report with totals in it, and was rather hoping one of you guys could help...We have a database storing the results of people taking a quiz (or rather, a set of quizzes). I need to produce a report which, for a given person, displays their score for each learning outcome, and their overall score for the quiz, for each attempt of each quiz. It should be noted that some questions may feature in none, one, or multiple learning outcomes. In addition to this, it should display the average score for each quiz (i.e. the sum of the overall mark for each attempt divided by the number of attempts).The data has been normalised into the following table structures:- Quiz (containing quiz name, unique ID, and course)
- Learning Outcome [LO] (containing name, quiz it belongs to, and description)
- Session (containing user, unique identifier, quiz being taken, and a timestamp)
- Answers (containing session identifier, question number, answer, and score)
- LO-Qn link (links LO to a specific question)
* Note: this is slightly simplified as a question can contain mulitple sub-questions, but you get the idea. *After running a fairly long query on the tables (basically, it calculates the score for each question per attempt per quiz as a CTE, which is then used twice - to calculate the total score per attempt per quiz, and the score for each LO per attempt per quiz - the results of which are then glued together, along with some other text-identifier data, and DENSE_RANK() used to extract the attempt number), some data gets spat out in the following format:QuizName | AttemptNumber | ScoreForAttempt | LOName | LODescription | ScoreForLOQuiz1 | 1 | 5 | LO1 | Blah blah | 3Quiz1 | 1 | 5 | LO2 | Blah blah | 2Quiz1 | 2 | 10 | LO1 | Blah blah | 5Quiz1 | 2 | 10 | LO2 | Blah blah | 10Quiz2 | 1 | 7 | LO1 | Blah blah | 7... etc ...I have created a report in Visual Studio that consists of a single table, which looks like this: Now, up to this point, it all works fine. However, what I want to do is include an average attempt score for each quiz in the top right cell of the table (in the blank mid/deep blue space directly below "Score"). However, if I set this to be "=AVG(Fields!ScoreForAttempt.Value)", this doesn't work as it includes too many values - number of attempts x number of LO's.Any ideas on how to do this, or is it not possible?Thanks, and sorry if it's a silly question (I'm new to Reporting Services). P.S. Sorry for the huge post, but I wanted to try to make sure I gave all the relevant information. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 11:51:00
|
| Thanks for the detailed info provided. Have you applied any form of grouping in the table? ALso are you giving this expression in header or detail row? |
 |
|
|
martinch
Starting Member
35 Posts |
Posted - 2008-04-25 : 04:01:58
|
| Hi,Yeah, there's been some grouping applied in the table object on the report - QuizName (row 1) and Attempt (row 2). The expression is going in a standard table cell (row 1), so no "AVG(ReportItems!ScoreForAttempt.Value)". :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-25 : 04:14:51
|
| You mean detail row? Why are you taking AVG on deatil? it must taken on summary column i.e where you group the data |
|
|
|
martinch
Starting Member
35 Posts |
Posted - 2008-04-25 : 04:34:36
|
| Unless I'm missing something, where I want to apply the averaging is a row with grouping defined on it. I want to place it on the far right of the first row in the screenshot (the one that starts "=Fields!QuizName.Value") - this is grouped on the QuizName field (the row below it is grouped on Attempt, and the row below that - the one in white - isn't grouped).++EDIT: I can get it in SQL, but it means folding the CTE into the overall query a third time, and I was thinking that this is more suited to being calculated in the report? |
|
|
|
|
|
|
|
|
SSRS: Problem with Tables and Totals