reporting-servicesssrs-2012

Sum result of Sum of textboxes SSRS

I have two different textbox and a total column like:

report design

That I want to do is to Sum all values of Expression of sJerarquiaNivel5 and Sum all values of sJerarquiaNivel2 then Sum there results and display in total column

I try it using Sum texbox like:

=Sum(ReportItems!Calculo.Value + ReportItems!Calculo4.Value), but it throws an error

error message

How can I achieve this?

Update:

As comments below I try to use =ReportItems("Calculo").Value + ReportItems("Calculo4").Value

results Problem is report only get last register of sJerarquia and use it instead sum it... It occurs because sJerarquia can have more than one value, I need to sum this value then sum with another sJerarquia

Solution

  • The report items should be referenced like this to sum multiple report items. Using ReportItems in an aggregate function is not allowed.

    =ReportItems("Calculo").Value + ReportItems("Calculo4").Value

    I would recommend referencing the actual columns from the query to do the work instead. Something like this. It's a bit easier to maintain.

    =SUM([Calculo]) + SUM([Calculo4])

    If sJerarquiaNivel5 and sJerarquiaNivel2 are complex expressions, I'd convert the logic to a SQL CASE statement. Something like this.

    SELECT 
   sJerarquiaNivel5 = CASE WHEN X = 1 AND Y = 2 THEN Z + 1 END
 , sJerarquiaNivel2 = CASE WHEN X = 3 AND Y = 4 THEN Z + 2 END
FROM 
 your_table_name