Search code examples
reporting-servicesreportingservices-2005ssrs-tablix

SQL Server Reporting Services - Average Values for the row

I have a tablix that produces data like below:

ID | Name | Term1 | Term2
01 | RDA  | 2.9   | 3.9
02 | RDA  | 3.2   | 3.5

I want to have a column at the end of the tablix that averages the values for each row.

I've tried the following:

 =(Sum(CDec(Fields!term1.Value),"DataSet1") + 
     Sum(CDec(Fields!term2.Value),"DataSet1"))/2

However it adds all the values for (2.9 , 3.9, 3.2 and 3.5) rather than just for that row which would be 2.9 + 3.9 /2.

Is it possible to calculate the average for each row?

Solution

  • I just replicated your data and your report using the following and it performs the sum() as it should:

    =(sum(cdec(Fields!TERM1.Value)) + sum(cdec(Fields!TERM2.Value))) / 2

    I removed the ,"DataSet1" from the calculation. This adds a scope to the data, when scope is applied it aggregates for the entire dataset - from MSDN:

    Scope (String)

    The name of a dataset, grouping, or data region that contains the report items to which to apply the aggregate function. If Scope is specified, the aggregate function applies to all data in the dataset, grouping, or data region. All group expressions for the specified scope must contain simple field references. For more information about the Scope parameter, see Using Report Functions in Expressions (Reporting Services).