Selecting Between Two Different Tables

[Ragnarox]

Hello all,

My report needs to get information from 1 of 2 tables depending on a specific value. Whenever i place that second table into the else statement all i get is a blank report, except for my text objects. It just will not run with a table selection in my formula. Anyway here is my formula:

if {BillingMaster.Type} = "Long Term" then
   {LongTerm.LoanAmount}
Else
   {ShortTerm.LoanAmount}

I would think that Crystal would do this with no problem, but possibly my formula is incorrect, I am not sure.



Any help, as always, is greatly appreciated.

Brian

 

[synapsevampire]

Please post technical information:

Crystal version
Database/connectivity used
Example dtaa
Expected output

Are these tables joined? If not, than you misunderstand how Crystal works. You cannot have multiple unjoined data sources in a report, one dataset being returned at a time is all that Crystal can understand.

How to most easily allow for this is version dependent, so I'm sure I or someone will get you functioning once you post technical information.

-k

 

[Ragnarox]

synapsevampire,

I am using Crystal Reports 9 - Developer Version. I am connecting to a SQL 2000 server.

Idea of how the tables are set up. We have a customer table, a longtermpayment table, a shorttermpayment table, and then 3 billing tables. All the tables are connected to each other, with the customer table being connected to both of the payment tables.

So when we need to get a billing summary, we want to be able to sort it by type. The type is in one of the billing tables and so based on that value, i want to switch between the two payment tables.

I am not sure exactly what it is doing, because i get no errors, nor can i debug the formula.

Any help, as always, is greatly appreciated.

Brian

 

[synapsevampire]

Example data and expecte output would help.

I would test by placing the {ShortTerm.LoanAmount} field on the canvas without a formula, you may find that nothing is returned once you do this because the link isn't working to that table.

Try changing the joins to Left Outer joins, this should resolve, however I don't think that the {ShortTerm.LoanAmount} is returning anything so you'll get nulls for those as well.

Perhaps if you change to left outers and use:

if isnull({BillingMaster.Type})
or
{BillingMaster.Type} <> "Long Term" then
{ShortTerm.LoanAmount}
Else
{LongTerm.LoanAmount}

Hopefully you'll start seeing results then.

-k