Why does the selection criteria change from the order that I place it?

[AndersonCJA]

I am using Crystal reports XI, sql server 2005 db
why won’t the selection criteria stay in the order that I place it? For example, when I save my report, exit and re-open, line 2 will be at the bottom.
I change it back to what it is below, but it will not stay that way.

Line 1:
(isnull({table1.field1}) or {table1.field1} = "N" or {table1.field1} = " ") and
Line 2:
(NOT ISNULL({table1.field2}) OR {table1.field2} > DATE(0,0,0)) and
Line 3:
(isnull({table2.field1}) or {table2.field1} = 1) and
Line 4:
{table1.field2} <= currentdate and
Line 5:
{@formula} = "Y"

Thank you

 

[lbass]

I think it is trying to optimize the processing. Are you using the select expert to do this? I think if you go to report->selection formula-> record and enter the formula there, it will stay in order. You might want to then go to database->show SQL query and see if it is still reordered in the actual query.

-LB

 

[AndersonCJA]

Great suggestion, I wish I would have thought of it,I tried it out, but sad to say it didn't work. Thank you though.

 

[lbass]

So you are saying that you entered the selection formula by going to report->selection formula->record and then when you ran the report and checked back, it was reordered in the selection formula area? Or do you mean in the "show SQL query"?

I don't think I've ever seen a formula change that I've manually entered, so I'm wondering whether if you start a new report and enter the selection formula, whether this would still happen, or whether it is an artifact of your previous use of the select expert.

-LB

 

[AndersonCJA]

Yes, I changed it in the Report just that way, using report - selection formula - record area.

then ran the report and checked back. The selection formula was ordered differently than it was before I closed the formula editor.

Using your suggestion, I created a report from scratch and used the record area first. It did the same thing.

To see what would happen, I took out the 'or' statements, I replaced them with if then else, I placed the statements that check for isnull at the top of the selection criteria, saved the report, exited, reopened the report and they stayed in order this time. I moved the statements around, saved, exited, reopened, and they stayed in place.