Help with Summary Data Macro

[angelamarsh]

Hi,

I know I can use Macros to help make Summary Data (Sum and Count fields, etc...) but I can't figure out a way to do a simple mathmatic calculation here. I am trying to divide a DETAIL summary field by a REPORT summary field. Does anyone know if this is possible? And, if so... will you please offer a little guidance? ANY help anyone can offer will be very greatly appreciated.

Thanks so much!

 

[ReportSmithing]

Here's a macro I use to calculate average salaries. If you notice the 1 in the formula, this indicates the group level I'm using to calculate the sums and counts for my first group level - if you substitute the 1 with a 0, this will calculate for the entire group.

X = Val(SumField$("ANNUAL_RT","PS_JOB",1,"Sum")) / Val(SumField$("NAME","Derived Field",1,"Count"))
DerivedField Str$(X)

Hope this help.

 

[angelamarsh]

Thank you so much! This is exactly what I was trying to accomplish... I can't tell you how much this helps! Thank you, thank you, thank you!

 

[christimess]

Hi,
I am trying to do something similar in that I am trying to create a macro to calculate overtime percent. So I want it to take the Overtime Hours column, which is a derived field that adds multiple earning codes and divide it by the AL Hours from the PS_AL_CHK_HRS_ERN screen.
From the information above I came up with the following:
Sub PERCENT()
X=VAL(DerivedField$("OVERTIME_HOURS")) / Val(Field$("AL_HOURS"))
DerivedField Str$(X)
End Sub
There are two errors with the above macro and since I am not that good with macros I am not sure what they are.
Thanks for your help!

 

[ReportSmithing]

The two errors you're receiving are simple to correct.

First, your field OVERTIME_HOURS should not read DerivedField$ - the DerivedField command sets a value and isn't needed in this case. The Field$("OVERTIME_HOURS") is all that is needed - ReportBasic will regard this as a field and dosen't care whether it's derived or not.

Second, you very last statement is looking for a string, but you are using Val to do your division which is numeric.

Here's what it should look like:

Sub PERCENT()
X=VAL(Field$("OVERTIME_HOURS")) / Val(Field$("AL_HOURS"))
DerivedField(X)
End Sub

Let me know if this helps.

 

[RptSm]

How would I use this just to get a distinct total in the summary footer for a derived field. I keep getting duplicates