Linking based on one of four fields

[Former Member]

Hi,

I have a report that uses two SQL queries (incredably slow without).

One query uses a Product table that links data from four sources - Stock, Reserved, Ordered, and Usage.  Subqueries for pulling data from these tables use varying criteria, but for the purpose of this question, the query (command) uses a parameter for passing location (simply 1, 2, or 3).  The rows that are returned have four fields: Loc_Stock, Loc_Rsrv, Loc_Ord, and Loc_Use.  These fields will always contain a value of 0 or the location passed.  All, or some of the fields will have a non-0 value, but will never be all 0s.  e.g. - 0,1,0,1 - 1,1,1,1 - 1,0,0,0...

The second query pulls data from a table maintained in a separate database.  It wants to match up by location, so I need to pass this location value, either from the command parameter or from something like the max value of the four location fields.

I can probably get by with creating a parameter in the second query for location, but I'm trying to avoid the possibility of User error in entering two different locations.  I guess ideally I'd like to be able to create a parameter at the report level that passes through to the queries.

I also need to print the location name in the header and wonder about the ability to use the command parameter beyond just the query.

Any ideas?

Matt Tallon

[Former Member]

Last Question first: Yes you can put a parameter in the anywhere in the report.

Next... why can't you use the same parameter to pass the location selection to both?

[Former Member]

Hi, thank you for your help,

Using the same parameter is what I ended up doing, but only by trial and error.  I created a "Location" parameter in Query 1 and then created a parameter with the same name in Query 2.  Creating the "second" parameter in Query 2 overwrote the definition for the parameter created in Query 1 (evidenced by different verbiage in the prompt and different data type).

I'm still not clear if a parameter can also be shared between an "Add Command" and a parameter created in Field Explorer.  Anyone with experience using a parameter globally like this, I would appreciate any comments.

Thank you again,

Matt