OBIEE 11g Navigation to other reports does not pass all prompt / attribute values

I created a summary and a detail report in 11g. When I was clicking on a number in summary, it was navigating to the detail, but it did not pass the prompts for Year and month that was selected in summary. I cheked the detail report, the year and month filters were present.

After much deliberation, I found the issue in the way I was creating the prompt. In 11g, we have the option of selecting columns from multiple subject areas in the prompt, and I was using this to add same fields using icon below:

When you are in select columns dialog box, the green icon at the top allows you to select different subject area, following by letting you add columns from that SA. That works good, but the problem is that syntax of column in summary report and here in drill report will now be different, which will make drill down/navigation not to pass values to target.

To avoid this, start with subject area that has all the fields from where drill down needs to pass values, then add columns from other SA. Hope this helps.

Drill down on union reports

When creating drill down on union reports in BI, the value user clicks on for drilling down is not passed to the target report. But this problem can be overcome, if field is converted to html using the syntax below:

'<span style="">&nbsp</span>'||'<a class="Nav" href=saw.dll?Dashboard&PortalPath=/shared/Sales+Reports/_portal/&Page=Market+Share+Report&Action=Navigate&col1=Product.%22Item%20Number%20%28NDC11%29%22&val1="'|| Replace("Product"."NDC-11",' ','%20')||'">'||"Product"."NDC-11"||'</a>'

In example above, Item number is passed from summary report to the target.  The syntax above will also set values in prompts for the product field. This can further be used to set a presentation variable, and then using variable filter a direct database query. Direct database will call the variable using syntax below:

'@{item_num}{00099999}'

Where:

item_num is the variable setup in the prompt.

‘00099999’ is the default value

The above method has a limitation, that it will pass only one value to the target, which should be good for most situations. But if not, then we can also implement an intermediate report where one value will be passed to intermediate target report, but user can then click again to reach final destination and all values will be passed. The intermediate report in this case will have just one query. Downside of this method is user has to click twice to reach the destination report, but it works for those picky users we all know how demanding they can be.