passing multiple values

[ari_meta]

Hi All

In my report. query calculates top 10 customers by number of orders in a month and display the result.

I need to pass these top 10 customers to single parameters in drill through report.

any idea , how can i do that ? 

Thanks

Ari

[CognosPaul]

I can think of a way, but it's painful and requires building the drillthrough as a URL in an HTML item.

Your best bet may be to find a workaround. Can you recreate the top ten query in the target report, and filter the report on that?

[ari_meta]

Hi Paul

Can you guide me , how to build drill through as a URL .

I am passing that parameter to Stored Procedure. So, i can not create query in the target report.

Thanks

Ari

[Lynn]

I'm not sure if I got this right, but it sounds to me like you have a stored procedure that is expecting 10 parameters (one for each of the top 10 customers). Your report, however, is listing them on separate rows.

If that is the case, then maybe this will work. Can't say for sure, so FWIW...

Create a data item in your query with expression:
if ([CustomerRank] = 1) then ([Customer]) else ('')

Do this 9 more times for the remaining ranks. These does not have to appear in your layout. A query reference pointing to this query includes these data items only with aggregate property set to maximum. Then use that query as the basis for the drill through to a target report that calls the procedure.

[CognosPaul]

Okay, I'm warning you that this is painful.

Step by step.

In the portal, find your report. Click on the properties and "View the search path, ID and URL". Copy the Default action URL.

It should look something like this:

Code Select Expand

https://servername/cognos8/cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27VI%27%5d%2ffolder%5b%40name%3d%27examples%27%5d%2freport%5b%40name%3d%27Skeleton%27%5d&ui.name=Skeleton&run.outputFormat=&run.prompt=true

Change it to a dynamic url and remove the &run.prompt=true:

Code Select Expand

../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27VI%27%5d%2ffolder%5b%40name%3d%27examples%27%5d%2freport%5b%40name%3d%27Skeleton%27%5d&ui.name=Skeleton&run.outputFormat=


In your report create an HTML item that looks like this:

Code Select Expand

<a target="_blank" href="../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27VI%27%5d%2ffolder%5b%40name%3d%27examples%27%5d%2freport%5b%40name%3d%27Skeleton%27%5d&ui.name=Skeleton&run.outputFormat=
Note that the tag is not closed.

Drag in a repeater (not a repeater table!) directly to the right of the html item. Set the query of the repeater to the Top Ten query. On the properties of the repeater there is a row called Properties. Click on that and check the data item that has the top ten.

Drag an HTML item into the repeater. Set the source type to report expression. In the HTML item add the following:

Code Select Expand

'&p_PARAMETERNAME=' + [query1].[data item1]
If the data item is a numeric then wrap it with number2string(). The &p_ is essential, if your parameter name is p_customer then you would put in &p_p_customer.
Finally drag an HTML item to the right of the repeater (not in the repeater!) with the following:

Code Select Expand

">link</a>

When the report is generated in HTML the three HTML items will look something like:

Code Select Expand

<a target="_blank" href="../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27VI%27%5d%2ffolder%5b%40name%3d%27examples%27%5d%2freport%5b%40name%3d%27Skeleton%27%5d&ui.name=Skeleton&run.outputFormat=&p_PARAMETERNAME=1&p_PARAMETERNAME=2&p_PARAMETERNAME=3&p_PARAMETERNAME=4&p_PARAMETERNAME=5&p_PARAMETERNAME=6&p_PARAMETERNAME=7&p_PARAMETERNAME=8&p_PARAMETERNAME=9&p_PARAMETERNAME=10">link</a>

[ari_meta]

Thanks Paul.
As you said , it is big thing . but i am working on it.

Thanks again

Ari