How to create SSRS report that use different aggregation algorithms on the same column? [closed]

I’d like to create a report that has columns like Year, Month, Earnings. From Month Jan. to Sept, the report shows sum of earnings. From Month Oct. to Dec, the report shows average of earnings. I am not sure how to approach this question. I am new to SSRS, please explain it as simple as possible. Thank you very much.

Answer

Assume that, you have a table which likes as below and it includes your data in SQL Server.

CREATE table TestTable (ActYear INT ,
ActMonthName VARCHAR(20) , 
ActMonthNum INT,Earnings INT)

In order to develop a report for SSRS you can use the SSRS Report Builder or SSDT in Visual Studio. I suggested the SSRS Report Builder for newbies. In the first step of the ssrs report development, you should define a data source and the report builder wizard will help to complete this step. In the second step, you can use the following query in order to populate data SQL Server to report.

SELECT ActYear , SUM(Earnings) AS TotalEarning, 
(SELECT AVG(EarnAvg.Earnings) FROM TestTable EarnAvg 
WHERE EarnAvg.ActMonthNum BETWEEN @AvgParam1 AND @AvgParam2  ) AS AverageEarn FROM Testtable EarnTot
WHERE EarnTot.ActMonthNum BETWEEN @SumParam1 AND @SumParam2
GROUP BY ActYear

The above query has written as a parametrical so that the users can determine the specific month range. Therefore you don’t need to maintain your report every changings.

For more detail to develop ssrs reports you can refer SSRS Report Builder introduction and tutorial this article