Using a group sum in a formula

[laurenbattaglia]

I need to write a formula that evaluates a group sum. When I tried the following formula:
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >= 0 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) <= 10000 THEN '$0 - $10,000'

I am getting an error message: Group specified on a non-recurring field. Is there any way around this?

Thanks

 

[kenhamady]

What is the formula for conversion?
Is this subtotal on the report? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[laurenbattaglia]

The conversion formula is as follows:

if {AP_INVOICES_ALL.INVOICE_CURRENCY_CODE} <> 'USD' then {AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT} * {AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE}
else{AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT}

and yes this is subtotal is on the report.

Thanks, Lauren

 

[laurenbattaglia]

Here is the conversion formula:
if {AP_INVOICES_ALL.INVOICE_CURRENCY_CODE} <> 'USD' then {AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT} * {AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE}
else{AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT}

and yes the subtotal is on the report.

Thanks

 

[kenhamady]

when do you get this error - when you check the formula or when you run the report? What are your report's group fields? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

sounds to me that you don't have a group based on {AP_INVOICES_ALL.INVOICE_NUM} so it cannot do a summary operation JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.

 

[laurenbattaglia]

I am getting the error message after I click on the refresh button. Also, I do have a group based on {AP_INVOICES_ALL.INVOICE_NUM}

Thanks

 

[Ngolem]

Could you describe your report layout for us:

something like this, with the sections are listed and where your problem formulas are placed:

Report Header
Pager header
Group 1 header (tell us what the field is grouped on)
...(other groups)...
details
...(other footers)...
Group 1 footer
Page footer
Report footer

there must be something in the placement of the formula fields...Also are you giving us the complete formulas or snippets of them...
JimBroadbent@Hotmail.com

 

[laurenbattaglia]

I hate to get lengthy here, but here is the report definition.

Crystal Report Professional v7.0 (32-bit) - Report Definition
1.0 File Information
Report File:
Version: 7.0
2.0 Record Sort Fields
A - {AP_INVOICES_ALL.INVOICE_NUM}
A - {AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER}
3.0 Group Sort Fields
4.0 Formulas
4.1 Record Selection Formula
{AP_INVOICE_DISTRIBUTIONS_ALL.PERIOD_NAME} = {?Period Name} and
{AP_INVOICES_ALL.ORG_ID} = 2
4.2 Group Selection Formula
4.3 Other Formulas
Name: {@PO vs Non PO}
Formula: if {AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE} IN ['ITEM','AWT'] AND
NOT({AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID} in [1,2,3,4,5,6,7,8,9,0]) then 'PO'
else
IF {AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE} IN ['ITEM','AWT'] AND
({AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID} in [1,2,3,4,5,6,7,8,9,0]) then 'NON PO'
ELSE
if NOT({AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE} IN ['ITEM','AWT']) AND
({AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID} in [1,2,3,4,5,6,7,8,9,0]) then 'MISC'
Name: {@$ LEVELS}
Formula: IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >= 0 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) <= 10000 THEN '$0 - $10,000' ELSE
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >10000 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM})<= 25000 THEN '$10,001 - $25,000' ELSE
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >25000 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) <=50000 THEN '$25,001 - $50,000' ELSE
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >50000 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) <=75000 THEN '$50,000 - $75,000' ELSE
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >75000 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) <=100000 THEN '$75,000 - $100,000' ELSE
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >100000 THEN 'GREATER THAN $100,000'
Name: {@Conversion}
Formula: if {AP_INVOICES_ALL.INVOICE_CURRENCY_CODE} <> 'USD' then {AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT} * {AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE}
else{AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT}
5.0 Sectional Information
5.1 Page Header Section
Hidden, Keep Together
PO vs Non PO
String, Visible, Left Alignment, Top Alignment,
Keep Together
INVOICE_NUM
String, Visible, Left Alignment, Top Alignment,
Keep Together
CURRENCY
String, Visible, Left Alignment, Top Alignment,
Keep Together
PERIOD_NAME
String, Visible, Left Alignment, Top Alignment,
Keep Together
Conversion
String, Visible, Right Alignment, Top Alignment,
Keep Together
PO
String, Visible, Left Alignment, Top Alignment,
Keep Together
INVOICE_DATE
String, Visible, Left Alignment, Top Alignment,
Keep Together
LINE_TYPE_LOOKUP_CODE
String, Visible, Left Alignment, Top Alignment,
Keep Together
DESCRIPTION
String, Visible, Left Alignment, Top Alignment,
Keep Together
VENDOR_NAME
String, Visible, Left Alignment, Top Alignment,
Keep Together
5.2 Page Footer Section
Visible, New Page After, Keep Together, Print At Bottom of Page
5.3 Report Header Section
Visible, New Page Before
5.4 Report Footer Section
Visible, New Page After
Sum ({@Conversion})
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together,
Bracketed, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'
5.5 Group Header Section #1
Visible
GroupName ({@PO vs Non PO})
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
5.6 Group Footer Section #1
Visible
Sum ({@Conversion}, {@PO vs Non PO})
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting,
Leading Minus, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'
5.7 Group Header Section #2
Visible
5.8 Group Footer Section #2
Visible
GroupName ({@$ LEVELS})
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
Sum ({@Conversion}, {@$ LEVELS})
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together,
Bracketed, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'
5.9 Group Header Section #3
Visible
5.10 Group Footer Section #3
Hidden
{@PO vs Non PO}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.INVOICE_NUM}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.INVOICE_CURRENCY_CODE}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.INVOICE_DATE}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Date Time Order: Date Only, Separator: ' '
{AP_INVOICE_DISTRIBUTIONS_ALL.PERIOD_NAME}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM})
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together,
Bracketed, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'
{PO_HEADERS_ALL.SEGMENT1}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.DESCRIPTION}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{PO_VENDORS.VENDOR_NAME}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
5.11 Details Section
Hidden
Subsection.1
Visible, Keep Together
{@PO vs Non PO}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.INVOICE_NUM}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.INVOICE_CURRENCY_CODE}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICE_DISTRIBUTIONS_ALL.PERIOD_NAME}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{@Conversion}
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together,
Bracketed, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'
{AP_INVOICES_ALL.INVOICE_DATE}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Date Time Order: Date Only, Separator: ' '
{AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER}
Numeric, Visible, Horizontal Centre Alignment, Top Alignment,
Keep Together,
Leading Minus, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'
{PO_HEADERS_ALL.SEGMENT1}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{AP_INVOICES_ALL.DESCRIPTION}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap
{PO_VENDORS.VENDOR_NAME}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap

Thanks for you help.

 

[kenhamady]

The problem is that you have tried to group on {@$ LEVELS} and this is illegal, because it refers to a subtotal. You probably changed this formula to include subtotals AFTER you added it as the group field. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[laurenbattaglia]

I had a feeling that I could not do it like that. Is there any other way to evaluate the total of the invoice as I was trying to do in @$ LEVEL?

 

[kenhamady]

The formulas are fine in themselves. You just can't use them for grouping. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Also..in addition to Ken's observations..I refer back to my first comment on this subject...I repeat your problem here for reference:
&quot;
IF Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) >= 0 AND Sum ({@Conversion}, {AP_INVOICES_ALL.INVOICE_NUM}) <= 10000 THEN '$0 - $10,000'

I am getting an error message: Group specified on a non-recurring field. Is there any way around this?
&quot;

SORRY BUT I don't see a group based on
&quot;{AP_INVOICES_ALL.INVOICE_NUM}&quot; (unless it is Group 3...I don't see a groupname there) in this report definition.

Without such a group you cannot do summary operations like this...that is why you are getting the error saying that it is a &quot;non-recurring field&quot;. JimBroadbent@Hotmail.com

 

[kenhamady]

Good Point. I thought I had seen that group, but there is no group for that field. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[laurenbattaglia]

I inserted a group for &quot;{AP_INVOICES_ALL.INVOICE_NUM}&quot; and I am still getting the same error message.

 

[kenhamady]

You have fixed one problem, but NOT the one causing the error.
You will get that error as long as you are trying to group on a field that uses a summary function like SUM. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[digitalquatro]

Hi...laurenbattaglia
I was in the same situation...I'm sending &quot;Parameters&quot; to the report E.g.
strName = &quot;'Transport'&quot;
.crxReport.FormulaFields(36).Text = strName
the problem is that the FormulaFields(36) is in a Groupname and is a &quot;non-recurring field&quot;

Hope this can help...

 

[digitalquatro]

Hi...laurenbattaglia
I was in the same situation...I'm sending &quot;Parameters&quot; to the report E.g.
strName = &quot;'Transport'&quot;
.crxReport.FormulaFields(36).Text = strName
the problem is that the FormulaFields(36) is in a Groupname and it's a &quot;non-recurring field&quot;

Hope this can help...