Filtering the data in Crystal Report vs Filtering the data in SQL Server

[Former Member]

Hi,

To boost the performance, I've 2 option
A) Filtering the data in Crystal Report
B) Filtering the data in SQL Server

Which one is the best? A or B

Need advice

[nscheaffer · Accepted solution]

I would recommend "B" whenever possible.  However, sometimes that is not possible such as when you want to make a parameter optional.

Noel

[Former Member · Accepted solution]

Hi Noel Scheaffer,

'However, sometimes that is not possible such as when you want to make a parameter optional.'

Can you elaborate?

[nscheaffer · Accepted solution]

Sure.

When I am creating reports pulling data from a SQL Server database I always create command object and put my SQL there.  If you aren't sure what a command object is or how to create one watch this YouTube video...

http://www.youtube.com/watch?v=BHNHsIUbcn8

As an example, let's say we have an Orders table with the following 4 columns and others I have not specified.

OrderNumber

OrderDate

OrderStatus

CustomerNumber

... (more unspecified columns)

You could have the SQL in the command object be as simple as this...

SELECT *

FROM Orders

And then create Parameters within the report.  However, if you do that the entire Orders table will be pulled into your Crystal Report and then filtered with respect to any parameters and their values that you have specified.  For a small table that would probably be fine, but if you have a large table or numerous table joined together that could become a significant performance problem.

Therefore, I would recommend creating command parameters within the command object dialog box for StartDate and EndDate so you can limit the records found based on OrderDate.  For example, here is the StartDate parameter.

Do the same thing for EndDate and then you can limit the data that comes back to your Crystal Report with a SQL statement like this...

SELECT *

FROM Orders

WHERE OrderDate BETWEEN {?StartDate} AND {?EndDate}

Now for the part that I said wasn't possible.  Let's say I also want to have the CustomerNumber is a parameter I can do that within the command object.  However, I cannot make that CustomerNumber parameter optional.

My solution to this is hybrid approach.  I define the StartDate and EndDate parameters within the command object to limit the amount of data returned to the report.  I then create CustomerNumber as an optional within the report.

You then would need to put the following statement in the report as a record selection forumula...

Not HasValue ({?CustomerNumber}) Or {Command.CustomerNumber} = {?CustomerNumber}

You can do this two ways; via either of the following menu paths.

Report\Select Expert\Record...

Report\Selection Formulas\Record...

Hopefully this makes sense and helps you with your report.

Noel