Skip to Content
0
Former Member
Dec 04, 2008 at 09:14 PM

records returned with static parameter, but don't when using dynamic

24 Views

Hello,

I am using CR Developer, Version 11.0.0.1282 (BTW, I keep seeing references to Release 1 and Release 2, and I'm not sure which my version is.)

I have a large database (500,000 land records) and each record has a value for Management Program and for Project Name. Within each Management Program there are many Project Names.

If I set up a static parameter for Management Program, users are able to select a single Management Program (i.e. WMA, for Wildlife Management Area) from the dropdown list. This returns all records that have WMA listed as the Management Program, which thus includes all Project Names. This works fine, but I want to return just those records that are for a single Project Name.

So I got rid of the static parameter and instead created a dynamic parameter, where the user first selects a Management Program, and then a single Project Name.

I think I set up the parameters correctly, and the select formula (see below) includes those parameters. When I then try to run the report, I successfully get the expected drop down boxes, where I can first pick out the Management Program I want, then the Project Names that apply just to that Management Program.

That part seems to work fine, but no data gets returned.

The SQL query seems fine:

SELECT "LAND_RECORDS"."TOWNSHIP",

"LAND_RECORDS"."TOWNSHIP_DIR",

"LAND_RECORDS"."RANGE",

"LAND_RECORDS"."RANGE_DIR",

"LAND_RECORDS"."SECTION",

"LAND_RECORDS"."FORTY_DSC",

"LAND_RECORDS"."GOVT_LOT",

"LAND_RECORDS"."GROSS_ACRES",

"LAND_RECORDS"."LAND_TYPE",

"LAND_RECORDS"."PUBLIC_USE_ADMINISTRATOR",

"LAND_RECORDS"."PUBLIC_USE_MGMT_PROG_CODE",

"LAND_RECORDS"."PUBLIC_USE_PROG_PROJ",

"LAND_RECORDS"."PUBLIC_USE_PROJECT_CODE",

"LAND_RECORDS"."SURFACE_OWNED_CODE",

"LAND_RECORDS"."PUBLIC_USE_MGMT_PROG"

FROM "LNDREC"."LAND_RECORDS" "LAND_RECORDS"

WHERE "LAND_RECORDS"."SURFACE_OWNED_CODE"='Y' AND "LAND_RECORDS"."PUBLIC_USE_MGMT_PROG"='WILDLIFE MANAGEMENT AREA' AND "LAND_RECORDS"."PUBLIC_USE_PROG_PROJ"='AGDER'

ORDER BY "LAND_RECORDS"."PUBLIC_USE_MGMT_PROG_CODE",

"LAND_RECORDS"."PUBLIC_USE_PROG_PROJ"

And here is the selection formula:

{LAND_RECORDS.SURFACE_OWNED_CODE} = "Y" and

{LAND_RECORDS.PUBLIC_USE_MGMT_PROG} = {?Management Program - PUBLIC_USE_MGMT_PROG} and

{LAND_RECORDS.PUBLIC_USE_PROG_PROJ} = {?Management Program - PUBLIC_USE_PROG_PROJ}

Can anyone tell me why no records get returned when I use the dynamic parameter, while those same records (as well as the other records for that Management Program) get returned when I use the static parameter?

Thanks in advance for any help you can give me.