PROBLEM WITH NULL VALUES NOT SHOWING 1

[reidtw]

Hi,

Using CR8 connected thru' ODBC to a SQL server.

I have two tables, ACCOUNT and RECON.

The ACCOUNT table is a list of accounts held.
The RECON table is a history of reconciliations performed.

For any given rec date in the RECON table there will not necessarily be a record for all the accounts listed in the ACCOUNT table.

Left outer join from account to recon table on the account numeric id.

Set a date range parameter on the reconciliation date.

I need to show a record for ALL accounts regardless of whether a rec was done on the specified dates or not.

However, when one runs the report only the accounts where recs were performed appear.

The output should look something like this: -

ACC_ID REC_DATE APPROVED ON
123456 18/03/05 21/03/05
345678 No Reconciliation
456789 18/03/05 21/03/05

But the second line does not appear.

I am not sure how I am supposed to return a record for accounts that do not have an entry for the stipulated dates in the recon table.

The left outer join does not appear to work with the date parameter.

Assuming I have explained myself, all suggestions appreciated.

Cheers
Trev

 

[reidtw]

Hi again,

Just realised what is happening!!!

I AM getting a "No Reconciliation" warning where the account has NO records at all in RECON table.

NOT getting a "No Reconciliation" warning where the account HAS records in the RECON table for other dates.

I need to return a warning for each date entered in the parameter where no rec was performed.

Cheers
Trev

 

[lbass]

Remove the date parameter from your record selection formula and be sure there are no other selects on the reconciliation table. Then create two formulas like the following:

//{@reconc} to be placed in the detail section:
if isnull({reconciliation.acctID}) or
not({reconciliation.date} in {?daterange}) then 0 else 1

//{@dateinrange}:
if isnull({reconciliation.acctID}) or
not({reconciliation.date} in {?daterange}) then date(0,0,0) else {reconciliation.date}

Then group on {account.acctID} and then create a second formula to place in the account group header or footer:

if sum({@reconc},{account.acctID}) = 0 then "No Reconciliation" else maximum({@dateinrange},{account.acctID})

Then suppress the details section. This would give you the most recent reconciliation date for those accounts with records in the parm range or the message "no reconciliation".

-LB

 

[reidtw]

Thanks for the reply lbass, sorry, a couple of queries.

1. Should the formula @dateinrange also go in the details section? I have currently done this.

2. The formula

if sum({@reconc},{account.acctID) = 0 then "No Reconciliation" else maximum({@dateinrange},account.acctID)

gives me the error

<A string is required here.>

at the point maximum({@dateinrange}......

Cheers
Trev

 

[lbass]

Sorry. The formula should be:

if sum({@reconc},{account.acctID) = 0 then "No Reconciliation" else totext(maximum({@dateinrange},account.acctID),"dd/MM/yyyy")

Yes, {@dateinrange} is a detail level formula.

-LB

 

[reidtw]

lbass,

That's great, I seem to have it working for a single date but when I run it for a range, say 18/03 to 21/03, only the 21st comes out.

I need to be able to show recs for an account over a period. For example, where an account may have a rec done on the 18th but not on the 21st, there should be two records, one with details of the rec on the 18th and one with the "No Reconciliation" warning for the 21st.

Also, talking about rec details, the total output will be: -

Account No
Type
Currency
Reconciliation Date
Date Performed
Ledger Balance
Bank Balance
Difference
UserName
ApprovedBy
ApprovedOn

Most of the fields will be derived from the RECON table but if I am using maximum, all I will get is the most recent values, for instance, if I select 18/03 to 18/03 the "Date Performed" comes out as 24/03 but it should be the 21st, similarly, the recon date for the 21st has the 24th but should be the 22nd.

Any suggestions welcome.

Many thanks
Trev

 

[lbass]

Okay, but not sure how you want to treat reconciliation dates outside of the parameter period. You need to allow for them, in order to ensure that all accounts are returned. Here's one way. Keep the left join with no record selection criteria, and use a detail level formula like the following {@recstatus};

if isnull({reconciliation.acctID}) then
"No Reconciliation" else
if not({reconciliation.date} in {?daterange}) then "Earlier Reconciliations Only" else totext({reconciliation.date},"dd/MM/yyyy")

Then go to the section expert->details->suppress->x+2 and enter:

distinctcount({@recstatus},{account.acctID}) > 1 and
{@recstatus} = "Earlier Reconciliations Only"

For each detail field, you should also go to format->field->common->suppress if duplicated->x+2 and enter:

{@recstatus} = previous({@recstatus}) and
{account.acctID} = previous({account.acctID})

I think this should give you "No Reconciliation" if there have never been any reconciliations, or it will give you the reconcilation dates within the period, or it will result in one record with "Earlier Reconciliations Only."

-LB

 

[reidtw]

Hi lbass,

I have tried your suggestions but to no avail!

I have been able to produce the desired results using a sub-report (oh no!). The main report is a list of all accounts from the accounts static table and I have put the reconciliation table in the sub report and joined them on account id.

However, there is one problem that is bugging me if you wouldn't mind thinking about a possible solution.

I have a shared number variable in my sub report to pass the account id to the main report. When I display it in a formula @varx (Shared NumberVar x, I get the following results: -

Account_ID @varx SubReport_Acct_id
1000 0
2000 0 2000
3000 2000 3000

Where 1000 does not have a reconciliation record but 2000 and 3000 do. You can see that the value of the variable is transposing down to the next account id.

What am I doing wrong?

Trev

 

[lbass]

The formula in the main report needs to be in a section below the one in which the subreport is located. If that is a problem for alignment, you can format the section in which the subreport is located to "Underlay following sections."

-LB

 

[reidtw]

lbass,

That's brilliant, working fine now, thanks for your time on this.

Trev