Tree prompt filter problem

[ninjamayo]

I am new to Cognos and having difficulty with the tree prompt filtering. I created a hierarchy dimension in Framework Manager which shows Year -> Channel -> Product. Each of these is a unique level and uses the same database field for business key and caption. Each Product has a number of records registered for it with a unique id. I want my report to return those multiple unique ids for a selected product so I created a query that returns unique_id, product. I will then want to filter that query in Report Studio.

I created in Report Studio my prompt page which uses the hierarchy and unticked the create parameterised filter option. Once I have the tree prompt set up I test it and I can see in my browser that it comes back with the hierarchy. I then go to the query that returns my unique_id, product that I have brought in Report Studio from my package and I add a filter in it. The filter is as follows:

[Report Layer].[qrySRN].[PRODUCT_NAME] = ?paramProduct?

This filter does something really strange which I haven't seen before and that is it changes the behaviour of the tree prompt. When I add the filter the tree prompt simply shows Products with no hierarchy anymore. Why is that? When I remove it I can get back my hierarchy.

Any ideas how to resolve this issue?

[BigChris]

Ok...I'm  a bit confused, but that might be lack of coffee. Are you using a cube or relational data? Tree prompts suggests a cube, but your filter looks like it's coming from a relational source...

(If it's a cube you should really be using a slicer rather than a filter by the way)

[ninjamayo]

Hi BigChris,

this is where my newbieness comes on the surface.

I have a database table that contains rows with YEAR, CHANNEL, PRODUCT, SRN_ID. For a specific YEAR, CHANNEL and PRODUCT there are multiple SRN_ID entries. I want to have a tree prompt which essentially displays the YEAR, CHANNEL, PRODUCT and once I select the PRODUCT at the lowest level I want to be able to filter my data for that combination of YEAR, CHANNEL, PRODUCT and return back a list of SRN_IDs.

That is my end goal but I am not sure if my approach is correct. Any help would be much appreciated.

[BigChris]

I could be wrong, but I think Tree Prompts only really work with dimensional (cube) data. What you're describing sounds to me more like three cascading prompts.

In your filter, I'd put something like:
[YEAR] = ?Select Year? and [CHANNEL] = ?Select Channel? and [PRODUCT] = ?Select Product?

Then I'd create a prompt page with the three prompts, and for each of them I'd set up a query to drive the data for them. The first query would just select all the values of YEAR. The second query would select all the values of CHANNEL, but with a filter of [YEAR] = ?Select Year?. The third query select values of PRODUCT, with a filter of [YEAR] = ?Select Year? and [CHANNEL] = ?Select Channel?

Back on your prompt page you'll then need to have a Cascading Source for ?Select Channel?...and you need to set that to ?Select Year?...and then set teh cascading source for ?Select Product? as ?Select Channel? Set each of the prompt to auto submit so that the other prompts populate.

The end result then should be a prompt page that forces the user to select Year, then Channel then Product and filters the output accordingly.

Hopefully that all makes some sort of sense. It's all off the top of my head, so if there are any references that don't quite tally to what you can see in your design just have a bit of a dig around...I might have used the wrong term

[ninjamayo]

I think I have a cube rather than a relational model. One major problem is that my tree in the end returns a single parameter such as product that cannot be used on its own to identify the unique ids. Instead it is the combination of YEAR, CHANNEL, PRODUCT that gives me what I want. How can I use the prompt to return for me all three selections rather than just PRODUCT?

[MFGF]

Hi,

We need to know what sort of package you are using to be able to help you here. The info you're giving us is contradictory:

The filter is as follows:
[Report Layer].[qrySRN].[PRODUCT_NAME] = ?paramProduct?

I have a database table that contains rows with YEAR, CHANNEL, PRODUCT, SRN_ID.

These two posts imply you have a relational package?

I think I have a cube rather than a relational model.

This post implies you are using a dimensional package?

Can you tell us what you see in your package tree? Are you seeing dimensions, hierarchies, levels and members, or are you seeing query subjects and query items? If you're not sure, can you post up an image of what your package looks like?

MF.