SQL Reporting add group total

I have a report that has a column that takes the differnce between column A and column B. The expression is written to put a zero in the case that there is a negative number. The expression looks like this:

=IIF(Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value >0, Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value, 0  )

User_Define_1 is column A
Act_Labor_Units_TTD is column B
The above formula is in column C

There is a row I would like to add a total for the group. The group name is "table1_FRSTNAME"

I tried to put in a formula that using SUM(ReportItems!textbox30.value) but it said I can only use that in headers and footers.

What should I put in th expression editor to total column C for each group?
bkpierceAsked:
Who is Participating?
 
Megan BrooksConnect With a Mentor SQL Server ConsultantCommented:
It would be the same expression you used for the Column C detail textbox, but wrapped with Sum(). So
=Sum(IIF(Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value >0, Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value, 0  ))
You could make it neater by calculating the Column C value in the dataset query.
0
 
planoczConnect With a Mentor Commented:
You can also do this.....
Add a Field to your Report Dataset (By Right clicking on the dataset name) add Name Like MyNewData.
Use the Fx Expression button to make your IIF statement.
Example: =IIF(Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value >0, Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value, 0  )
Then add your new column and place this in it.
=Sum(Fields!MyNewData.Value)
0
 
bkpierceAuthor Commented:
I'm getting a #Error with both suggestions. It's still running the report but in the cell box where the summary total is it's show "#error"
0
 
GKaloskyCommented:
The formula as givien by @rscowden is correct.  Where are you putting it?  You need to add the group level first and then enter it into a field at that level.  The field(column) is usually the same one as you are totalling, in this case, the column for field C.
0
 
bkpierceAuthor Commented:
I was getting an error with rscowden's formula, however when I added a calcuated field that planocz suggested it worked except for the #error. I just had to convert the field to decimal and now everything appears to be working. Thanks for your help.
0
All Courses

From novice to tech pro — start learning today.