Very general question regarding another designer's selection formula

[elsenorjose]

Can someone shed some light on why one would use variables like this in a selection formula? I inherited a report with the following and it seems like a really convoluted way of doing things but maybe there's a good reason.

stringVar localPlantArea := "*";
stringVar localProcess := "*";
stringVar localProduct := "*";
stringVar localStatus := "*";
stringVar localIssueType := "*";
stringVar localOrgUnit := "*";
stringVar localOwner := "*";
if {?PlantArea} <> "" then localPlantArea := {?PlantArea};
if {?IssueType} <> "" then localIssueType := {?IssueType};
if {?OrgUnit} <> "" then localOrgUnit := {?OrgUnit};
if {?Owner} <> "" then localOwner := {?Owner};
if {?Process} <> "" then localProcess := {?Process};
if {?Product} <> "" then localProduct := {?Product};
if {?Status} <> "" then localStatus := {?Status};
//    
{V_QEI_QEX_ISSUE.DOC_ORG_ID} in {?OrgID} and
(if not(localIssueType="*")then {V_QEI_QEX_ISSUE.QEI_ITY_NAME}=localIssueType else true) and
(if not(localPlantArea="*")then {V_QEI_QEX_ISSUE.QEI_PLA_CODE}=localPlantArea else true) and
(if not(localOrgUnit="*")then {V_QEI_QEX_ISSUE.QEI_ORU_CODE}=localOrgUnit 
else true) and
(if not(localOwner="*")then {V_QEI_QEX_ISSUE.DOC_OWNER_CODE}=localOwner 
else true) and
(if not(localProcess="*")then {V_QEI_QEX_ISSUE.QEI_PRC_CODE}=localProcess 
else true) and
(if not(localProduct="*")then {V_QEI_QEX_ISSUE.QEI_PRD_NAME}=localProduct 
else true) and
(if not(localStatus="*")then {VH_QEI_QEX_ISSUE.DOC_STATUS}=localStatus 
else true) and
(if({?FromCreateDate} <> "" )then 
date({V_QEI_QEX_ISSUE.DOC_CREATED_DATE})>=date({?FromCreateDate}) else 
true) and
(if({?ToCreateDate} <> "" )then date({V_QEI_QEX_ISSUE.DOC_CREATED_DATE})
<=date({?ToCreateDate}) else true) and
(if({?FromCloseDate} <> "" )then 
date({V_QEI_QEX_ISSUE.QEI_CLOSED_DATE})>=date({?FromCloseDate}) else true) 
and
(if({?ToCloseDate} <> "" )then date({V_QEI_QEX_ISSUE.QEI_CLOSED_DATE})
<=date({?ToCloseDate}) else true)

 

[Turkbear]

Hi,
It appears he/she is parsing the parameters passed by the user and, depending on which ones are specified, restricting the record selection to those specific values, otherwise he is telling the query to return any value for that particular field.
Why he/she chose that particular method is probably a matter of personal preference or how it was learned.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

Try taking a look at the SQL Query, which you should find under 'Database' on the menus. This usually has a lot of the selction rules, though maybe not all of them.

Anything done at that level is done more efficiently, because the server does the work. Later seleciton is done by your own machine and tends to be slower.

I'd be interested to see how much of the selection comes through as SQL. Please post it.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[elsenorjose]

Interesting but no matter how many parameters I provide, all that's returned in the SQL is:

 SELECT "V_QEI_QEX_ISSUE"."QEI_ITY_NAME", "VH_QEI_QEX_ISSUE"."DOC_STATUS", 
"V_QEI_QEX_ISSUE"."DOC_DOCUMENT_NO", "V_QEI_QEX_ISSUE"."DOC_CREATED_DATE", 
"V_QEI_QEX_ISSUE"."DOC_OWNER_CODE", "V_QEI_QEX_ISSUE"."QEI_ORU_CODE", 
"V_QEI_QEX_ISSUE"."QEI_PRD_NAME", "V_QEI_QEX_ISSUE"."QEI_PRC_CODE", 
"V_QEI_QEX_ISSUE"."DOC_TITLE", "V_QEI_QEX_ISSUE"."QEI_CLOSED_DATE", 
"VH_QEI_QEX_ISSUE"."DOC_DOCUMENT_NO", "V_QEI_QEX_ISSUE"."DOC_ORG_ID", 
"V_QEI_QEX_ISSUE"."QEI_PLA_CODE"
 FROM   "SmartReport"."dbo"."V_QEI_QEX_ISSUE" "V_QEI_QEX_ISSUE" INNER JOIN 
"SmartReport"."dbo"."VH_QEI_QEX_ISSUE" "VH_QEI_QEX_ISSUE" ON 
"V_QEI_QEX_ISSUE"."DOC_DOCUMENT_NO"="VH_QEI_QEX_ISSUE"."DOC_DOCUMENT_NO"
 WHERE  "V_QEI_QEX_ISSUE"."DOC_ORG_ID"='0000000000000000000000000'
 ORDER BY "V_QEI_QEX_ISSUE"."QEI_ITY_NAME", 
"V_QEI_QEX_ISSUE"."DOC_DOCUMENT_NO"