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.