none
Average Rows by Group in Tables

    Question

  • 

    Okay, so I have this simple table with a group. I have Agents listed by name, grouped by Supervisor as you can see. Under "call monitors", the expression is a lookupset function with a custom code call to average all of the call monitors that agent gets in the filtered timeframe (ie, August, September, etc) All I want to do is have the rows that populate for each group be averaged and displayed in the blue textbox directly below the expression in the white textbox below "Call Monitors". I've tried =Avg(ReportItems!CallMonitors.Value) but SSRS doesn't like it, something about aggregates not in a header of footer. all of these fields discussed are populated from sharepoint

    I assumed that the logic behind this would be that, for each supervisor group, the average call monitor score for the group would be in that blue textbox but as I said, error :(

    [This is supposed to be an image giving a great visual for what I'm talking about, but Microsoft doesn't trust me yet since I just created an account...will post it as soon as they trust me]

    any help here would be greatly appreciated!!

    Tuesday, October 01, 2013 7:24 PM

All replies

  • Hi Brian,

    Sorry for the delay.

    In your scenario, what do you mean by "but SSRS doesn't like it"?  If you want to get the average all of the call monitors for each month, then can add a Month calculated field to your dataset
    Right-click your dataset>Add Calculated Field:
    Field Name                                         Field Source
    Month                                                =Month(Fields!Date.Value))
    And then group it on this Month, and use the expression below to get the average all of the call monitors for each month.
    =Avg(Fields!CallMonitors.Value,"Month")

    Reference:
    Expression Examples (Report Builder and SSRS)
    Lesson 11: Creating a Calculated Field
    Create a Basic Table Report (SSRS Tutorial)

    If the issue persists, please elaborate your tablix and dataset structure, if possible provide us some sample data and screentshots about it, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, October 07, 2013 10:02 AM
    Moderator
  • Thanks for your help on this.

    Unfortunately, the DataSet the Table is connected to doesn't have any data except for Agent information like username, formatted name, etc, that I'm using to Lookup on other datasets. So, I couldn't simply average any call monitor field in the dataset.

    I ended up adding a column in sharepoint for the agent dataset with additional supervisor information that I used to connect the call monitor information to the appropriate team. I was really hoping to avoid it, but I think it worked out for the best.

    Sorry i couldn't add pictures, I'm still too fresh to post them :\

    Monday, October 07, 2013 12:41 PM
  • sorry about the vague "SSRS doesn't like it". By that I was referring to the "aggregates not in header or footer" error:

    "The Value expression for the textrun 'TeamAverage.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page header or footers."

    Monday, October 07, 2013 12:45 PM