SQL Server Reporting Services Max of Average?

Christian Kelih 1 Reputation point
2022-01-28T13:23:11.133+00:00

So please be patient with me as I am totally new to this topic and software but I am trying to alter a report for a customer.
We have a column that SUMs the value of each day of the week and the puts out the average o fthis week.
Which is easy cause we already have that and its just using a sum(value) / count.of.values. (Sorry those are just my easy expression and not taken from the expression in the reporting.
But in other words. It adds all values of the week and then divides it by the amount of values it added. Which is the average.

Now I need the max and min of this week.
And since we dont have that we dont know how to get the expression.
SQL only provides the max value or the min value. But it doesnt has the "average".
But the customer wants to see the max value IN the average and the min value IN the average summ.
I hope I explained it so you undestand it cause its really hard to explain.

So in other words. SQL Reporting can not fetch the average time. So we have to calculate it ourself. Which is fine.

https://gyazo.com/1add62d00d99759a00d143e93ead6f14

But now we also have to make a column where it displays the max average value and the min average value.
And I have no idea, cause for that it would need to make a comparission between all those days and for that also save the average for each day.
Which would be a horror big expression.

Any ideas or tips?

This is our calculatio/expression for average:

=Code.FormatHHMMSS(IIF(Sum(Fields!Handled_Waiting_Time_In_Seconds.Value) > 0, (Sum(Fields!Handled_Waiting_Time_In_Seconds.Value) / Sum(Fields!Count_Of_Handled_Contacts.Value)), 0))

From this we want to find the max value and the min value for the week.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,782 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Joyzhao-MSFT 15,561 Reputation points
    2022-01-31T01:48:37.443+00:00

    Hi @Christian Kelih
    I created a Table as follows:
    169704-00.jpg

    Then three new columns were added outside the group, labeled "Avg", "Max" and "Min".
    Three types of expressions are used in the text box. They are:

    =Avg(Fields!YourField.Value)  
    =Max(Fields!YourField.Value)  
    =Min(Fields!YourField.Value)  
    

    The design looks like this:

    169659-design.jpg

    By right-click—Expression,the expression is set as follows:

    169689-01.jpg

    Our commonly used expressions can usually be found in this interface.

    The final preview is as follows:

    169693-preview.jpg

    Similarly, you can also combine Avg and Max/Min expressions, like this:

    =Max(Avg(Fields!YourValue.Value))  
    

    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments