Drill down on one column, while filtering another

[parishilton]

Hi all,
I have a crosstab with two columns, the first column contains our stores (it's defined as children of 'North'). The second column contains a product category.
I'd like to allow users to click the product category and drill down into the underlying products.  Out of the box, this works and shows all products for all the stores (in the North).
This is more data than I'd like to pull back.  Ideally, when drilling on product, I want to also filter to the store on which the product category was selected.

The nearest I've managed is to set up a drillthrough to a second report. Whilst this works, if I have to use drillthrough, I'd like to drill to the same report, but pass a parameter somehow which instructs the 'target' to drill down on product.

Probably, the drill needs to be based on the store. So clicking a store , will show the product lines for that store.

Hopefully someone out there can help with what must be a fairly common issue.

P
additional info .
Cognos 8.4 BI
TM1 cube datasource

[tjohnson3050]

You can add a filter on the report for store.  Set that filter to optional, then drill through to the same report and pass the store value in the drill definition.

[parishilton]

Thanks tjohnson.
I was aiming not to make the user select the store.

I've achieved it with a drillthrough , I'd like to be able to drillthrough to the same page, but I can't figure out how to set a flag to indicate where I am.  That's a whole different question though!


P

[tjohnson3050]

You can accomplish this without making the user select the store, but it takes a few steps.
1. Put the store in the query behind the scenes (don't add it to the list).
2. Make it active by selecting the entire list, then in the 'properties' property, check 'store'.
3. In the drill definition pass both the store and the product.

[parishilton]

Hi,
this is a crosstab, it doesn't seem to have a 'properties' property.

P