Fields Blank When Summing Account Balances

[Former Member]

Hello There,

I am new to the forum and I think there must be something fundamental I am missing. have gone back several pages and not found anyone else with this problem...

I am connecting to Sage 200 to summarise an account's transactions into current, 30 days overdue, 60 days overdue and 90 days overdue. I have created a formula for each of these periods in the details section of the report which I am the totalling in the report footer section together with the account code and name. This works fine where there are accounts with transactions but the report does not display any fields at all where the account has no transactions.

Is there any way to force Crystal Reports to show this data when there are no transactions against the selected customer account or am I missing something obvious?

Any help would be greatly appreciated.

[former_member11696]

Please post your question to Sage to get support.

[Former Member]

Hello Derek,

If I correctly understood your problem then there is an easier solution to this.

Click File->Report Options-->Check the box "Convert Other Null Values to default"

Run the report and see if that does the magic.

Regards

Jehanzeb

[Former Member]

Hello Jehanzeb,

Thanks for the help.

A few of the numeric fields are now being populated by zeros but others like account name and account id are showing blanks.

I have also created a subreport which displays the account's contact details and is linked by account id. This subreport is also blank.

Regards,

Derek.

[Former Member]

Right how are the tables linked to each other?

What are the formulas you created? Can we see one of them for a sample? Usually if the time span is like 60 days or 90 days or whatever, crystal filters records accordingly and does not show the ones which do not lie on those dates however if the table link is set as left outer join, it might display all the records regardless of the entries.

Regards

J

[Former Member]

Hello Again Jehanzeb,

Firstly the user selects the account they want to check by entering the account id which is supplied to the report as a parameter.

The tables are linked in the following manner (please forgive the primitive graphics) :

Customer Account -

-

---

SL Posted Customer Transaction

¦

¦----

-

---

Currency

The report is designed to select every transaction then slot the overdue amount into the appropriate ageing period based on the age of the debt. At the end of the report it should provide totals for each ageing period and some basic customer account information like id, account name, credit limit and total overdue balance.

The thirty day formula looks like this:-

IF (CurrentDate - {SLPostedCustomerTran.TransactionDate} > 30) AND (CurrentDate - {SLPostedCustomerTran.TransactionDate} <= 60) THEN

({SLPostedCustomerTran.GoodsValueInAccountCurrency} - {SLPostedCustomerTran.AllocatedValue}) / {SLPostedCustomerTran.EffectiveExchangeRate}

The 60 and 90 day formulas are the same apart from the number of days in the IF... THEN.... statement.

Thanks Again,

Derek.

[Former Member]

Hi Derek,

Can you try the following in your formula :

IF NOT Isnull( ({SLPostedCustomerTran.GoodsValueInAccountCurrency}) and NOT Isnull( {SLPostedCustomerTran.AllocatedValue})) and NOT Isnull({SLPostedCustomerTran.EffectiveExchangeRate}) Then

IF (CurrentDate - {SLPostedCustomerTran.TransactionDate} > 30) AND (CurrentDate - {SLPostedCustomerTran.TransactionDate} <= 60) THEN

({SLPostedCustomerTran.GoodsValueInAccountCurrency} - {SLPostedCustomerTran.AllocatedValue}) / {SLPostedCustomerTran.EffectiveExchangeRate}

else

0

Thanks,

Sastry

[Former Member]

Hello Sastry

Thanks for the help, still no joy.

Regards,

Derek.

[Former Member]

Hello which version of CR are you using?

Regards

jehanzeb

[Former Member]

Hi Jehanzeb

I am using CR 11.0.0.1282.

Regards,

Derek.