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
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