Is a conditional link to a table possible?

[lbass]

Hi,

I might not be using the right terminology here. I am working with a complex database with dozens of tables, and I have a report which uses ten or twelve tables. The report selects customers based on table_1 and I also have a parameter which asks whether the user wants to see all customers (those represented in table_1) or only those who have received a service since the beginning of the fiscal year (with services and related dates in table_2 or in table_3). Table_2 and/or Table_3 fields may be null, and tables are linked with left joins from Table_1.

The parameter works fine, but no matter how I set it up in the select statement, the report always reads from Table_2 and Table_3. With thousands of records in these tables, the report is dramatically slowed. If the user selects "All" for ?CustomerstoInclude, I would like the report to act as though Table_2 is not in the report for speed.

The report works speedily enough when I do not include Table 2 at all, but I would like to build in the option that I mentioned. I have tried different kinds of if/then statements in the select statement and currently am using the following:

(if {?customerstoinclude} = "All" then true else
if {?customerstoinclude} = "Current" then
(if not isnull({Table_2.date}) then
{Table_2.date} in Date(?FiscalYear-1, 07, 01) to Date({?FiscalYear, 06, 30)) or
(if not isnull({Table_3.date} then {Table_3.date} in Date (?FiscalYear-1, 07, 01)to Date (?FiscalYear, 06, 30)))

So, is there a way to "skip" Table_2 and Table_3 altogether in the "All" scenario? Alternatively is there a way to limit the reading of fields in these tables to one incidence that meets the criterion, i.e., to tell the report to stop reading records when the criterion is first met? I tried maximum functions, but that requires reading all date records I think.

Thanks in advance for any help!

-LB

 

[ardentdev]

I don't think you're going to be able to ignore table2 and table3 based on the parameter entered if you build the query in Crystal Reports. Of course this is easily done using a stored procedure. Have you considered doing that? I'm not sure if that is an option for you. Depending on the database being used, it may be your best bet. It will speed things up.

Also, you might want to confirm that the database is indexed to effiently execute your query. The fields in the join should be indexed to increase performance.

 

[lbass]

Thanks for responding, Ardentdev. Stored procedures are not an option, unfortunately. The fields in the joins are indexed. Maybe there is no better solution? Just thought I'd ask...

-LB

 

[Naith]

Hey LB,

With your current query, Crystal is effectually saying, "Just bring back everything, and I'll sort out the relevant stuff." That's responsible for a lot of your slowdown, and also the reason why the excess tables are always in the equation.

Ideally, you want to make sure your query passes through to the database, then the database will know whether to dump or utilise the excess tables. This should speed up your report for a couple of reasons; (a) only the relevant data should be returned, not excessive rows, and (b) most of the processing is taken away from Crystal and done on the database.

Try this on for size:

(If {?customerstoinclude} = "All"
Then True
Else
(If {?customerstoinclude} = "Current"
Then
(if not isnull({Table_2.date}) then
{Table_2.date} in Date(?FiscalYear-1, 07, 01) to Date({?FiscalYear, 06, 30)) or
(if not isnull({Table_3.date} then {Table_3.date} in Date (?FiscalYear-1, 07, 01)to Date (?FiscalYear, 06, 30))))

I'm writing this in notepad without any Crystal around - you might need to add a closing bracket somewhere - but the idea is to wrap each of your conditions in parentheses, so that they are independently considered. 'Kay?

All the best with your report,

Naith

 

[lbass]

Hi Naith,

I was hoping I could write back and say "All for the want of a pair of parens"--but alas, the report is still reading all of the Table 2 and 3 records, though somewhat faster, I think, after adding the additional parentheses. In a sample report with limited records, without selecting any Table 2 or 3 criteria, there are 26 records, but adding the criteria results in 210 records, even when I choose "All."

I also wonder whether it makes any difference to speed if I use the if-then statement in the record select or in the group select. I thought the record select might be faster, even though both ways appear to involve local evaluation, but maybe it's all the same ball of wax...

I really do appreciate the time you put into responding. Thanks again.

-LB

 

[Naith]

Okay, let's take another look at this.

When you arrange your record selection criteria to resemble this, what does the SQL being passed look like?

(
If  {?customerstoinclude} = "Current"
Then
	(if not isnull({Table_2.date}) then
	{Table_2.date} in Date(?FiscalYear-1, 07, 01) to Date({?FiscalYear, 06, 30)) or
	(if not isnull({Table_3.date} then {Table_3.date} in Date (?FiscalYear-1, 07, 01)to Date (?FiscalYear, 06, 30))))
Else
If {?customerstoinclude} = "All"
Then True
)

Can you confirm that there are no Table2 fields placed in your report, too? Because if there are, this will make the table's inclusion mandatory.

Naith

 

[lbass]

Hi Naith,

Sorry to confuse things, but it's really only Table_2 I want to avoid. {Table_3.date} is already in the details section for another reason. There are no other references to Table_2 fields in any other part of the report. I tested whether adding Table_3 again (Table_3B) in a separate link to Table_1 would make a difference, but it didn't (except the report was even slower).

I also tried rearranging my formula to correspond to your recent one as follows, although I had to adjust the parentheses a little to make it work:
(
If {?Customers to Include} = "Current"
then
(if not isnull({Table_2.DATE}) then
{Table_2.DATE} in Date({?Fiscal Year }-1, 07, 01) to Date ({?Fiscal Year }, 06, 30)) or
(If not isnull({Table_3B.Date} then
{Table_3B.DATE} in Date ({?Fiscal Year }-1, 07, 01) to Date ({?Fiscal Year }, 06, 30)) else
if {?Customers to Include} = "All"
then true
) and
//rest of select formula.

The SQL query still shows the selection of Table_2 dates when I choose "All"--which I guess is the same as saying that this query does not change based on the parameter. Maybe I have to just accept the slowness of the report if I want to provide this option.

Thanks for the time you spent on this.

-LB

 

[Naith]

How is Table3 being linked, if it isn't already being linked to Table1? If it is being tacked onto Table2, then Table2 always has to be in the mix just to get to Table3.

Naith

 

[lbass]

Hi Naith,

Table_2 is left joined to Table_1 and Table_3 is left joined to Table_1. Would the version of my Crystal Reports make a difference to what I can do with the SQL query? I'm using 8.0.

Thanks.

-LB