Left outer Join option

[dfbonilla]

Reporting from two files Chart of accounts LEFT and GL Transactions RIGHT in both CR 8.5 and XI , even if I don't have a GL transaction I will need to report the chart because it may have previous balance. Example

Account, AcctName, Prev Balance, Tran Date, tranAmount New Bal

1000 ABC Bank 1500 01/01/05 $1.000 $2.500
1010 XYZ Bank 500 $500

As soon as I make a selection in the GL Transaction like Beg-end Date It will join and exclude the second record. Even If I select left outer join between links in crystal the second record will never show. The only way I have been able to make it work is through a subreport where the main report is Chart of accounts (reporting all charts) and the subreport is GL transactions Reporting linked Trans only), but when I get the information about the balances in the subreport is too late to suppress print becuase is in the same section that the subreport runs. If I create a sub section beneath subtotalB it show the correct information from a shared variable in the subreport but I can't go back and suppress a previous section where I am pirinting and where the subreport is located.

when getting no previous balance and no transactions I Need to suppress, Example:

1050 MY Bank 0 0 0 0


Thank you,

Diego

 

[synapsevampire]

When you say "As soon as I make a selection in the GL Transaction like Beg-end Date", does this mean use the reocrd selection criteria to define filtering criteria?

This would make sense since you would be explicitly stating that you want only rows that contain that criteria.

You might try posting technical information:

Crystl version
Database/connectivity used
example data
expected output

including fields/data so that we have a better understanding of what you have and need.

-k

 

[dfbonilla]

Is simple. chart of accounts contains account number, name and previous balance of the month. GE Transactions contain all the trans for giving dates. The ABC bank can have 100 transactions for january 2005 and 68 Trans for FEB 2005, etc. Because I am reporting a month i will have to select date from Jan 1th to Jan 31st.

In an standar inner join chart 1000 for bank ABC will report because there are trans for January but "My bank" will get excluded because it has no trans for this same month.

I tought that left outer join in crystal will fix this, report form the left table even there a no transactions in the right table but it doesn't. As soon as I select a date in the right table no matter the join (full or left) will exclude all left table trans not in right.

Thank you,

Diego

 

[lbass]

You could use a left join from the Chart of Accounts table to the Transaction table, but do not use the date criteria or any other criteria that references the Transaction table. Instead, create a formula like the following:

if isnull({transaction.acctno}) or
not({transaction.date} in {?daterange}) then 0 else{transaction.amount}

This should return all banks regardless of transactions during the month in question, and you can suppress the detail section when there are zeros for both previous balance and new balance.

-LB