Search code examples
sqlsql-serverssrs-2008ssrs-tablixssrs-2012

SSRS 2008/2012 - ADDING AGGREGATION FIELDS

I'm attempting to add a field to an SSRS report based on 2 other displayed fields. I'm using a matrix report

Field One is a Count of Account numbers the Second Field is an Order Amount

My Attempt

    New_field=(Sum(Amount))/(Count(Account))

What is the best way to do this in SSRS. Because one cannot have Aggregate functions in SSRS.

A second and related issue is Percent increases. What would be the best way to generate Percent differences in a new column.

Notes: 1. The report is fueled using a SQL Stored Procedure. 2. Graphical Display vs tabular are acceptable

Thanks

Solution

  • You can simply put your formula in query and give it an ALIAS. I've also use CASE statement to catch the error when Count(Account)=0.

    SELECT 
      CASE WHEN Count(Account)=0 THEN (Sum(Amount))/(Count(Account)) END AS New_field
FROM TableName