Yet another Default Parameter Question 3

[aaronburro]

I more or less know how to create default parameters OK, with the exception of parameters that allow multiple discrete and range values at the same time. But thats not my question (sneaky, aint i?).

My question is how to create a default parameter selection that will still get passed through to the SQL query. Here is what I normally do:

(
if {?Product Line} <> "ALL" then {ARO_InvHistoryDetail.SOProductLine} = {?Product Line}
else true
)

But, when I look at the records being read, I can tell that some are being excluded. A peak at the SQL Query shows:

SELECT
ARO_InvHistoryDetail."SOItemNumber", ARO_InvHistoryDetail."SOWhse", ARO_InvHistoryDetail."SOProductLine", ARO_InvHistoryDetail."SOQtyShipped", ARO_InvHistoryDetail."SOUnitPrice", ARO_InvHistoryDetail."SOUnitCost", ARO_InvHistoryDetail."SOMiscItemCode",
IM1_InventoryMasterfile."StdUM", IM1_InventoryMasterfile."Weight", IM1_InventoryMasterfile."Category1",
ARN_InvHistoryHeader."InvoiceNumber", ARN_InvHistoryHeader."InvoiceType", ARN_InvHistoryHeader."InvoiceDate", ARN_InvHistoryHeader."SOShipToCity", ARN_InvHistoryHeader."SOShipToState", ARN_InvHistoryHeader."SOShipToZipCode", ARN_InvHistoryHeader."SOShipViaRate",
AR1_CustomerMaster."CustomerNumber", AR1_CustomerMaster."CustomerName"
FROM
"ARN_InvHistoryHeader" ARN_InvHistoryHeader,
"AR1_CustomerMaster" AR1_CustomerMaster,
{ oj "ARO_InvHistoryDetail" ARO_InvHistoryDetail LEFT OUTER JOIN "IM1_InventoryMasterfile" IM1_InventoryMasterfile ON
IM1_InventoryMasterfile."ItemNumber" = ARO_InvHistoryDetail."SOItemNumber"}
WHERE
ARO_InvHistoryDetail."InvoiceNumber" = ARN_InvHistoryHeader."InvoiceNumber" AND
ARO_InvHistoryDetail."HeaderSeqNumber" = ARN_InvHistoryHeader."HeaderSeqNumber" AND
ARN_InvHistoryHeader."Division" = AR1_CustomerMaster."Division" AND
ARN_InvHistoryHeader."CustomerNumber" = AR1_CustomerMaster."CustomerNumber" AND
ARO_InvHistoryDetail."SOQtyShipped" <> 0 AND
ARN_InvHistoryHeader."InvoiceType" = 'I' AND
ARN_InvHistoryHeader."InvoiceDate" >= {d '2005-01-01'} AND
ARN_InvHistoryHeader."InvoiceDate" <= {d '2005-12-31'}
ORDER BY
ARN_InvHistoryHeader."SOShipToZipCode" ASC,
AR1_CustomerMaster."CustomerNumber" ASC,
ARO_InvHistoryDetail."SOProductLine" ASC,
ARN_InvHistoryHeader."InvoiceNumber" ASC

and sure enough, the product line did not get limited Is there any way to get around this in Crystal8.5 using MAS200 4.0?

 

[BobSuruncle]

Have you tried:
{?Product Line} = "ALL" OR {ARO_InvHistoryDetail.SOProductLine} = {?Product Line}


Bob Suruncle

 

[lbass]

The standard approach to pass parameter values to the SQL (per SynapseVampire, rosemaryL, et al.) is the following:

(
if {?Product Line} <> "ALL" then
{ARO_InvHistoryDetail.SOProductLine} = {?Product Line} else
if {?Product Line} = "All" then
true
)

SynapseVampire has an FAQ on maximizing passing to the SQL which you might want to read.

-LB

 

[IdoMillet]

While

{?Product Line} = "ALL" OR {ARO_InvHistoryDetail.SOProductLine} = {?Product Line}

is correct, I found that in order to ensure the condition gets passed to the WHERE clause, you should switch the order of the clauses:

{ARO_InvHistoryDetail.SOProductLine} = {?Product Line} OR {?Product Line} = "ALL"

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[aaronburro]

dangit. now I am mad at myself, cause I knew that . i lose a gold star for the day thx guys!

 

[aaronburro]

interestingly enough, the "OR" method doesn't seem to pass through...

 

[aaronburro]

sorry, the submit post button got clicked before I was finished...

Yeah, I tried the "OR" method and it doesn't pass through. I made sure to get the order as IdoMillet suggested, and that didn't seem to help... weird.

The if-else method still works, though.