Oracle BI EE 10.1.3.3/2 - Drilling on Measures - Passing Multiple Parameters during Drills - Go URL and HTML Formatting
Posted by Venkatakrishnan J on February 17, 2008
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.
February 18, 2008 at 10:55 am
Due to its complex nature, very less information is available on Business Intelligence. A simple Google search gave me around 45,000 results, most of them failing to meet my expectation of a comprehensive coverage of the subject.
One of the useful one, though was http://blog.maia-intelligence.com/ . The information here was extensive, and was explained in clear and concise language. Seems they also have their own BI products. This, although being a good reference point, my search is far from over. If you can suggest me another place where I can find relevant information on Business Intelligence, will be good!
February 18, 2008 at 2:47 pm
Thanks for the clear example. I’ve found the documentation on GO URL to be very confusing.
One limitation of this technique is that it seems you need to CAST the metric as a TEXT column. Won’t this interfere with any totalling that you might be doing on your source report?
February 18, 2008 at 3:50 pm
Another question: Instead of passing a prompted value, can you control the selection of a column selector through a passed value?
February 18, 2008 at 4:01 pm
TL: I dont think it is possible unless you are passing it as Direct SQL to the GO URL(assuming you need only tabular views). You can also make the columns dynamic by using my example here http://oraclebizint.wordpress.com/2008/01/24/oracle-bi-ee-101332-dynamic-report-columns-using-dashboard-prompts-and-presentation-variables-part-2/. But it has its own limitations, like you need to harcode the column values in the dashboard prompt. Also, for your first question, unfortunately the answer is Yes. In order to achieve the totals we need to either use UNION sets or use another report.
February 19, 2008 at 5:00 am
Hi Venkat,
Why not just use the navigate feature and have the columns is prompted in the other report? The subsequent report will then be able to pick up the values (even dimensional values) of the previous report. When would be best to use the GO URL and html formatting?
Thanks!
February 19, 2008 at 6:28 am
Rye - You are right. I should have mentioned that above. The above case would help you in constraining what you are passing to the GO URL which is not possible using Navigate. If you use navigate, all the dimensional attributes would be passed. But consider a case where in you need to pass only one of the dimensional attributes as filter to the target report. For the example above, we have 2 reports. Consider a case, wherein while clicking on Sales, it should pass down only Region. Similarly, while clicking on Region it should pass down Country and Sales. To achieve such a case using Navigate would require you to have more and more reports (with varying filters). But the above case lets you to have that flexibility in controlling the target filters without having too many target reports.
February 20, 2008 at 3:26 pm
If you use HTML Formula, I couldn’t sort it by and conditional format with images didn’t work properly. I was trying to use Custom Text Format which I can sort and use conidtional Format with images but I can’t pass other column values as parameters.
Can you suggest any work around? I need to drill down to another report by passing other column values and the link column should be sortable and should be able to use Conditional Format should work as well.
Thanks.
Jay
April 7, 2008 at 2:36 pm
I’m having an issue when the value has a space. For example we have value like “EAST REGION” for the REGION column and when I want to pass this to other report using &P3=’|SALES.REGION|’,it is taking only “EAST” and skipping REGION and hence my query returns no values.
I can’t use %20 to control spaces as these are the data values that come from the table and I have no control over those.
ANy suggestions would be great.
Thanks
Sowjanya
April 10, 2008 at 2:43 pm
I tried your example for passing date and number values using GO URL from report1 to report2. It messed up dates during casting.
I guess it only works with strings. I am lost on this and need help.
Can you please show us how to do pass date or numbers.
-Ranjay