If you are unable to create a new account, please email support@bspsoftware.com

 

Generating the URL of a report that includes parameters with multiple values

Started by jackg_tor, 13 Jul 2010 01:31:19 PM

Previous topic - Next topic

jackg_tor

Hi,

I would like to include a URL string in my reports that can be used to recreate the report at a later time, either by the same person or anyone else. (The Cognos Connection features to save a report seem to be a lot less convenient, especially for people unfamiliar with the tool).

These reports include parameters that can take multiple values. From various sources, I know that we can manually encode those parameter values in the URL with an XML string like this: &p_P1=<selectChoices><selectOption useValue="Value1" displayValue="Display1"/><selectOption useValue="Value2" displayValue="Display2"/></selectChoices>. However, I need a way to do it automatically from Report Studio so that users don't have to figure this out for themselves.

My first attempt used just a report expression that contained a clause like

URLEncode(ParamValue('p_P1'))

but that just expands to "value1%2c+value2" which is not properly interpreted when passed in the URL.

I then looked for a report function that would replace the commas with appropriate XML strings but couldn't find one. So, I thought I might be able to define a query data item to do that mapping with an expression like this:

'<selectChoices><selectOption useValue="' || replace(?parameter1?, ',', '"/><selectOption useValue="') || '"/></selectChoices>'

This didn't work because the phrase "?parameter1?" seems to expand to only one value unless used in list context. There were other problems too but I won't list them all right now.

The ability to share reports seems like such a fundamental requirement that I feel I must be missing something. Is there a way to do this without jumping through so many hoops?

Thanks,
Jack Goldstein

CognosPaul

Instead of using an XML string you can pass parameters like this: (Note this is 8.2 syntax, 8.4 is different)

http://cognos/cognos8/cgi-bin/cognosisapi.dll?b_action=xts.run&m=portal/report-viewer.xts&ui.action=run&ui.prompt=false&ui.object=%2fcontent%2fpackage%5b%40name%3d%27GO%20Data%20Warehouse%27%5d%2freport%5b%40name%3d%27Current%20Assets%27%5d&p_Parameter1=1&p_Parameter1=2&p_Parameter1=3

That will pass 1,2,3 to Parameter1.

In order to build the link to the report with the full parameter list you'll need a singleton (not available in 8.2 so no example XML today) and an understanding of the macro functions.

To begin create the singleton wherever you want the link to appear.

Drag an HTML item inside, set it to text, and paste in an open anchor tag plus the full URL for the report.

It should look something like:
<a href="http://cognos/cognos8/cgi-bin/cognosisapi.dll?b_action=xts.run&m=portal/report-viewer.xts&ui.action=run&ui.prompt=false&ui.object=%2fcontent%2fpackage%5b%40name%3d%27GO%20Data%20Warehouse%27%5d%2freport%5b%40name%3d%27Current%20Assets%27%5d
Note that the tag is not closed.

Go to the query that the singleton created and create a data item.
Use the expression:
#sq(join('&p_Parameter1=',split(';',promptmany('Parameter1','token',' ','&p_Parameter1='))))#

Back in the singleton go to the properties and click on properties (isn't that delightfully confusing?) and add that item to the list. Drag in another HTML item to the right of URL, set that to data item value and use the item you just created. In the description set that to "parameters"

Now, finally, add another HTML item, set that to text:
">Permanent Link</a> to close the anchor.

Save and test.

jackg_tor

Paul,

Thanks for the detailed response.  That was very helpful and in the end, I used an expression very similar to what you suggested.  The only real difference is that I had to add an extra level of split and join calls in order to do the urlencoding, something like this:

#sq(
''
+ join('&p_p_product_groups=',split('XXvalsepXX',urlencode(join('XXvalsepXX',split(';',substitute('^ $','',promptmany('p_product_groups','token',' ','XXvalsepXX')))))))
+ join('&p_p_products=',split('XXvalsepXX',urlencode(join('XXvalsepXX',split(';',substitute('^ $','',promptmany('p_products','token',' ','XXvalsepXX')))))))
...


I had noticed that the macro functions seemed to have the features I needed but since I didn't (and don't) see them in the Available Components pane, I assumed that they were not available in that context.  I even tried a test macro call but I must have done that wrong because it didn't work.

Regards,
Jack Goldstein