cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports showing no records but returns SQL that returns records

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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}

abhilash_kumar
Active Contributor
0 Kudos

hi Rene,

Replace the '%' sign with '*'.

({VW_TASK_HISTORY_EFILE_REPORTS.SCBA_NM} like "*-EC" and

-Abhilash

Former Member
0 Kudos

You are awesome!!! Thank you so much!!! 

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks! I will give that a shot and see what I can come up with

Answers (0)