select reports where Amount Due > zero 1

[wiegelman]

Hi,

First, the givens:
Crystal version = various (8.0, 8.5, 9.0)
Database/connectivity used = ODBC to AS/400
Example data: Total Amount Due is created from a running total field so its value will either be zero (0) or some number > 0

Expected output: Reports where Amount Due is greater than zero

The scenario is that I have a report that calculates account statements. We only want to display/print those statements where the balance due is greater than zero. The Amount Due is calculated as a final running total less a discount. How can I display/print only those statements where the amount due > 0? The Amount Due formula field does not show up in the selection criteria since it is based on a running. Any ideas/help would be greatly appreciated.
Thanks,
Tom

 

[IdoMillet]

Can you use a summary formula in the Group Selection formula?

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[wiegelman]

Hi Ido,
That was a good suggestion but will not work because the balance is calculated later in the report. The error message I get when I use a summary formula (@AmountDue)>0 in the Group selection area is:
"This formula cannot be used because it must be evaluated later."
I have a feeling that it is having a problem evaluating the running total that gets calculated as the records are processed.

Any other ideas?

Thanks,
Tom

 

[synapsevampire]

Don't use a Running Total, use a summary as Ido suggested.

Running Totals are created at the 3rd pass, Summaries are not, so they're available.

you didn't share the criteria for this total, but here's an example:

Right click a numeric field in the details and select insert->Summary->Sum and state that it is a grand total. Be sure to follow this part closely as there are several means to create a total.

Now open a formula and you will see that this summary is available to the formula.

You can also use a formula, then right click it in the details and do the same, providing the formula always returns a value, the following would not work:

If {table.code} = "A" then
{table.value}

The following would:

If {table.code} = "A" then
{table.value}
else
0

If you need further help, please post specifics about your total requirements.

-k

 

[wiegelman]

Hi synapsevampire,

Thanks for your reply. I changed the running total to a summary, summing the data instead of using the running total. Unfortunately, I am getting the same message "This formula cannot be used because it must be evaluated later".
Below is the scenario:

The report currently displays ALL customer statements even if they do not have a balance - I would like to display only those with a balance. The fields in the report are:

1. Balance forward calculated based on the entry of a balance forward date parameter - created in group header for each customer number - A/R file is queried for all transactions and the balance forward is calculeted based on credits and debits to the account up to this date.

2. Current Debits - each transaction of this type (since the balance forward date) appears in the details section for each customer (these are positive values in A/R)

3. Current Credits - each transaction of this type (since the balance forward date) appears in the details section for each customer (these are negative values in A/R)

4. Discounts (pending and verified) - comes from a sub report that is located in the group footer "a" for each customer. Pending discounts are quoted discounts that have not been posted to the A/R since the balance forward date. Verified discounts are discounts that have been received and are posted into the A/R since the balance forward date.

5. Amount due = Balance forward + (Current Debits + Current Credits) - verified discounts only. This field is located in group footer "b" for each customer.

Hopefully this is enough information. Unfortunately, this is a "canned" report supplied to us and I do not have enough experience in Crystal to understand ALL the underlying programming techniques used in the report. Any help would be much appreciated.

Thanks,
Tom

 

[wiegelman]

In reply to my "Aug 26, 2004" post above...

Have I stumped the Crystal Reports experts out there?

Maybe there is no way to calcualte the balance due and display only those where it is greater than zero...

If anyone has any ideas on this, please let me know. The vendor who supplied the report to us does not even know how to make this happen. Maybe it is a fault in the Crystal Reports program?

 

[mpdinosaur]

Hi, wiegelman;

This may be kind of out there, totally off-base and DEFINITELY inefficient, but how about creating a subreport that runs before the detail that does exactly the same thing as the main report (but only for the "current" customer.) Using a shared variable, when it returns, check the variable to see if you need to print the detail line or not.

It may be slow, but it just might work. Let me know.

MikeTheDinosaur

 

[wiegelman]

Thanks for your input mpdinosaur - that may work but I do agree it will be slow, especially since the report already has 3 other embedded sub-reports.

A work-around, which really does not solve the problem, is to create another table on our database that contains a field to hold the Total Amount Due. Then, update this field overnight via a batch program or forced an update to it when items on the customer statement changes. Then, use this Total field as the criteria to select customer statements who have balances greater than zero. It works, but requires another table as well as a program to update the total field in that table.

Just seems to me that Crystal should be able to handle this - there must be businesses out there that use Crystal to create customer invoices/statements who do not print them for customers who have a zero balances...

Again, thanks for your help.

 

[rosemaryl]

Dear wiegelman,

I wouldn't say it was a crystal issue - I would state it is a database design issue. Crystal is designed to report on databases.

So from your statements it seems that if you were to pull up a customer record in your database you cannot tell them whether or not they have a balance due unless you run a Crystal Report?

IMHO that is most definitely a failure in database design.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[wiegelman]

Hi rosemaryl,
Thanks for your reply, although, I do not agree with you 100% regarding it is a DB design issue. Granted, if I were the designer of the DB, I probably would have done it differently. We are able to "pull up" a customer record to "view" on the screen and are able to see those who have balances. That is not really the issue.

The issue is using Crystal to print a statement/invoice for only those who have balances greater than zero. As for tables involved, the are three, one with the customer info. (ID, Name, address, etc.), one with A/R transactions (debits and credits), and one with the discount info denoting whether the discount in the A/R table is verified (amount is actually subtracted from balance due) or pending (amount is not yet subtracted from balance due). Our problem appears to stem from the way Crystal calculates Running totals and summary fields. Please see post from "synapsevampire" of 8/25/2004 and my post of 8/26/2004.

Any other ideas, since we are not in the position to change the DB design on our AS/400 since an outside vendor supplies it. What is funnt is that I am able to use MS Access (and some VBA) to create the report. Too bad the outside vendor will not support it...

Any other ideas?
Thanks, Tom

 

[rosemaryl]

Dear Wiegelman,

The reason I stated it is a db issue is because Crystal is not a programming application. It works best just reporting on data as it finds it. It gives us lots of tools to do things that extend its capabilities, but at then end of the day it is still just a reporting application.

If you are able to pull up the customer record and see the customer balances - then what field is that data stored in?

If it is done via code then to my mind it should be stored in the database itself. I understand that if it is not - then it is an issue for you to deal with.

The difficulty here is that we are trying to assist you with concepts - I do not have your database nor do I have a schema that tells me the tables and fields.

I have reread the posts above and the issue isn't that Crystal can't do it - I can easily create a statement or invoice that states {Customer.Total Due} <> 0 - it is that your report isn't currently structured to allow crystal to do this.

And, the issue isn't the way crystal does summary or running totals ... it is the way they are being used on the report.

When you described your report your description didn't really make sense to me. Instead of telling us what you have done ... I would rather see:

Tables in use, the joins, Fields that are pertinent.

From your descriptions, I don't understand why you don't have the following simple formula that does the debit, credit and any "verified" discount.

You have given us lots of information but not the information I need.

Try providing the actual fields you are using and a sample of the data in each. I don't want to know how you placed it on your report - I want to know how the data looks in the table.

A sample of the data you would see if you put the fields on a report simply grouped by customer would be most helpful.

Best regards,
ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[wiegelman]

Dear rosemaryl,

Thanks for your reply - I do understand your point completely. Since I see your email address is included in your post, I could forward the report for you to check out - that is, if you want to check it out. You will not be able to refresh the data but should be able to at least look at the tables, links, sub reports, formulas, etc. If, we do find a fix or even a work-around, we could post it back here to share with the community... Please let me know if you want me to forward the report to your email address.

Again, unfortunately, this is a "canned" report supplied from an outside vendor who states they cannot get it to work without the extra table containing the Balance due field...which is now included in the report.

Thanks, Tom

 

[rosemaryl]

Dear Tom,

You can email it to me as long as you agree that all communications regarding the report are to be carried out here on tek-tips and not privately.

That way everything stays at tek-tips!

Regards,
ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[wiegelman]

Sounds like a plan. I will send the report to you via email after removing anything that I feel may contain confidential and/or otherwise privacy related information. I agree that it is best to share this in Tek-Tips, I am just very security conscious and do not want to run into any privacy issues.

Thanks for agreeing to take a look at it.

Tom

 

[rosemaryl]

Dear Tom,

I have looked at the report and since you didn't send saved data it makes it hard to discern what will display. I do understand since this has student data why you didn't send saved data.

If I were setting up this report I would have simply grouped on Student ID and then ARMAST. I understand that you didn't create the original report, so you were just working with what was already there.

I would assume that the Student ID is Unique for each student so grouping on their last name, first name, and then ID is doesn't make sense.

I am curious as to why you are passing the balance forward date and using it in the subreport? Does this mean you are pulling all transactions for the student, but only balance forward information in the ARMAST <= to that date? This could have easily been accomplished on the main report by creating the following formula:

If {ARMAST.TRANSACTION_DATE} <= {?Balance Forward Date}
then {ARMAST.AR_CREDIT_AMT}
else 0

Now, you place that in the details (you can suppress if you don't want it to show) and then you can insert a summary into the report on that field to get the total. No subreport necessary.

And, my goodness didn't they do a lot of things the hard way in all those formulas!

For just one example, the ssn formula could simply be:

picture(Totext({STBIOS.SOCIAL_SEC_NUMBER},0),'xxx-xx-xxxx')

If I were you, I would start this report from scratch, link the tables appropriately and add the Aid table. I notice that you have ARBALS linked but have no fields from that table on the report. Why is the report there? And, ARBAL indicates to me that it contains

Same thing with STMAST - There are no fields in the report so it doesn't make sense to have the link.

By using appropriate grouping you can create this Inovice with no subreports and then should be able to use the resulting Total Due formula in a group selection formula to select only those <> 0.

This will be a little work, but in my opinion you will have a much better report.

And, a last note - The Report Options show that Null values are converted to default. Why did you choose to do that? Knowing that a field is null is a very usefull thing ...

Regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.