on 11-09-2015 3:18 PM
I have a report that I have made changes to, and when I put in the parameters, no records appear in the report except for one selected parameter.
But each selection returns an SQL query that will return records when ran in TOAD.
For example, three different parameters (select only one at a time):
xSelect
ySelect
zSelect
When I select xSelect in the parameters, records appears in Crystal. SQL returns same in TOAD. Number of records in both is 7.
When I select ySelect in the parameters, no records appear in Crystal, but it does give the SQL. SQL returns 31 records in TOAD.
When I select zSelect in the parameters, no records appear in Crystal, but it does give the SQL. SQL returns 17 records in TOAD.
Any help or ideas would be appreciated.
A couple of questions:
1. Which version of Crystal - please go to Help>>About and get the exact version number.
2. Are the parameters single-select or multi-select?
3. Please post the formula from the Select Expert to show us how you're using the params.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Crystal Reports 2011 Version 14.0.9.1446 RTM
Parameters are single-select.
Parameters :
PCIO-EC not displaying records
PCIO-IC not displaying records
FARM displaying records
(If {?Account Type}= "PCIO-EC"
Then
({VW_TASK_HISTORY_EFILE_REPORTS.SCBA_NM} like "%-EC" and
{VW_TASK_HISTORY_EFILE_REPORTS.ACNT_TYPE_NM} = "PCIO" and
{VW_CURENT_TASK_LOCATION.ACNT_PREFIX_NM} = "ACP")
else If {?Account Type}= "PCIO-IC"
Then
({VW_TASK_HISTORY_EFILE_REPORTS.SCBA_NM} like "%-IC" and
{VW_TASK_HISTORY_EFILE_REPORTS.ACNT_TYPE_NM} = "PCIO" and
{VW_CURENT_TASK_LOCATION.ACNT_PREFIX_NM} = "ACP")
else If {?Account Type}= "FARM"
Then
({VW_TASK_HISTORY_EFILE_REPORTS.ACNT_TYPE_NM} IN ["PCIO", "BOAT"] and
{VW_CURENT_TASK_LOCATION.ACNT_PREFIX_NM} <> "ACP"))
and
{VW_TASK_HISTORY_EFILE_REPORTS.UW_TYPE} = {?UW TYPE} and
{VW_TASK_HISTORY_EFILE_REPORTS.TASK_EVENT_TYPE_ID} = 140.00 and
{VW_CURENT_TASK_LOCATION.TASK_STTS_CD} <> "C" and
{VW_CURENT_TASK_LOCATION.TASK_STTS_CD} <> "Q" and
{VW_CURENT_TASK_LOCATION.ACNT_TYPE_ID} <> 3.00 and
{VW_CURENT_TASK_LOCATION.TASK_TYPE_NM} <> "Audit" and
........
{REF_STATE.STATE_ABR_NM} = {?State_Abbr}
Also, I tend to not use If..Then..Else in the selection criteria - frequently it will cause all data to be pulled into memory and filtered there, where it can significantly slow down report processing. Instead I'll modify the formula to remove the If statement, causing the report to push the filter down to the database where it can be processed more efficiently. I would go to Database >> Show SQL Query to see whether this filter is being pushed to the database. If it's not. you might change the formula to something like this:
(
({?Account Type}= "PCIO-EC" and
{VW_TASK_HISTORY_EFILE_REPORTS.SCBA_NM} like "*-EC" and
{VW_TASK_HISTORY_EFILE_REPORTS.ACNT_TYPE_NM} = "PCIO" and
{VW_CURENT_TASK_LOCATION.ACNT_PREFIX_NM} = "ACP"
)
OR
({?Account Type}= "PCIO-IC" and
{VW_TASK_HISTORY_EFILE_REPORTS.SCBA_NM} like "*-IC" and
{VW_TASK_HISTORY_EFILE_REPORTS.ACNT_TYPE_NM} = "PCIO" and
{VW_CURENT_TASK_LOCATION.ACNT_PREFIX_NM} = "ACP"
)
OR
({?Account Type}= "FARM" and
{VW_TASK_HISTORY_EFILE_REPORTS.ACNT_TYPE_NM} IN ["PCIO", "BOAT"] and
{VW_CURENT_TASK_LOCATION.ACNT_PREFIX_NM} <> "ACP"
)
)
and
{VW_TASK_HISTORY_EFILE_REPORTS.UW_TYPE} = {?UW TYPE} and
{VW_TASK_HISTORY_EFILE_REPORTS.TASK_EVENT_TYPE_ID} = 140.00 and
{VW_CURENT_TASK_LOCATION.TASK_STTS_CD} <> "C" and
{VW_CURENT_TASK_LOCATION.TASK_STTS_CD} <> "Q" and
{VW_CURENT_TASK_LOCATION.ACNT_TYPE_ID} <> 3.00 and
{VW_CURENT_TASK_LOCATION.TASK_TYPE_NM} <> "Audit" and
........
{REF_STATE.STATE_ABR_NM} = {?State_Abbr}
-Dell
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.