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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
planoczCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.