MAS90 Outer Joins 1

[janemaritz]

I'm a newbie to Crystal and MAS90, but have done a lot of report writing and development in the past.

Is it true that you can use only one left outer join with these programs per report? If so, how can you write decent reports?

Example: I need a report that shows all vendors [vendor table], the payment amount [payment table], the 1099 amount [1099 table], and a comparison between the two amounts, with differences highlighted. A vendor may have a payment amount with no 1099 amount, or a 1099 amount with no payment amount (1099 adjustment).

With a normal reporting solution, I would use a left outer join from the vendor table to the two other tables. Since I can't do this, I've tried using a subreport for the payment amount. However, this doesn't allow for calcuations, so I can't compare the two amounts and highlight the lines with differences.

Help!

 

[kenhamady]

Do you get an error when you try the two left outer joins? I am surprised, since many databases will allow more than on outer join.

You CAN do calculations from a subreport, but you have to use shared variables. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lyanch]

One thing that might not be obvious, once you have an outter join to a table, you must have an outer join to any tables that join to that table.

ie.

Customer -> Contact -> PhoneNumbers

if you have an outer join for Customer->Contact you must also have an outter join for Contact->PhoneNumbers.

I use multiple outer joins and have no problems...

Lisa

 

[janemaritz]

Thanks for your replies.

This issue is is not universal with all databases, but doesn't work for MAS90. I don't get an error message with multiple outer joins, but the generated SQL statement FROM section is incorrect, and I get database errors if I try to change the SQL directly (not due to incorrect SQL). I've seen this mentioned as a MAS90 issue at least twice: see

http://www.habinc.com/iscg/MAS90_Tips.htm#LinkRules

section Simba and ProvideX Engines for Crystal Reports if you're interested.

Ken, you may have pointed me in the right direction with subreport formulas; if I pass through one of the amounts I can do the calculation in the subreport, which may be the best option. Is there any way to:
*Do the calculation in the main report?
*Format a section with shading on the main report based on the subreport data?
*Get report subtotals and totals of the subreport data on the main report?

Thanks again,
Jane

 

[kenhamady]

Shared variables will allow you to bring a value back to the main report for calculations, and you can use these to format sections. But the calculations have to occur after the subreport is run (physically below the subreport). If you are trying to bring back a series of subtotals, things get more complicated and you are going to need a separate variable for each subtotal, or possibly an array of variables.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[dgillz]

Jane is correct, I have seen this limitation is Mas90 and BEST software acknowledged it was an issue. Ken's solution is the best idea I can think of. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[janemaritz]

I'm hoping this is a simple issue:

I need to calculate totals of the fields that I'm using shared variables for. I'm told that shared variables must be executed while printing records, and then that I can't calculate totals of formulas that are executed while printing records. Is there a way around this so I can calculate the totals?

Thanks,
Jane

 

[kenhamady]

You have to use running totals with variables. see faq149-182. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com