Dynamic Cascading parameter

[tweetie7]

Hi
I have been using Crystal Reports for years but only recently started using CR2008. I am reporting against an Oracle 11g database. I have set up a dynamic cascading parameter based on the following:

Region
District
Division

These are set up as optional (if no division is selected then all divisions in the district selected should be used) and multiple values can be selected if required.

Incident records need to be retrieved by matching the Division_ID to the Location_ID on an incident. This is working fine when Division/s is selected but if no Division is selected then the Region/District is not used to retrieve records, all records are returned.

How do I get the selection to be based on Region and/or District if only these are selected? I don't want to set these up as separate parameters as the users only want to see the values associated with the Region/District. Will I have to base the parameters on a command and if so, how do I do this?

Thanks for your help in advance.

 

[Turkbear]

Hi,
What is your record selection formula?






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

 

[Parahall]

you need to make "where" formula that according to selection from prompt list will retrive from sql...

 

[tweetie7]

Hi Turkbear

The select statement in Crystal is:

(not HasValue({?Operator}) OR {INCIDENT_NARRATIVE.OPERATOR_ID} = {?Operator}) and
instr({INCIDENT_NARRATIVE.MESSAGE},"Priority changed") > 0 and
{INCIDENT_NARRATIVE.MESSAGE_TIME} in {?From Date} to ({?To Date} + 1) and
(not HasValue({?QPS Area - DIVISIONID}) OR {INCIDENT.LOCATION} = {?QPS Area - DIVISIONID})
and {INCIDENT.LOCATION} = {Divisions.DIVISIONID}

This works fine if a Division ID is selected, but if no Divison ID is selected (because it's optional) I want to force the select to go to the next level, eg

and {Divisions.DISTRICTID} = {District.DISTRICTID}

but again, needs to be optional.

Don't know if it makes any difference, but the 'Area' data has not been loaded into the Oracle database yet so I have created an Access database with the Region > District > Division data that I am using for the report.

The sql generated by the report looks like:

CAD
SELECT "INCIDENT"."INCIDENT_NUMBER", "INCIDENT_NARRATIVE"."MESSAGE", "INCIDENT"."INCIDENT_TYPE", "INCIDENT_NARRATIVE"."SEQ", "INCIDENT_NARRATIVE"."OPERATOR_ID", "INCIDENT_NARRATIVE"."MESSAGE_TIME", "INCIDENT"."LOCATION"
FROM "VISION"."INCIDENT" "INCIDENT" INNER JOIN "VISION"."INCIDENT_NARRATIVE" "INCIDENT_NARRATIVE" ON "INCIDENT"."INCIDENT_NUMBER"="INCIDENT_NARRATIVE"."INCIDENT_NUMBER"
WHERE ("INCIDENT_NARRATIVE"."MESSAGE_TIME">=TO_DATE ('01-10-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "INCIDENT_NARRATIVE"."MESSAGE_TIME"<TO_DATE ('24-11-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
ORDER BY "INCIDENT"."INCIDENT_NUMBER", "INCIDENT_NARRATIVE"."SEQ"
EXTERNAL JOIN INCIDENT.LOCATION={?Area: Divisions.DIVISIONID}


Area
SELECT `Divisions`.`DIVISIONID`
FROM `Divisions` `Divisions`
WHERE `Divisions`.`DIVISIONID`={?QPSCAD: INCIDENT.LOCATION}

I can see why it's not working, but can't work out how to force it use the 'upper' levels of the cascading parameter.

Let me know if you need anything else.

 

[tweetie7]

Hi

I think I've got this working now. Changed the select to:

(not HasValue({?Area - REGION}) OR {?Area - REGION} = {Regions.REGION}
and {Regions.REGIONID} = {Districts.REGIONID}
and {Divisions.DISTRICTID} = {Districts.DISTRICTID}
and {Divisions.DIVISIONID} = {INCIDENT.LOCATION})
and
(not HasValue({?Area - DISTRICT}) OR {?Area - DISTRICT} = {Districts.DISTRICT}
and {Divisions.DISTRICTID} = {Districts.DISTRICTID}
and {Divisions.DIVISIONID} = {INCIDENT.LOCATION})
and
(not HasValue({?Area - DIVISIONID}) OR {INCIDENT.LOCATION} = {?Area - DIVISIONID})

which produces the following sql (if for example SOUTHERN region is selected):
CAD
SELECT "INCIDENT"."INCIDENT_NUMBER", "INCIDENT"."LOCATION"
FROM "VISION"."INCIDENT" "INCIDENT"
EXTERNAL JOIN INCIDENT.LOCATION={?Area: Divisions.DIVISIONID}


Area
SELECT `Districts`.`DISTRICT`, `Divisions`.`DIVISION`, `Regions`.`REGION`, `Districts`.`DISTRICTID`, `Divisions`.`DISTRICTID`, `Divisions`.`DIVISIONID`, `Districts`.`REGIONID`, `Regions`.`REGIONID`
FROM (`Divisions` `Divisions` LEFT OUTER JOIN `Districts` `Districts` ON `Divisions`.`DISTRICTID`=`Districts`.`DISTRICTID`) LEFT OUTER JOIN `Regions` `Regions` ON `Districts`.`REGIONID`=`Regions`.`REGIONID`
WHERE `Divisions`.`DIVISIONID`={?CAD: INCIDENT.LOCATION} AND `Regions`.`REGION`='SOUTHERN' AND `Regions`.`REGIONID`=`Districts`.`REGIONID` AND `Divisions`.`DISTRICTID`=`Districts`.`DISTRICTID`

Must have needed the weekend to think about it

Thanks for taking the time to look at it.