compare tot 12 digit accts with 5 digit account

[sue1127]

I am using Crystal Reports 8 on MS sql server 2000. I need to compare the total of accounts such as 2xxxx7801 with the amount of account 27801. The 2 in the example stands for the fund, and there are several different funds. I can't seem to construct the report so that the 5 digit account is not getting summed in with the related 12 accounts.

Can anyone help?

Thanks,
Sue

 

[Turkbear]

Hi,
Please provide an example of what you have tried and what you want the output to actually be.

Thanks..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

It would help if you flesh out the requirements.

By compare what do you mean?

Are these in different tables?

Try providing:

Example data (show tables.fields, data)
Expected output (what the output would be based on the example data)

For instance "I can't seem to construct the report so that the 5 digit account is not getting summed in with the related 12 accounts"

Why would a 5 digit account be summed with a 12?

Your examples show a 5 digit and a 9 digit account, so where does the 12 digit come in?

-k

 

[sue1127]

I have tried grouping by 5 digit account then by 12 digit account. For example, you have a 5 digit(fund) account:

27405 45.00

You have several 12 digit accounts:
212347405 10.00
236987405 15.00
243567505. 20.00

I want the total of these 12 digit accounts(i.e. 45.00) to appear right below the amount of the corresponding 5 digit account, so that I can see immediately if there are any 12 digit accounts that do not total to the amount of the 5 digit account.

I have tried grouping several different ways, but I keep adding the 5 digit account in with the corresponding 12 digit accounts.

Does this make sense?

Thanks,
Sue

 

[sue1127]

Sorry, I'm confusing myself.

In the example in my last post, the 3rd 12 digit account should end with 7405.

 

[sue1127]

I'll try that again. It's been a very long day.

The 12 digit accounts would be
200012347405 10.00
201036987405 15.00
202043567405 20.00

The fund account in all cases is 27405, and that amount
should equal the total of the 12 digit accounts

 

[lbass]

Create a formula:

left({table.account},1)+right({table.account},4)

Insert a group on this formula. If the account number is not a string, then change the formula to:

left(totext({table.account},0,""),1)+right(totext({table.account},0,""),4)

-LB

 

[synapsevampire]

Doesn't make sense to me.

Allowing for the typo, you have 3 accounts that have the last 5 digits different from the 5 digit account, perhaps what you're trying to say is that you're comparing the last 4 digits of the 12 digit accounts to the last 4 digits?

Taking time to post example dtaa and expected output based on it makes sense.

Since you're even confusing yourself, it stands to reason that you're not getting your requirements across.

Is there a reason why you elect NOT to post example data and the expected results, rather preferring to try to describe requirements?

It's much simpler to give examples.

You can create a formula of right(trim({table.field}),4) to use to group by, if that helps. This assumes that they are the same field, which you still haven't clarified.

Example data would do so. btw, example data doesn't mean your current output format, it means what the data looks like in the table, as in:

acct amount
27405 45.00
212347405 10.00
236987405 15.00
243567505 20.00

Output (using the right 4 characters of the acct field)

For Group 7405
acct amount
27405 45.00
212347405 10.00
236987405 15.00
243567505 20.00

As for not summing 5 digit accts, create a Running Total and in the evaluate->use a formula use:

len(trim({table.acct})) > 5

Your rush to post prevents people from understanding your requirement, please take the time to state specifics if you need additional help.

-k

 

[Turkbear]

Hi,
Is it consistant that the first digit and last 4 digits in the 12 digit account number identify the corresponding 5 digit account number, and are they in the same data field?








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sue1127]

Turkbear,

Yes to both questions.

 

[MrBillSC]

Use lbass's formula above for a group.
Below that create another formula based on length of account number:

// formula {@AcctLen}
If Length({table.acctNum}) = 5 then 0 else 1.

Insert this formula as a group below lbass's formula.
Suppress the group headers for these formulas.
Sum the amounts in the {@AcctLen} group footer.
Conditionally suppress the {@AcctLen} group footer if {@AcctLen} = 0.

MrBill

 

[lbass]

MrBillSC picked up on the fact that I didn't provide a way to compare the accounts. Using my formula, since the 5-digit account will end up in the same group with the 12 digit accounts, in order to identify groups where the 12-digit accounts do not add up to the 5-digit total, you could create the following formula for the amount field {@amt}:

if len({table.account}) = 5 then -{table.amt} else {table.amt}

Then right click and insert a summary on {@amt} at the
group level, where you are grouping on {@5-digitacctno}:

left({table.account},1)+right({table.account},4)

Any summary that <> 0 has a discrepancy. To only display those, go to report->edit selection formula->GROUP and enter:

sum({@amt},{@5-digitacctno}) <> 0

-LB

 

[sue1127]

Thanks to all who responded. I was able to get it working correctly.

Sue