Oracle Business Intelligence

Products Banner

How to navigate to child report based on value in parent report.

Received Response
204
Views
51
Comments

Hi all,

I have requirement to drill to another report (which is in Subject Area B) from Parent report (Which is made of Subject Area A).

The below report data build on Subject Area A.

pastedImage_0.png

As when i click on Agreed Spend amount then it has to take to the below report (Built on Subject Area B).

pastedImage_1.png

It has to show only one record BPA-TAWSEEL-10, but showing entire report.

I tried with Action link to the child report but not filtering. It's showing entire report (may be because column names are not same (Source agreement in Subject area A, Agreement Number in Subject area B)). Please let me know how to filter based on value (BPA-TAWSEEL-10) from the parent report.

Regards,

Chandra.

«1

Answers

  • 3319067 wrote:(may be because column names are not same (Source agreement in Subject area A, Agreement Number in Subject area B))

    Not maybe - that IS the reason. You could cheat the system by having both columns in both subject are have a matching fully qualified alias so that the match can happen.

    Alternatively - and this is the more proper solution - is to use parametrized GO urls: https://docs.oracle.com/middleware/12212/biee/BIEIT/apiwebintegrate.htm#BIEIT363

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Christian,

    Thanks for the reply.

    Where do i require to do "fully qualified alias" for two columns.

    This is cloud environment (using 11.1.1.9 version). Do not have access to RPD. Please let me know.

    Regards,

    Chandra.

  • 3319067 wrote:This is cloud environment (using 11.1.1.9 version). Do not have access to RPD. 

    You see that's exactly the reason why it's a good idea to always, right away from the start, state what your version and your context are. It wastes a lot less of everybody's time!

    So then check out the Go URL I posted

  • asim cholas
    asim cholas ✭✭✭✭✭

    Create and keep the second report in a dashboard page and keep the reports with hidden prompt in second dashboard for the column u need to filter and use presentation variable

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Christian,

    Currently Agreed Spend measure (1st report) column having the following formula.

    FILTER("Procurement - Purchasing Real Time"."- Purchase Order Distribution"."Purchase Order Distribution Amount" USING ("Procurement - Purchasing Real Time"."- Purchase Order Line Detail"."Agreement Reference Flag" = 'Yes'))

    I am unable to write Go URL from this column.

    2nd report name: BPA Details report.

    Column name: "Blanket Agreement Header Detail"."Agreement Number"

    I have written the following query but throwing an error.

    <a href=https://efjj-test.bi.em3.oraclecloud.com/analytics/saw.dll?&Path=/Shared/Custom/Procurement/BPA%20Details%20report&Action=Navigate&P0=1&P1=eq&"Blanket%20Agreement%20Header%20Detail"."Agreement%20Number"="Procurement - Purchasing Real Time"."- Purchase Order Line Detail"."Source Agreement">Report Name</a>

    Request you please help me.

    Regards,

    Chandra.

  • asim cholas
    asim cholas ✭✭✭✭✭

    Did you try the second approach?

    Keep the report in a dashboard page and use a presentation variable and a prompt. Let me try in my environment and will update you.

    Below two reports are from two different subject areas.

    pastedImage_0.png

    pastedImage_1.png

    Prompt  Settings

    pastedImage_0.png

    pastedImage_1.png

    You can choose the default prompt for dashboard page  from dashboard page properties (or keep it in section itself)

    pastedImage_0.png

    pastedImage_2.png

    pastedImage_3.png

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi,

    Here i am clicking on Agreed Spend column and i need to pass  Source Agreement value to another report (another subject area).

    1st report

    pastedImage_0.png

    Not able to get the result.

    Regards,

    Chandra.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    HI all,

    Please suggest how to pass parameter to the child report from column formula (href).

    Please see above i have written formula but was unable to correct.

    Regards,

    Chandra.

  • asim cholas
    asim cholas ✭✭✭✭✭

    Clicking on one value and passing another, also navigating to another subject area ... Why not navigate from source agreement column, so that you can pass the value.

    Check the below blog. May be helpful though i am not sure.

    pastedImage_1.png

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Asim,

    I have placed the Source Agreement column for understanding problem description. Real scenario the Source agreement column will be hidden.

    User has to click on measure to drill down to another report. Please let me know how to correct the href script.

    Regards,

    Chandra.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Hi,

    another poster has given you the answer to this.

    Create a hidden dashboard prompt that includes a prompt for the value that you want to coordinate. On that prompt have it set a variable.

    It is worth starting with this initially not hidden to check that it is catching the click on the data item as it should be.

    Use that variable as the filter on the target column that isn't the same name.

    Have your column open the other report, this has worked for me many times.

    Or build up the GO_URL, it is a good deal easier than it used to be.

    Best Regards,

    Robert.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    HI Robert,

    I am not clear on one thing how will you have the value in the hidden prompt column (Source Agreement).

    As when i click on the measure value in the report the corresponding agreement value (for example: BPA-TAWSEEL-10) has to pass to prompt column. How it will pass to the prompt column?

    Regards,

    Chandra.

  • asim cholas
    asim cholas ✭✭✭✭✭

    It will not pass. Only the one you clicked will be captured and can be used in another subject area.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    HI asim,

    If i click on Source agreement (report 1) column  then how will you capture value. Request you please detail me for my scenario.

    Regards,

    Chandra.

  • asim cholas
    asim cholas ✭✭✭✭✭

    Dear Chandra,

    Create a prompt and presentation variable for the column source agreement and use it as default prompt in a dashboard page and keep the report in the same page  . Also keep it in the page where your first report resides. Check the screenshots above and let me know which part is not clear for you.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Hi,

    say your source report has 3 columns A,B,C that need to be prompted and various others that don't.

    You create a dashboard prompt for each of the three and you set variables in the dashboard prompt for each {MyVarA} {MyVarB} {MyVarC}

    When you click on a row of data you will see that the prompts "catch" the values you click on setting the prompt to the values in the row.

    All you need to do then is set your on click value on ANY of the columns to navigate to the other report.

    And in the target report for the equivalent columns AA, BB, CC, you need to filter each to be equal to your presentation variable {MyVarA} etc.

    As I said I have used this on very complex reports and the hidden prompts will catch multiple columns data level values and the target report will then give you correctly correlated data.

    Hope this helps,

    Robert.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    HI asim,

    I have created prompt and pv for source agreement column. PV name is pv_agreementvar.

    Step1: I have placed prompt and report1 in dashboard page1

    pastedImage_0.png

    In the above screen i have placed report and below prompt. Now i will click on last column value (BPA-TAWSEEL-10) then how my prompt capture that value here. In report1 column Source agreement, I have navigated to report2 through action link.

    Step2: I have placed same prompt and report 2 in dashboard page 2

    pastedImage_1.png

    How this prompt captures value from 1st db page and filters 2nd report here.

    Pls correct me where i am wrong here.

    Regards,

    Chandra.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Any help please

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi all,

    I am trying with GO URL from column formula.

    I have written the following formula in the 1st report column. I am trying to pass Source Agreement column value to another report column (Agreement Number) in another subject area. But target report is not filtering with value as when i click on value (BPA-TAWSEEL-10) in first report. Displaying complete report.

    Formula:

    1st report screen shot:

    pastedImage_0.png

    '<a href=saw.dll?Go&Path=/Shared/Custom/Procurement/BPA%20Details%20report&Action=Navigate&P0=1&P1=eq&P2="Blanket Agreement Header Detail"."Agreement Number"&P3="- Purchase Order Line Detail"."Source Agreement">'||'Link'||'</a>'

    "BPA Details report" is target report and built with another subject area. Column to filtered is Agreement Number.

    Please let me know what is wrong with code written.

    Regards,

    Chandra.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Hi,

    two things to check; -

    1. Make your prompt visible if it is not. Click on your value that invokes the navigation, but make sure it does it in a new window - go back to the original window, did the prompt change to the value that you just clicked on?

    2. On the target page add a title / text area or similar and display the parameter that you should have set in 1 above

    If point one fails then check your parameter corresponds to the column that you are drilling from

    If point two fails then check the syntax on the parameter that is being set by the prompt

    Also, I assume that you have added a filter to the column on the target to filter the value explicitly to the presentation variable?

    Hope this helps,

    Robert.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    I don't currently have access to OBIEE, but there is a means of generating the links - which takes most of the work out of GO_URL, so go the target report, filter it as you need and then capture the url at that point and compare with what you have.

    See the below; -

    https://docs.oracle.com/cd/E10383_01/doc/bip.1013/e10416/dashbdlinks.htm

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Robert,

    As and when i click on the value in the report 1, my prompt does not change. Please see the following steps:

    1. Report 1 has source agreement column.

    pastedImage_0.png

    2. Dashboard prompt created and presentation variable:

    pastedImage_1.png

    3. In dashboard properties set hidden dashboard prompt to filter.

    pastedImage_3.png

    4. In the dashboard pulled report1, and dashboard prompt.

    pastedImage_2.png

    5. I clicked on BPA-TAWSEEL-10 value (action link to target report) now source agreement prompt does not show anything. Target report is displaying in another without any result.

    pastedImage_4.png

    Target report:

    pastedImage_5.png

    Please let me know how to resolve this.

    Regards,

    Chandra.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Hi,

    it seems you have the filter as the explicit value 'pv_agreementvar' - this will never work, unless you have an agreement of that name!?

    When you create the filter one of the options is to use a presentation value as the equal to / in value, you need to use this and it will then pull the variable, not use the variable name as a literal as you have currently, see =>

    pastedImage_0.png

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Robert,

    I have done in similar way as you explained.

    For target report the filter applied as shown below. For agreement number column, filter created as shown. But no result

    pastedImage_0.png

    Regards,

    Chandra.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    On your previous slide where you show the prompt I cannot see the value that you have created the prompt on, is it definitely the column that you are invoking the drill behaviour from?

    Also check the scope of your dashboard prompt - is it global?

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Robert,

    I am asking in the first dashboard page where my first report and prompt with same column resides. As i click on the value in the report, prompt should catch that value but it is not.

    As you mentioned in your one of the reply:

    say your source report has 3 columns A,B,C that need to be prompted and various others that don't.

    You create a dashboard prompt for each of the three and you set variables in the dashboard prompt for each {MyVarA} {MyVarB} {MyVarC}

    When you click on a row of data you will see that the prompts "catch" the values you click on setting the prompt to the values in the row.

    Let us come to my report:

    I have a first report with one column and its name is: Source Agreement.

    I have created dashboard prompt for the same column and has presentation variable name: pv_agreementvar.

    I have placed the report (with one column) and prompt in the dashboard page. As click on the value the prompt should capture the value but it is not.

    Please see the steps for the screen shots:

    1. Report with one column:

    pastedImage_1.png

    2. Dashboard prompt with the same column as shown in the report:

    pastedImage_2.png

    3. I have placed the above report and prompt in the dashboard page:

    pastedImage_3.png

    4. My requirement is when i click on the value for ex: BPA-TAWSEEL-11 in the report. then below prompt has to capture the value. But it is not capturing the value. Why? I followed the steps you have mentioned.

    Regards,

    Chandra.

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    And did you check the scope of the variable is not restricted to page?

    And is your prompt coming from the same subject area as the one in the report?

    Can you put a text or heading on the same source page to ascertain if the variable is being set?

    pastedImage_0.png

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Robert,

    Above report and prompt created from same subject area.

    I have created the static text, but it is not showing any value after clicking the value in the report.

    pastedImage_0.png

    Regards,

    Chandra

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Can you repeat the test, I would try a shorter name, all upper case, no special characters, see if it makes any difference.

    If it doesn't make any difference I would log an SR, this is standard functionality and should work!

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    On should work -  does the variable get set if you actually use the prompt and select a value and click go?