Select Expert Changes Order

[Sange]

Hi,

I'm currently developing a report (CRv10) which includes two parameters; date range and region. The region parameter relies on a formula and ideally the report would first search the date range before bringing back the regional information.

I find however, that whilst I make sure the date range appears first in the select statement, it tends to swap around in order and ends up looking like this: {@Region} = {?Region} and {ARRESTS.COMMITMENT_DATE} = {?Date Range} instead of: {ARRESTS.COMMITMENT_DATE} = {?Date Range} and {@Region} = {?Region]. I'm not sure why it does this but am I to assume that the report will first sort/select the region information and then the date range. If this is in fact the case, how do I get it to search the date range first?

Any assistance would be appreciated.

 

[dgillz]

Do you want to SORT by date first, or SELECT by date first? This is not clear from your post.

Regardless of the order of the record selection formula, this will NEVER affect teh way the report is sorted. First of all, if the report is grouped in any way, that will be the primary sort. Adiitional groups, if any will be secondary, tertiary, etc.

Beyond grouping, the report will sort based on the way you define the sorting in the sort expert. Have you defined a certain sort order?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Sange]

Thanks for your response.

I was actually hoping to select by date first and then secondly by region. My select statement originally had the date parameter first and then the region parameter but it then swapped around and everytime I try to change it reverts back.

My only concern is that because the region information appears first in the select statement that the report will select the regional information first and then the date. The database I deal is quite large (ie. 10,000 records a month) and as a result, I really need to have records returned first based on the date range parameter.

I hope this is clearer.

 

[TheBlondOne]

Hi there,

It doesn't matter what order the fields appear in in the Select Expert, this has not got any relevance as to how the report will look when previewed. The idea of the select expert is just to literally filter out any records it doesn't need. So when the SQL script is sent from Crystal to the SQL Server and then executed it won't have to retrieve everything from the database.

Like Dgillz say's its the grouping which will take priority in the published report and then any sort orders which you have specified.

HTH

Steve

 

[Sange]

Hi again,

I don't think I'm explaining it clearly enough for which I apologise.

I'm actually not talking about the way the actual report sorts it's records and I understand that the records will first sort by any groups that are included in the report.

What I'm interested in doing is forcing the report to select it's records based on the date range parameter first and then according to the region parameter which is actually a formula. In the select expert, the report initially did have the date range parameter first and then the region parameter second. For some reason however, this has now changed in order and my concern is that the records are first being returned based on the formula and then the date range, as this seems to have slowed the time it takes for the report to run.
Each time I go into the Select Expert and manually change the order back to date first and then region, Crystal Reports seems to swap it again.

Is this any clearer?

 

[lbass]

I think CR automatically optimizes the order of the SQL query, reordering as necessary. If your report is slow, you should check to make sure all selection criteria are appearing the "Show SQL Query". There is an FAQ on optimizing SQL queries that might be helpful.

-LB

 

[dgillz]

Don't use the select expert, it sucks and limits you anyway. Modify the record selection formula manually and you should be able to achieve what you want to do.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz