One of my colleagues within Oracle called me today with regard to a pretty interesting issue that he was stuck up on. Basically, he wanted to find a method of drilling on measure values by passing some of its dimensional attributes as filter to another report. As you would probably know, when we do drilling we would be passing the value of the drilled column as filter to the subsequent report. But in the above case, he wanted to pass 2 or 3 other dimensional attributes. If you are not sure about what i mean, look at the sample report below
As you see above, we have a simple report, containing Region, Country and the corresponding sales. Now the issue is how do we pass the Region and Country as parameter to another report while drilling on sales i.e in the above report when we click on 7,264,336 we should pass on Asia and Japan as parameters to another report. In order to achieve this lets start with creating the 2nd report containing the same Region, Country and Cost instead of sales. Now add 2 filters to this report on Region and Country with is Prompted clause.
Now, lets go back to the first report. The idea is to convert the sales column into a HTML href column and then pass the parameters to the 2nd report using GO URL. In the formula tab of the Sales column enter the below formula,
’<a href=http://localhost:9704/analytics/saw.dll?GO&path=/shared/Dynamic%20GO%20URL/Report2&Action=Navigate&P0=2&P1=eq&P2=Geography.Region&P3=’||Geography.Region||’&P4=eq&P5=Geography.Country&P6=’||Geography.Country||’ style=”text-decoration:none;”>’||CAST(Sales.Amount_Sold AS CHAR)||’</a>’
And convert the above column into HTML format.
So, basically what we are doing is we are calling the 2nd report using GO URL and also passing the other row-dimensional attributes like Region and Country to the GO URL. The formula has such a format in order to make the column as a HREF link. Now if you click on the measure it would pass all the dimensional attributes to the target report.
A very simple technique but again can be very powerful since it can pass multiple parameters at the same time.
Posts
