Create a column within an SSRS Report to sum values based on conditions of previous column

Question

I have an ssrs report that shows revenues for each month of a year for several years based on start and end date parameters the user enters. What I would like to do is add a second column that shows the revenue for that quarter (march would show q1 revenue, june q2 etc). the problem I am having is that I am able to do this for a single year but once the query starts looking at multiple years my column shows the value for all 1st quarters regardless of year. The picture shows what I am currently getting. Any recommendations on how to fix my expression to also look at the year value when summing these quarters?

Here is the code I am using to only populate the quarter column based on the month.

    =IIF(Fields!Sort_Order.Value=3,
        Sum(Lookup(1,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(2,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(3,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings")),
    IIF(Fields!Sort_Order.Value=6,
Sum(Lookup(4,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(5,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(6,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings")),
    IIF(Fields!Sort_Order.Value=9,
    Sum(Lookup(7,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(8,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(9,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings")),
    IIF(Fields!Sort_Order.Value=12,
    Sum(Lookup(10,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(11,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings"))
    +Sum(Lookup(12,Fields!Sort_Order.Value,Fields!Calculated_Revenue.Value,"Bookings")),""))))

I dont really understand the question but this article helped me alot when trying to do monthly / quarterly reporting. social.msdn.microsoft.com/Forums/Lync/en-US/… — Joe C
Have you tried doing this within a sproc? Probably easier than trying to do these complicated IIF's — StelioK
Try solving all this in SQL. SSRS is for presentation. The best practice is to do the calculations in SQL. — ViKiNG
I'd do this in SQL using sum() over for each quarter (partitioned by year and quarter) and then present the values in the report.. This should not be done at the report level! — Harry

Tom Tom · Accepted Answer · 2017-12-28T18:44:18

I ended up creating a quarter column within my data set that was then used as a parent group for the month rows. I then hid that column and added a total for the quarter that gave me sub totals. Not exactly what I was looking for but gave me the result I needed.

Create a column within an SSRS Report to sum values based on conditions of previous column

1 Answers