interrelated report parameters

Is it possible to have parameter available values (coming from different
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>