Dynamic Cascading Parameters ... from a command 1

[TomSalvato]

Good morning, experts.

I admit to being perplexed.

I created a simple command consisting of just two fields from two different tables. When I do a simple listing of the command, it shows the exact number of records that it should show. The fields are COMMAND.DISTRICT and COMMAND.CITY. When I look at the listing, DISTRICT A has 6 cities (CITY1, CITY2, CITY3, CITY4, CITY5, CITY6).

Here's my delema ...

I have created a dynamic parameter on the city, based on the district. The dropdown on the DISTRICT shows both DISTRICT A and DISTRICT B. No problem there.
The CITY dropdown shows ONLY CITY3. It's missing the other 5 entries. What gives?

I've tried adding more fields to the command, including the fields that link the two tables. But this appears to be just a parameter problem, since all the records show just fine when i do a flat listing. Anyone who has seen this before and has some thoughts would be GREATLY appreciated!

-TS

* using Crystal 2008, Oracle DB

 

[kenhamady]

Does your command select distinct? There is a limit (defaults to 1000) to the number of records a dynamic parameter can read so if there are lots of duplicates it might hit the limit before it sees all the values.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[TomSalvato]

Hey Ken - Thanx for responding.

If I'm reading you right, then there might not be a way to get what I want ... Are you saying that the command can NOT return more than 1000 records?

 

[kenhamady]

A command can return any number of records. The command isn't the limitation. The dynamic parameter won't show more than 1000 records, unless you tweak the registry. I think the limitation might be applied when the command reads the data.

So if your parameter needs to show the user 1000 different combinations then you will need to do the registry tweak. But if you there are less than 1000 unique pairs for the user to work with then a SELECT DISTINCT should get the list down to under 1000 so that the parameter doesn't hit the limit. The registry tweak is described here, if needed:

http://kenhamady.com/cru/archives/2316

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[TomSalvato]

That's interesting. There's no way the district/muniname combo will ever be more than 1000. Here's what my command sql looks like ...

SELECT distinct("MCD_BOUNDARY_V"."MUNINAME"),"SERVICE_VW"."DISTRICTNAME", "SERVICE_VW"."MCD", "MCD_BOUNDARY_V"."MUNNUMBER", "SERVICE_VW"."OBJECTID"
FROM "MY_GAS"."SERVICE_VW" "SERVICE_VW" LEFT OUTER JOIN "MY_GAS"."MCD_BOUNDARY_V" "MCD_BOUNDARY_V" ON "SERVICE_VW"."MCD"="MCD_BOUNDARY_V"."MUNNUMBER"
WHERE "MCD_BOUNDARY_V"."MUNINAME" IS NOT NULL

When I remove the "SERVICE_VW"."OBJECTID" from the select line, the parameters work perfectly. When I add it, I only get a handful of the options on the dropdown. That's why I was thinking it was somehow based on the number of rows the command returns.

 

[kenhamady]

Yes, adding the Id will make every record 'distinct' so you should note use any fields in the select except for those being returned to the parameter.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[TomSalvato]

I think I've got it.

I knocked the select in my command to just those two fields that I want in the parameters, plus a municipality code ... THEN, I linked the command back to the initial SERVICE_VW via the district and municpality code from the command ... now, I have the parameters working AND I can get all the record details that I need. Pretty fast too.

Thanx again for your help, Ken.

-TS