Crystal Reports Select Expert Formula not working

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Select Expert Formula not working

Author	Message
carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Topic: Select Expert Formula not working Posted: 08 Jun 2009 at 1:15pm
	CR8.5 The report has (3) parameters: ?DateRange ?CustID (default = All Customers) ?PartID (default = All Parts) SELECT EXPERT FORMULA: {WORK_ORDER.CLOSE_DATE} >= Minimum({?DateRange}) and {WORK_ORDER.CLOSE_DATE} <=Maximum({?DateRange}) and If {?PartID}='ALL Parts' Then {CUST_ORDER_LINE.PART_ID} like '' Else {CUST_ORDER_LINE.PART_ID} = {?PartID} and If {?CustID}='ALL Customers' Then {CUSTOMER_ORDER.CUSTOMER_ID} like '' Else {CUSTOMER_ORDER.CUSTOMER_ID} = {?CustID} when {?CustID} = All Customers and {PartID} = a single part THE REPORT WORKS when {?CustID} = a single ID and {PartID} = All Parts THE REPORT RETURNS ALL PARTS FOR ALL CUSTOMERS
	IP Logged


hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 08 Jun 2009 at 1:24pm
	I would take out the If's and change it like this: {WORK_ORDER.CLOSE_DATE} >= Minimum({?DateRange}) and {WORK_ORDER.CLOSE_DATE} <=Maximum({?DateRange}) and ({?PartID}='ALL Parts' or {CUST_ORDER_LINE.PART_ID} = {?PartID}) and ({?CustID}='ALL Customers' or {CUSTOMER_ORDER.CUSTOMER_ID} = {?CustID}) Note where I put the parentheses - you've got to have them or this won't work. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Posted: 08 Jun 2009 at 1:42pm
	thank you but can you explain how/why this works? how does it know the meaning of "ALL Parts" and ALL Customers"?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Jun 2009 at 1:59pm
	If Hilfy's suggestion does not work I would leave your original but use the parentheses the way Hilfy was. It has worked fine for me that way in the past... {WORK_ORDER.CLOSE_DATE} >= Minimum({?DateRange}) and {WORK_ORDER.CLOSE_DATE} <=Maximum({?DateRange}) and (If {?PartID}='ALL Parts' Then {CUST_ORDER_LINE.PART_ID} like '' Else {CUST_ORDER_LINE.PART_ID} = {?PartID}) and (If {?CustID}='ALL Customers' Then {CUSTOMER_ORDER.CUSTOMER_ID} like '' Else {CUSTOMER_ORDER.CUSTOMER_ID} = {?CustID}) Although I think I would have changed your date parmeter into two fields Begin date and End date and then use an in statement for the first part... {WORK_ORDER.CLOSE_DATE} in {?Begin Date} to {?End Date} and (If {?PartID}='ALL Parts' Then {CUST_ORDER_LINE.PART_ID} like '' Else {CUST_ORDER_LINE.PART_ID} = {?PartID}) and (If {?CustID}='ALL Customers' Then {CUSTOMER_ORDER.CUSTOMER_ID} like '' Else {CUSTOMER_ORDER.CUSTOMER_ID} = {?CustID}) if that does not work you can reverse the logic and make the else statement a little odd as just equal to itself which also should work... {WORK_ORDER.CLOSE_DATE} in {?Begin Date} to {?End Date} and (If {?PartID}<>'ALL Parts' Then {CUST_ORDER_LINE.PART_ID} = {?PartID} else {CUST_ORDER_LINE.PART_ID}={CUST_ORDER_LINE.PART_ID}) and (If {?CustID}<>'ALL Customers' Then {CUSTOMER_ORDER.CUSTOMER_ID} = {?CustID} else {CUSTOMER_ORDER.CUSTOMER_ID}={CUSTOMER_ORDER.CUSTOMER_ID}) Edited by DBlank - 08 Jun 2009 at 2:01pm
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 09 Jun 2009 at 7:15am
	My formula works because Crystal replaces the parameter notation with the value of the parameter. So, it's actually comparing two literal strings instead of comparing a string against data. So, for example, if {?PartID} is "ALL Parts" the actual SQL sent to the database will be: ('ALL Parts'='ALL Parts' or {CUST_ORDER_LINE.PART_ID} = 'ALL Parts') Statements with OR in them are only evaluated until the first "True" occurs. Since both strings are the same, it evaluates to True and the database moves on to the next line of the where clause. If {?PartID} is a part number (I'll use "Part1" here) the line will be: ('Part1'='ALL Parts' or {CUST_ORDER_LINE.PART_ID} = 'Part1') Since the first part of the OR statement is false, the database will go on to look for records where the PART_ID value is 'Part1'. Make sense? -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 09 Jun 2009 at 7:36am
	Thanks for explaining that Hilfy. If I understand this correctly this was a much more elegant and efficient way of how I was trying to get a "True" value (the clumsy table.field=table.field part of my formula) that prevents filtering, the same as selecting *, and still allow for an AND statement between the paremeter select options. Is this a correct interpretation?
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 09 Jun 2009 at 7:41am
	That is correct! Also, I'm not sure if your database will evaluate If statements in a where clause. If it doesn't, then with your original formula all of the data would have been selected for the report and then Crystal would do the filtering. This can significantly increase the amount of time it takes to process a report. Using the technique I've shown you means the database can work the filter which could make the report run faster. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

carstowal Groupie Joined: 31 Jul 2008 Online Status: Offline Posts: 80	Posted: 12 Jun 2009 at 6:41am
	THANK YOU BOTH
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum