Report dropping accounts

[travelerII]

I am using Crystal 10 and I am constructing a trial balance by account and dimension.

I am using a sub report to get the opening balance for each account and then in the main report I sum the transactions by account+dimension add those to the sub report opening balance to get the current balance using the following formula.
{@Shared Var Opening Balance}+Sum ({@Amount}, {@Account and Dimension})

All that works fine except there are transactions I need to exclude from the report. In this case transaction whose number starts with "NP". I do this with the select expert which uses the following formula

not ({GACCENTRYD.NUM_0} startswith "NP")

The problem is that in two accounts, in this case 3000 and 3200, the only transactions are ones that start with "NP". This is causing my report to drop both 3000 and 3200 I assume because there are no transactions even though there is an opening balance from the sub report that should be reflected.

I have tried a number of approaches to fix this but have been unsuccessful.

Any thoughts would be greatly appreciated.

 

[lbass]

Instead of using the select expert, build the condition into your amount formula (whatever it is--you should always show the contents of your formulas).

-LB

 

[Madawc]

As lbass says, it is better to use Report > Selection Formulas > Record rather than letting Select Expert do it. But I don't think that's you main problem.

If I've understood it correclty, you' ve got the clssic problem of wanting records from table A along with corresponding records from table B, but you still want table A records with nothing on table B.

That much you can solve by going Database > Datebase Expert [Links] and changing the link to a Left Outer. (Or you may know this already, but if so you should have mentioned it.)

The extra complication is that you don't want records that start 'NP'. If it does the test when there is no record, it hits a null and stops. You can overcome this by something like

IsNull({GACCENTRYD.NUM_0})
or Left({GACCENTRYD.NUM_0}, 2) <> "NP"

I've used a different method for selection: startswith would probably also work. Try removing collection and displaying @Selection as a report field, which should show True or False You could then simply say @Selection in the record selection to get the rule applied.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[travelerII]

Thanks for the help. Got it working.