Create form w/cond formatting - sections, multiple subreports

[BonnieSue]

We would like to create forms for lease contracts that will read off of one database, then, have multiple subreports with different paragraphs containing bookmarks from a different database and each subreport section will have conditional formatting suppressing the "wrong" paragraphs and only pulling the correct one. We would also build parameters in the report prompting the end user to print a specific type of lease for a particular customer.

I am using Crystal Reports 8 and we are also on SQL Server 7 with SP2. I have alot of Crystal report writing experience; but no "programming" experience with the exception of writing SQL scripts to correct problems in our accounting software database. I can do some conditional formatting, parameters, and basic subreports.

First off, can this be done with Crystal and if so, what is the best way to do it?

 

[marckssg]

Instead of using subreports why don't you use the Insert Section Below function.

Using this you can insert say 4 detail sections into your report. Then use conditional formatting either based on a value in the database or from a Parameter field to display the relevent section.

To use the Section Below Function, in the design screen right click on the gray section of the screen that contains the names for each section, then select the function from the short cut menu that appears.

Hope this helps

Cheers

Marc

 

[BonnieSue]

I thought subreports was the way I would have to go when I need to pull information from different databases on the same form?

 

[jazerr]

You only NEED subreports if you are using stored procedures to query the database. Otherwise you only need to insert sections.

In answer to your first question, yes it can be done, fairly easily. The syntax to use in the suppress is :

IF ISNULL({table.field}) THEN TRUE ELSE
IF {table.field} = '' THEN TRUE ELSE FALSE

that will work if you are basing the suppression on a field being null. If you are basing it on a value the syntax is as follows:

IF {table.field} = 'ValueBAD' THEN TRUE ELSE
IF {table.field} = 'ValueWorse' THEN TRUE ELSE
IF {table.field} = 'ValueGood' THEN FALSE ELSE TRUE

 

[kenhamady]

I am not sure I would say that you only use subreports for Stored Procedures, there are many other times that you need to go to subreports.

If you can link the other tables to the main table without mucking up the report through table inflation or join problems, then you can use split sections as described.

However, when the link causes problems or is impossible, then you need subreports. Linked subreports are much slower.




Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[FowlerConsulting]

"have multiple subreports with different paragraphs containing bookmarks from a different database and each sub report section will have conditional formatting suppressing the "wrong" paragraphs and only pulling the correct one."

Why don't you author a stored procedure (SP) in the DB that accepts from the report a type of lease parameter? Encapsulated within the SP you would filter out the unwanted paragraphs.

Pushing the business rules of the report to the DB will promote re-use and increase performance. Sub-reports process as a separate report, and thus a root report with 4 sub reports (used for aggregation) would process the table relationships, and where clause 5 times- ouch!

Steven Fowler
steven_m_fowler@yahoo.com

http://www.fowlerconsulting.com