Summary Field Subtraction Macro

[vlfox]

I'm new to macros, but thought I was getting close:

I've written the macro below to have Summary Fields subtraction done and the test of the macro shows it to be successful, but when I try to insert it and use it in a derived field, my results down the column shows "N/A".

Sub FlexMedBal()
x=Val(SumField$("FlexMedDed","Derived Field",0,"Sum"))
y=Val(SumField$("FlexMedReimb","Derived Field",0,"Sum"))
Val(x-y)
End Sub

ThanX for any suggestions !

 

[ReportSmithing]

Is this a derived field defined by a macro? If so, you need to specify the field to be returned...try the below and let me know how it turns out.


Sub FlexMedBal()
x=Val(SumField$("FlexMedDed","Derived Field",0,"Sum"))
y=Val(SumField$("FlexMedReimb","Derived Field",0,"Sum"))
DerivedField(x-y)
End Sub

Could also do something like this...

Sub FlexMedBal()
x=Val(SumField$("FlexMedDed","Derived Field",0,"Sum"))
y=Val(SumField$("FlexMedReimb","Derived Field",0,"Sum"))
Result = (x-y)
DerivedField(Result)
End Sub

 

[vlfox]

The good news is both versions worked.

My only question now is how to have it use the sumfields for EACH employee, rather than the total of the whole report. Any tips?

ThanX for your input !!

 

[ReportSmithing]

What you need to do is add a group to your report using EMPLID...depending on how many groups you have in your report, the "0" in your macro is the group level - so "0" will be the entire report. If you next grouping is on EMPLID, then you would change your macro to reflect "1".

Sub FlexMedBal()
x=Val(SumField$("FlexMedDed","Derived Field",1,"Sum"))
y=Val(SumField$("FlexMedReimb","Derived Field",1,"Sum"))
Result = (x-y)
DerivedField(Result)
End Sub

Let me know if this works for you.