Passing Parameters Drill Through

[SS07185@bcbsa.com]

Hey everyone-

I am having a very simple problem but cannot seem to figure it out. I have two reports. Parent and Child

The commonality of the reports is Customer ID.

In Parent Query..I have Customer ID, Plan Code, Plan Name, Primary Widget, and Year

I have created a list and only show Plan Code, Plan Name, Primary Widget and Year
this is an aggregate look. I want the user to click Plan Name and pass all the customer IDs associated with the query from Parent to Child. That's about it.

It works however when i run the report the list is replicates its self multiple times in Parent. Looks kind of hokey and defeats the purpose. I have attached a excel mockup I think will clear this help.

Any ideas? Do i need HTML? THis seems like basic functionality but Cognos is not behaving the way I would expect. I also tried passing Parameter Value but then the Value section is blank so I can only pass Item for some reason.

Any help would be great.

[CognosPaul]

Hi Shawn,

Cognos will build a query based on what appears in the list, so the Customer ID won't be part of the select statement. If you did force the field to be part of the select, then it would group by it defeating the purpose of not including it in the first place.

There are a few ways to achieve your goal. The easiest way would be to simply pass the plan code and let the query return all the associated customers.

Now let's add a wrinkle, suppose you want to show all of the plans for customers associated with that plan you clicked on. The solution there is to create a separate query in the target report. It will have Customer ID as the only data item and the same filters as your original list plus the plan code filter. You can then use that query to filter another. In the query associated with your target list you would do something like
[Customer ID] in ([query2].[Customer ID])

The second query would then become a subselect in the where clause.

[SS07185@bcbsa.com]

Cognos Paul-

Awesome reply. Here are my responses

1) The passing of the plan code would work however, this particular report is housed in a totally different folder in cognos. Meaning the filters applied to the first query isolates my customers, no i want to take those customers and find out unrelated information about just those folks. What happens with the plan code is i lose the targeted customers.

2) The second solution works. I did something very similar in using a Join instead (perhaps better performance) but that ended up getting me what I needed. What I also did is a drill down fake out. In that i put a hyperlink down and then had the user click and go to the URL of the joined query target report.


Thanks for your help.