Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using a group sum in a formula

Status
Not open for further replies.
laurenbattaglia

laurenbattaglia

Technical User
Joined
May 3, 2002
Messages
28
Location
US
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
 
Sort by date Sort by votes
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
tek@kenhamady.com
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
tek@kenhamady.com
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
tek@kenhamady.com
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
tek@kenhamady.com
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
tek@kenhamady.com
 
Upvote 0 Downvote
I inserted a group for &quot;{AP_INVOICES_ALL.INVOICE_NUM}&quot; and I am still getting the same error message.

 
Upvote 0 Downvote
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
tek@kenhamady.com
 
Upvote 0 Downvote
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...
 
Upvote 0 Downvote
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...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

T
  • Locked
  • Question Question
Date comparison Formula
Replies
2
Views
2K
TPG_IT
T
john432
  • Locked
  • Question Question
OLE Objects in your formula
Replies
2
Views
721
john432
john432
Corinne30
  • Locked
  • Question Question
Division of currency formula with/without rounding
Replies
7
Views
1K
dgillz
dgillz
mmck1964
  • Locked
  • Question Question
Using "OR" to select data
Replies
4
Views
994
mmck1964
mmck1964
D
  • Question Question
Dynamic field height in crosstab?
Replies
0
Views
160
doodledump
D

Part and Inventory Search

Sponsor

Back
Top