SubReport Loses Link... 1

[Barbara265]

I have a report that contains several sub-reports. This report retrieves benefit enrollments for employees. The main report contains employee level information (name, SSN, etc.) and the sub-reports each return data about a specific benefit enrollment for every employee. The sub-reports are linked to the main report via a unique employee level identifier. The original version of this report works just fine as it is, but I need to modify both the main report and all the sub-reports so the report returns data about a different group of employees in another company. This change involves a simple modification to the SQL statement in the main and all the sub-reports. Here is a snippet of the code that needs to be changed...

EBase.EbPSID = 'Q10' AND

This is one of several parameters in the WHERE clause of the SQL query, but is the only one I need to change. I modified it to read 'Q17' in the main report and all the sub-reports and saved the changes. When I run the report after making the changes it's as though the link between the sub-reports and main report are broken. I see multiple rows of data for an employee where there should only be one and/or I see the same amount returned for every employee rather than the actual employee amount.

I have tried relinking the sub-reports after I make the necessary changes, but that doesn't seem to fix the problem. Is there some standard way to modify reports that contain sub-reports so the linking doesn't become an issue?

Thanks.

 

[kenhamady]

I would avoid changing the SQL directly unless you have no choice. You should be able to make the same change in the selection formula which generates the WHERE clause.

Report, edit selection formula, record Ken Hamady

http://www.kenhamady.com/

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

 

[MalcolmW]

Quite often the Record Selection formula cannot be "compiled" into SQL by Crystal Reports, so the WHERE clause has little resemblance to what would be an efficient method of getting data. Editing the SQL statement is a good (but painful) way to learn the quirks of how Crystal Reports works. Perhaps you should wait until you are forced under threat of imminent danger before you start editing the SQL statement.
Using stored procedures with parameters would be more efficient, and would keep you away from editing the SQL statments. Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[Barbara265]

Ken,

Unfortunately there's little alternative than to directly change the SQL. Well, there's little alternative to someone who's had no formal training in Crystal anyway! Seriously, the database I'm running this report against is pretty messy. I often have no choice but to monkey with the code.

Thanks for your advice though. Maybe someday I can afford to invest in your training.

 

[Barbara265]

Malcom,

The client I'm writing this report for is adamant about NOT using stored procedures to base the report on. Though I would like to be more proficient in creating stored procedures. Is there a good book you could recommend?

 

[SMahoney]

did you try using the Info SQL designer to write the SQL and then base the report off that? That is what I do when I have to write SQL directly because writing it directly into the report causes problems.

 

[Barbara265]

I'm using Crystal 6.0. Does the Info SQL designer exist in this version? I'm entirely self-taught at Crystal so I'm sure there are plenty of avenues I've yet to explore.