Cascading Parameters: How Do It Know? 1

[dspitzle]

I'm trying to figure out HOW CR cascades parameter choices. Does it take the join information from the Links tab and use that to determine that "SCHOOL"."district" links to "DISTRICT"."id", or is it pulling foreign key information from the source database, or what? The reason I'm asking is that I'm trying to filter staff by school, but in the query underlying my report I don't need (or want) the USERS table linked to the DISTRICT table. I'm hoping it's possible to explicitly define the cascade, but I'm concerned it isn't.

 

[lbass]

I might be wrong, but I don't think the cascading parameter is dependent upon the links in the database expert. You could test this though by trying it using your database fields as sources for the parameter pick list.

In a separate version of your report, create a command that uses only the tables you want linked to generate the fields you want to use for the parameters. Don't link the command to any other table, and then use this as the source for the parameter picklists. Then compare the two.

-LB

 

[chelseatech]

My suspicion is the parameter processing does a select distinct from the source tables. So at the top level, does a select distinct on the field, and then the next level down does a select distinct but with a filter to pick up the top level field equal to the value you selected. So yes, it does depend on your table joins in the report. Or maybe it does.

Note that you have three choices for the source of your data:
1) The tables and views joined as a data source in the report
2) A seperate table, or SQL command not joined to the other tables used in the report. Very important to NOT use fields from this in your report.
3) A business view already preloaded with some values. You can schedule the reloading of the values.

The first two options require a database interrogation prior to the processing of the report data. The third is more efficient, but isn't as up to date.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[dspitzle]

2) A seperate table, or SQL command not joined to the other tables used in the report. Very important to NOT use fields from this in your report.
-----------------------------

This is the area where I get hesitant: in my prior experience with MS Access, is you have tables in the query designer which aren't connected to one another, it triggers as "Cartesian Join", where for two tables containing M and N records respectively, the join returns all M x N possible combinations. Can you confirm for me that including tables in the Link View which are detached from the main query tables doesn't trigger a Cartesian Join?

 

[lbass]

Yes, it doesn't trigger a Cartesian join --UNLESS you reference the command in the main body of the report--so make sure you ONLY use it for your picklist.

-LB

 

[dspitzle]

Ah! I believe I have achieved enlightnment