Aggregation and granularity levels

I'm having problems implementing the following in reporting services 2005.
My hierarchy looks like this (just to illustrate the problem...):
University->Student->Exam
My query returns the following fields:
University, Student, StudentPayment, ExamName, ExamScore
I need to create a report that will show the hierarchy and to smartly
aggregate the StudentPayment to both the Student and the University levels.
The problem is that the StudentPayment field is being multiplied by the
number of exams in the upper level aggregation.
If only I could set the granularity level of the StudentPayment measurement...
Note that I don't have access to the query, so I can't change anything on
that front.
Thanks,
EfiHello Efi,
I would like to know this more detailed.
How you calculated the StudentPayment?
Did you mean that you use a constant like $10 to multiplied by the count
amount of the exams the student takes?
If you have used a group for each level of University and Student, you
could have a aggregation expression like this:
AVG(Fields!StudentPayment, 'Group_name')
You could refer this article.
Aggregate Functions (Reporting Services)
http://msdn2.microsoft.com/en-us/library/aa255811(SQL.80).aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Efi,
I understand this issue.
It seems that the dataset you use is not a 3NF data. So we may not
calculated the Student Payment.
You may use some embbed code to sum the column. I am researching this.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Efi,
Unfortunately, I did not find the solution yet.
A workaround maybe using the custom code to sum the Payment by code.
Or you could add a second dataset to get the distinct Payment for the
Student and aggregate it.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Can you please send me an example of a custom code the aggregates a field?
Thanks,
"Wei Lu [MSFT]" wrote:
> Hello Efi,
> Unfortunately, I did not find the solution yet.
> A workaround maybe using the custom code to sum the Payment by code.
> Or you could add a second dataset to get the distinct Payment for the
> Student and aggregate it.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Efi,
Currently, I did not have any examples yet. I will try to figure out any
example for you but it will be some time consuming so I appreciate your
patience. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Efi,
My internal colleague suggest that you need to change your dataset. Which
means you need to modify the sql statement you use to get the dataset or
create a new dataset.
Could you please let me know if you could do this?
Could you contact your DBA to create a new stored procedure to get the
information you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.