Business Intelligence – Oracle

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.

Advertisements

22 Responses to “Oracle BI EE 10.1.3.3/2 – Drilling on Measures – Passing Multiple Parameters during Drills – Go URL and HTML Formatting”

  1. Ashi said

    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!

  2. TL said

    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?

  3. TL said

    Another question: Instead of passing a prompted value, can you control the selection of a column selector through a passed value?

  4. Venkatakrishnan J said

    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 https://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.

  5. rye said

    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!

  6. Venkatakrishnan J said

    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.

  7. Jay said

    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

  8. Sowjanya Kapireddy said

    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

  9. Ranjay said

    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

  10. Qinghe said

    I am having the same issue as Sowjanya Kapireddy had. I want to
    pass the value of soldto name by P6=’||”POS Customer Shipped”.”SoldTo Name”||’. The value is like “20978 – New York”,
    it returns only “20978” and skips ” – New York”. How to modify this formula so that it will return 20978%20%20-%20New%20York?

    Thanks a lot!

  11. Qinghe said

    It looks that what I reported above is related to:

    Bug 12-GKFCJ4: Analytics Web Dashboard API does not work with space characters in data values.

  12. Krishna Marur said

    To pass values of columns with spaces in the Go URL use Replace funtion as follows
    P3=’ || REPLACE(Table.ColumnName,’ ‘,’%20’) || ‘.
    I tried it and it works

    • Yee Yoong said

      Hi Krishna Marur,
      We tried your method by using P3=’ || REPLACE(Table.ColumnName,’ ‘,’%20′) || ‘ but end up Low Disk Space when we execute the command.

      Any suggestion?

      Thank you.

  13. Desmond said

    I’m getting errors when i try to use this formula in my sales column:[nQSError: 10058] A general error has occurred. [nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)

    ‘||CAST(Measures.EXT_SELL AS CHAR)||’

    anything wrong with this

  14. Qinghe said

    The new version 10.1.3.4 may help!

  15. David said

    Hi,
    I have question…
    What is the P0=2 in the href stringP0=2&P1=eq&P2=Geography.Region

    Is this the number of parameter values, Iam using 3 parameters will P0=3?.

  16. Ruchi said

    Hi,
    I have the same issue as Sowjanya Kapireddy mentioned above (value has a space). I want to pass employee name from my Report1 to Report2.The format of full name is space.

    When i do this by &P3=’||Employee.FULL_NAME||’, it is only taking last name of the employee and skips first name because of the space and so I do not get any result. The value of full name is being populated from table so I have no control over those.

    Can u suggest me some way to modify my formula to achieve the correct result.

  17. Igor said

    Hi Venkat,
    in the method you described above you navigate through common “Geography” dimension. My question is if the same or somehow modified approach can be used for a scenario where you don’t have common dimension but two “not connected” tables. I mean, you have table A with columns c1 and c2 and report1 on the top of it. Then, you have table B with the “same” c1 and c2 columns and report2 on the top of that. The goal is that report2 should show related “detail” records to the active record in the report1. In other words, navigation (drilling) from report1 to report2 based on the values in columns A.c1(=B.c1) and A.c2(=B.c2) is required.
    I did all what you recommended above but I always get all records from the table B in report2.
    Could you please suggest me how to achieve required result?
    Thank you.
    Igor

  18. […] Oracle BI EE 10.1.3.3/2 – Drilling on Measures – Passing Multiple Parameters during Drills – G… page_revision: 6, last_edited: 1241821646|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by Wikidot.com — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an “edit” link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General Wikidot.com documentation and help section. Wikidot.com Terms of Service – what you can, what you should not etc. Wikidot.com Privacy Policy. _uff = false; _uacct = “UA-68540-5″; _udn=”wikidot.com”; urchinTracker(); _qoptions={ qacct:”p-edL3gsnUjJzw-” }; […]

  19. Kelvin13 said

    But it’s not clear that people understand what it means. ,

  20. Sid said

    When charting a column that has been converted into a HTML href column, the column value formula is displayed on the chart instead of the data value. Can the data value be displayed on the chart?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: