Subreports and Shared Variables

[mgallot]

I have a Crystal 9.0 report that uses an ODBC into SQL Server 2000. There are 3 fields that I am using the INSTR function to search memo fields for certain strings. The problem is that 2 of the fields can have just one record associated with them, but the third field is in a table that can have many detail records associated with it. So the tables have a one to many relationship. How can I search the first table, keep track of the record id, then search the second table, but only display the record if the first search didnt find it. I have a main report that pulls the records by date, then 2 subreports that search the memo fields.

 

[Madawc]

I'd say you needed a formula in the group footer that checked them, testing for null values.

You didn't make it clear how much or how little you know about Shared Variables. The basics are:

To pass data back from a subreport, use a shared variable. For a date, put a formula field in the subreport like
Shared dateVar
V_Today := {LoadStatus.LastDLoad}
To access it in the main report, create another formula field with
Shared dateVar
V_Today := V_Today

If it was a currency value, you'd do it differently, e.g.
whileprintingrecords;
shared currencyvar SumSaved;
SumSaved:={#TotSaved};
SumSaved
And to access it in the main report, create another formula field with
whileprintingrecords;
shared currencyvar SumSaved;
SumSaved

Note that the shared variable is only available in the section after the section which contains the subreport.

Hope this helps.

Madawc Williams (East Anglia, UK)

 

[Naith]

Mgallot, I think you'd be best served by providing an example of your data which would illustrate what you mean.

 

[mgallot]

Sure. This is a Problem Mgmt system where users log problems. Each problem consists of

1. A callid field as the key in CallLog table
2. Calldescription and Closedescription memo fields in CallLog table
3. Multiple instances of a journalentry memo field in the Journal table

So each callid can contain many journalentry detail records. I need to create a 'search' report to search all of the memo fields for whatever string the user decides to enter. I have used Shared Variables, subreports, and the instr function. But I havent been successful in structuring this report to bring back the results from the journal table. The performance becomes so poor that users get bounced out of the system. I have created a main report that requires dates and search criteria as parameters, but only selects records based upon dates. Then I pass the search criteria (and dates) into the first subreport to bring back records that contain the string in the calldescription and closedecription. Then I have another subreport that gets passed the criteria and dates, but I'm having trouble with this piece.

 

[Naith]

As you already suspect, the reason your processing time has gone out the window is because you're executing a couple of subreports multiple times at a low level of the report.

But, unless I have misunderstood your point, I'm not convinced that you need a subreport at all, since you acknowledge that there is a relationship between all the tables concerned, and that you're passing the same criteria through each of the reports.

Let me know if there's any impact if you try the following:

Firstly, ditch the subreports.

Create a left outer join between the CallLog and Journal tables in the main report, as I presume it's plausible that a call can be opened and resolved with no Journal entries.

Group by CallID, suppressing the GroupHeader and details section.

Place the CallDesc and CloseDesc in the GroupHeader, and the Journal Entry in the details section.

Create the following formula:

WhilePrintingRecords;
StringVar CallDesc;
If InStr({CallLog.CallDesk},'string') > 0
Then CallDesc := {CallLog.CallDesk}//or a custom string
Else CallDesc := CallDesc;
[code]
Follow the same principle for the other two memos, placing the CallDesc and CloseDesc variables in the GroupHeader and the variable you create for the journal entries in the details. I'm assuming you don't need to return each journal entry that meets the InStr requirement, as opposed to the first or last instance, depending on how you order it. 

Then call the three variables in the GroupFooter. You can conditionally suppress the journal variable on whether the CallLog variables are null or not.

Finally, remember to reset the variables in a secondary group footer, or initial group header.

Naith

 

[mgallot]

I am going to try this technique. I just wanted to say, this sounds brilliant! Thanks for spending time thinking about this. I will soon post my results.

 

[mgallot]

This works like a charm! You Rock! Thanks so much.