GOURL for BI Publisher report - Passing multiple values on to a parameter

Most of you might know already on how to place a BI publisher report on a Dashboard and link a Dashboard prompt to that BI Publisher report.

By creating a Presentation variable on the Dashboard prompts with the same name as the parameter name on BI Publisher, we can link the Dashboard prompts and BI Publisher.

For instance, if you have a parameter created ASOFDATE on your BI Publisher report, you can create a Dashboard prompt with a Presentation variable ASOFDATE and embed the Prompt and BI Publisher report on the same Dashboard page.

To make if more interested my client gave me a requirement to call this Dashboard page from an External application. Here comes the use of GOURL.

Here I called directly the BI Publisher report  through GOURL as the Client anyhow don’t want to see the Prompts and want to pass the values from the application through GOURL.

My GO URL was like this:

https://SERVERNAME/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo&bipParams={"_xmode":"1","_xpf":"","_xpt":"0","_xdo":"%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo","Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399,456,876"],"_xt":"Test%20Template","_xf":"html","_xautorun":"false"}&NQUser=weblogic&NQPassword=password1234

I have explained the above URL here:

SERVERNAME: name of the OBIEE server or IP address.

Action=Open: Action of this GOURL is just to open the report.

%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo: Name of the BIP Report.

xmode":"1": This will hide the parameter labels on top of the BIP report.

Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399"] : Parameters used on the BIP Report

Test%20Template: Template name on the BIP report

Weblogic: User ID passed as part of the GO URL from application, user no need to enter it again.

Password1234: Password given on GOURL, hence user no need to enter it again.

More interesting task would be to pass multiple values on the same parameter. Consider the client want to pass multiple TestIDs from the application via GOURL. The URL below has multiple TestIDs ("399,456,876") , but this one doesn’t work because parameter on BIP side will take this as a single string.

https://SERVERNAME/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo&bipParams={"_xmode":"1","_xpf":"","_xpt":"0","_xdo":"%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo","Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399,456,876"],"_xt":"Test%20Template","_xf":"html","_xautorun":"false"}&NQUser=weblogic&NQPassword=password1234

One workaround would be pass the entire TestID value as a string with a separate character and use the below on the filter to split the string of numbers into separate numbers.

TESTID in (with str as (select :PoolId as val from dual)

select cast(regexp_substr(val,'[^.]+', 1, level) as int)

from str

connect by cast(regexp_substr(val, '[^.]+', 1, level) as int) is not null)

Here the multiple TestIDs will be passed as ("399.456.876"), dot separated one string and the above function on the filter will convert it into a comma separated list of values (399,456,876). The GOURL is below.

https://SERVERNAME/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo&bipParams={"_xmode":"1","_xpf":"","_xpt":"0","_xdo":"%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo","Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399.456.876"],"_xt":"Test%20Template","_xf":"html","_xautorun":"false"}&NQUser=weblogic&NQPassword=password1234