Parameters, selection criteria and dynamic fields.

[Flopper]

Crystal 8.5

Is the following possible:

Depending on the value selected within a specific parameter is it possible to change the table and field name required for selection:

For example:
------------------------------------------------------------
Switch (
{?AnalystType}="Action",{ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst}
,
{?AnalystType}="Incident",{ASSYST_USR.ASSYST_USR_SC}={?Analyst}
)
------------------------------------------------------------
If the user selects 'Action' within the {?AnalystType} parameter then hopefully only records with {ASSYST_USR_Action.ASSYST_USR_SC} matching {?Analyst} will be retrieved. Similarly, if 'Incident' is selected within the {?AnalystType} parameter then only records with {ASSYST_USR.ASSYST_USR_SC} matching {?Analyst} should be retrieved.

As you have probably realised the above formula doesn't actually work, and in all honesty the more i think about it the less likely i believe this is possible.

However i am hoping to be proven wrong...

Thanks in advance,

 

[TheBlondOne]

Hi there,

This is possible to achieve, try the following formula

if {?AnalystType}="Action" then {ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst} else
if {?AnalystType}="Incident" then
{ASSYST_USR.ASSYST_USR_SC}={?Analyst}

Let me know how you got on with this.

HTH


-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[lbass]

Instead of using switch, try:

(
{?AnalystType}="Action" and
{ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst}
) or
(
{?AnalystType}="Incident" and
{ASSYST_USR.ASSYST_USR_SC}={?Analyst}
)

-LB

 

[Flopper]

Thanks Steve/LB,

Apologies for not clearly explaining the problem on my first post.

The first part of the formula,{ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst}, appears to work but the final part continually produces 0 records.

I attempted Steves method originally but the above problem persisted and neither the "If then Else" structure or the Switch command sent the criteria to the server.

Unfortunately LBs formula doesn't work either as records with {ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst} or {ASSYST_USR.ASSYST_USR_SC}={?Analyst} are retrieved, thus disregarding the {?AnalystType} parameter.

Any further ideas?

 

[lbass]

Please double check how you implemented my suggestion as I tested this out first.

-LB

 

[Flopper]

LB,

I checked the implementation of your suggestion and i'm still retrieving the OR statement. It looks like its translating it to the DB incorrectly.

Below is the complete selection criteria used
---------------------------------------------------------
{INCIDENT.INCIDENT_ID} >= 10000000 and
{INC_DATA.EVENT_TYPE} = "i" and
{ACT_REG.DATE_ACTIONED} in {?StartDate} to {?EndDate} and
(
{?AnalystType}="Action" and
{ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst}
) or
(
{?AnalystType}="Incident" and
{ASSYST_USR.ASSYST_USR_SC}={?Analyst}
) and
{ACT_TYPE.ACT_TYPE_SC} = {?ActionType}
----------------------------------------------------------

Below is the Show SQL Query statement, with the linking statements supressed.
---------------------------------------------------------
WHERE
("INCIDENT"."INCIDENT_ID" >= 10000000. AND
"INC_DATA"."EVENT_TYPE" = 'i' AND
"ACT_REG"."DATE_ACTIONED" >= TO_DATE ('01-08-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"ACT_REG"."DATE_ACTIONED" <= TO_DATE ('01-09-2005 23:59:59', 'DD-MM-YYYY HH24:MI:SS') AND
"ASSYST_USR_Action"."ASSYST_USR_SC" = 'BK607' OR
"ASSYST_USR"."ASSYST_USR_SC" = 'BK607' AND
"ACT_TYPE"."ACT_TYPE_SC" = 'REJECT')

 

[Turkbear]

Hi,
Try adding parens around the OR stuff:

{INCIDENT.INCIDENT_ID} >= 10000000
and
{INC_DATA.EVENT_TYPE} = "i"
and
{ACT_REG.DATE_ACTIONED} in {?StartDate} to {?EndDate}
and

(

(
{?AnalystType}="Action" and
{ASSYST_USR_Action.ASSYST_USR_SC}={?Analyst}
) or
(
{?AnalystType}="Incident" and
{ASSYST_USR.ASSYST_USR_SC}={?Analyst}
)

)

and
{ACT_TYPE.ACT_TYPE_SC} = {?ActionType}


It may help the parser send it to the Database...
If not, try the If..Then..Else format, with those extra parens..






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

 

[lbass]

Yes, the extra parens as TurkBear suggested must be added.

-LB

 

[Flopper]

Thanks to you both as that seems to have done the trick. I just wish i could have thought of it myself as i've used the same solution many times before. How come the answer always seems to be easy once someone tells you?!?

I do have an additional query though in that the SQL Query now shows the following:

"INCIDENT"."INCIDENT_ID" >= 10000000. AND
"INC_DATA"."EVENT_TYPE" = 'i' AND
"ACT_TYPE"."ACT_TYPE_SC" = 'STOP CLOCK' AND
"ACT_REG"."DATE_ACTIONED" >= TO_DATE ('01-05-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"ACT_REG"."DATE_ACTIONED" <= TO_DATE ('02-09-2005 23:59:59', 'DD-MM-YYYY HH24:MI:SS') AND
("ASSYST_USR_Action"."ASSYST_USR_SC" = 'BK607' OR
"ASSYST_USR"."ASSYST_USR_SC" = 'BK607')

What i don't understand is that when selecting
{?AnalystType}="Incident" ONLY records with {ASSYST_USR.ASSYST_USR_SC}='BK607' are retrieved even though the SQL statement states that EITHER:

{ASSYST_USR_Action}.{ASSYST_USR_SC} = 'BK607' OR
{ASSYST_USR}.{ASSYST_USR_SC} = 'BK607')

I'm presuming its to do with the Oracle driver translating the Selection Criteria. Am i right?

Thanks once again.

 

[lbass]

The {?Analysttype} isn't being passed to the SQL, so the SQL statement is returning both results. Then this parameter is being applied at the client to return just one result or the other.

-LB