Question

Crystal Reports - reads all records before applying filter

  • 12 October 2020
  • 6 replies
  • 1278 views
STMVALENTINE
Rank
Userlevel 3
Badge +8

I am looking at a Crystal Report that prompts for parameters to filter the report results, however, it seems to read all the records of the table before applying the filter and then producing the report.  It wouldn’t be a problem except it has almost 4 million records to read and makes the report take way too long. 

Any suggestions out there?

Thanks

~JV

~JV

6 replies

servetsc
Rank
Userlevel 2
Badge +5

Hi,

Is it a subreport or the report itself?

If it is the report itself you just have to create a query with the right “where” clause. If it is a subreport you can use the subreport link option.

Regards

Servet

 

STMVALENTINE
Rank
Userlevel 3
Badge +8

Thanks for your reply….

This is in the main report itself.  And I am using CR2016, and I have a parameter prompt for filter conditions….however…..when I ‘show SQL query’ it does not put in all the parameter filters.

Any additional help as to where and how I can make sure the SQL query is passing all the ‘WHERE’ conditions?

~JV

~JV
CallumW
Rank
Userlevel 6
Badge +15

It sounds like you have created a parameter, but haven’t actually linked it to anything?

You can use Select Expert - but I personally find it easier to use selection formulas.

 

 

Callum Wells - https://uk.linkedin.com/in/callum-wells
STMVALENTINE
Rank
Userlevel 3
Badge +8

Looks to me as though you have created a parameter, but you will need to link in the parameter to the record selection formula. 

 

 

 

 

I have looked at this and there is code there to use for the filter.  It just does not show when looking at the SQL query.  It’s like it has a mind of it’s own to exclude the code.  This is the problem when looking at existing reports created by someone else.  :( 

~JV
CallumW
Rank
Userlevel 6
Badge +15

If you could upload your report file in a zipped folder I could take a look.- or send me a message and ask for my Email address. (Albeit I will not be able to actually run the report if its using custom views/ials) (ensure all company related information is removed from formulas/text fields and ‘File → Save Data With Report’ is not selected before saving)

If this is not possible then the only other suggestions are as follows:.

  1. Check all database links are correct and Crystal Reports hasn’t automatically added a link which isn’t correct.
  2. File → Report Options  & select ‘Convert Database NULL Values to Default’ and see if that makes a difference
  3. Check the selection formula to make sure it makes sense with all brackets in the correct places.

 

Callum Wells - https://uk.linkedin.com/in/callum-wells
STMVALENTINE
Rank
Userlevel 3
Badge +8

Thanks for your responses.  I am going back to find out the report needs and might just attempt to create a new one or just a SQL query.  The CR was a mess and half the data didn’t make sense to me anyway.

~JV

Reply


Terms & ConditionsCookie settings