Summary/Group formula 1

[sandora]

I am writing a report that is an income statement. I have the account descriptions, account numbers and totals but I need to subtotal by account number where the account number is between x and x. The account numbers are a 7 character string. I can't figure out how to group them or summarize them. If you are familiar with accounting you know that
there is a group for Revenues and Cost of Sales and Gross Profit, etc. and then I need a grand total for all of that. Can you help me please, I'm just stuck on that group/summary formula thing. Here is what I have so far

if {@AcctNo_Convert} > 4000 and {@AcctNo_Convert} < 5000
then
"Revenues"
if {@AcctNo_Convert} > 5000 and {@AcctNo_Convert} < 7000
then
"COS"
if {@AcctNo_Convert} > 7000 and {@AcctNo_Convert} < 8000
then
"SGA"
if {@AcctNo_Convert} > 8000 and {@AcctNo_Convert} < 9000
then
"Other"
else "Acct not defined"

of course this doesn't work, the first line will work without the rest. Maybe that gives a general idea of what I want to do. If there is a better way I'm certainly open to it. Thanks

 

[synapsevampire]

Using Crystal syntax, it would be:

if {@AcctNo_Convert} > 4000 and {@AcctNo_Convert} < 5000
then
"Revenues"
else
if {@AcctNo_Convert} > 5000 and {@AcctNo_Convert} < 7000
then
"COS"
else
if {@AcctNo_Convert} > 7000 and {@AcctNo_Convert} < 8000
then
"SGA"
else
if {@AcctNo_Convert} > 8000 and {@AcctNo_Convert} < 9000
then
"Other"
else
"Acct not defined"

Also posting what's in the {@AcctNo_Convert} may prove useful.

What does "of course this doesn't work, the first line will work without the rest." translate to in technical terms? Did the formula have an error, not return the required data, or?

You'll get better results by posting techical information:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[kchaudhry]

Try this
if {@AcctNo_Convert} > 4000 and {@AcctNo_Convert} < 5000
then
"Revenues"
else
if {@AcctNo_Convert} > 5000 and {@AcctNo_Convert} < 7000
then
"COS"
else
if {@AcctNo_Convert} > 7000 and {@AcctNo_Convert} < 8000
then
"SGA"
else
if {@AcctNo_Convert} > 8000 and {@AcctNo_Convert} < 9000
then
"Other"
else "Acct not defined"

I am assuming that you are creating a group on this formula. Once the groups are created you can insert a summary.

Hope this helps!


Kchaudhry

 

[synapsevampire]

I fear that both of the formulas posted are wrong as neither takes into consideration the 1000 interval numbers, so adjust to >= and <= if need be.

-k

 

[sandora]

Thanks for the tip, sorry for such sketchy info as this is first time here and my second report. I'm using CR9, connecting to MSAccess DB. I thought it was just a syntax thing and it was. Thank you for your help. Now how do I get the thing to summarize on those groups (Revenues, COS, etc) It keeps giving me a summary for each account number.

 

[rosemaryl]

Dear SV,

You are correct ... that was what I was going to post. The accounts = to 5000, 7000, 8000, and 9000 will be *Undefined*.

Also, I think a case statement works so much better for this type of thing, and I would rather see a SQL Expression used here to group upon rather then a formula, but here is my solution:

Insert a Group on your {@AcctNo_Convert} field and then select Option, Customize Group Name, Use a Formula and then click the x-2 formula editor and insert the following formula, checking for what your actual logic should be as to the ranges:

Select {@AcctNo_Convert}

Case 4000 to 4999 : "Revenues"
Case 5000 to 6999 : "COS"
Case 7000 to 7999 : "SGA"
Case 8000 to 8999 : "Other"
Default : "Acct not defined"

I like using the Customize Group Name because the Group Tree and the report Group Name values will then Match.

Regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

It will summarize based on whatever groups you use by right clicking a field in the details and select insert->summary-><select the aggregate requied>

Now, since you've decided that this was just a syntax issue, and you prefer kchaudry's formula as the solution (even though it's wrong), I should state that this would be much faster to code out on the Access database, and allow for more reusability and ease of maintenance downstream.

-k

 

[synapsevampire]

I probably should have clarified a bit more, create a Query on the Access database as the source for your report.

-k

 

[kchaudhry]

I just suggested a fix for the IF THEN statement focusing on the syntax. You should definatley consider synapsevampire's and maryl's suggestions. They make more sense.

Kchaudhry