This discussion is archived
7 Replies Latest reply: Feb 21, 2013 2:42 PM by jrimblas RSS

Help Creating A Link In An IR Report Passing The Value From A Different Col

Joe R Explorer
Currently Being Moderated
Version 4.0.2.00.07

Hello,

I have an Interactive Report with a column that I need to create a link on but I need to pass the value of another column.

This report shows the Features of a particular Release. There is a select list for the Release that is selected and executes the report.
There is a column that indicates whether the Feature has a Commitment attached to it. If there is have that a link to another report that shows all the Commitments for that Feature by passing the FEATURE_ID which is also a column on the Feature report.

Query:
SELECT
....
      ,CASE WHEN ft.feature_id IN (SELECT cc.feature_id
                                   FROM customer_commitments cc
                                   WHERE ft.feature_id = cc.feature_id
                                  ) THEN 'Yes' ELSE 'No'
       END "Commitment Exists"
          ,ft.feature_id
....
FROM ....
If "Commitment Exists" has a 'Yes', the 'Yes' is a link that will pass the FEATURE_ID to another report that will show the Commitments for that Feature. If the "Commitment Exists" has a 'No' then no link.
I was also wondering if the Commitment report could open in a seperate window and make it modal?

I hope this is clear and someone could help me.
Please let me know if it's not clear or more information is needed.

Thanks,
Joe
  • 1. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    Joe R Explorer
    Currently Being Moderated
    Hello,

    Does anyone have any ideas?

    Thanks,
    Joe
  • 2. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    jrimblas Expert
    Currently Being Moderated
    To accomplish this I would build the link as part of the value. So if Yes the Yes itself would be a link, if No is simply text as today.
    It would look something like this:
    SELECT
    ....
          ,CASE WHEN ft.feature_id IN (SELECT cc.feature_id
                                       FROM customer_commitments cc
                                       WHERE ft.feature_id = cc.feature_id
                                      )
           THEN
            '<a href="f?p=&APP_ID.:NEW_PAGE:&SESSION.::::Pn_FEATURE_ID:' || ft.feature_id || '">Yes</a>'
           ELSE
            'No'
           END "Commitment Exists"
              ,ft.feature_id
    ....
    FROM ....
    One problem with this approach is when you export the report (as a CSV for example). There are a few ways to handle this, one is to simply duplicate the column with clear text yes and no and set this column to be included on export and only visible on export. The other column, with the link, set it to no export.
    The other option is to add another condition on the REQUEST value.
    SELECT
    ....
          ,CASE WHEN ft.feature_id IN (SELECT cc.feature_id
                                       FROM customer_commitments cc
                                       WHERE ft.feature_id = cc.feature_id
                                      )
           THEN
                  decode(:REQUEST, 'CSV', 'Yes'
                , '<a href="f?p=&APP_ID.:NEW_PAGE:&SESSION.::::Pn_FEATURE_ID:' || ft.feature_id || '">Yes</a>'
              )
           ELSE
            'No'
           END "Commitment Exists"
              ,ft.feature_id
    ....
    FROM ....
    It seems that the link itself is getting replaced with a #. The link would look something like this:
    'f?p=&APP_ID.:NEW_PAGE:&SESSION.::::Pn_FEATURE_ID:' || ft.feature_id

    Hope this helps.
    Oh and of course, in order for the link to display you need to change the Display Type to "Standard Report Column" otherwise the HTML will be escaped.

    Thanks
    -Jorge
  • 3. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    jrimblas Expert
    Currently Being Moderated
    Oh and to make the new window modal you can use the SkillBuilkders Modal Page Plugin.
    Add a class to the link, for example class="feature_details"

    Then the modal window jQuery selector would look like this:
    $(".feature_details")
    And the destination can be as-is by default using the href attribute of the link.

    -Jorge
  • 4. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    Joe R Explorer
    Currently Being Moderated
    Jorge,

    Thank you very much for replying!

    I'm implementing your ideas now.

    Thank you!
    Joe
  • 5. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    jrimblas Expert
    Currently Being Moderated
    Great!
    Remember to mark the thread as answered so that others known its status.

    But post a follow up if you have problems with it.
    -Jorge
  • 6. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    Joe R Explorer
    Currently Being Moderated
    Jorge,

    I'm hoping you can help me with the plug-in.

    I have the link working in the query:
    SELECT
    ....
          ,CASE WHEN ft.feature_id IN (SELECT cc.feature_id
                                       FROM customer_commitments cc
                                       WHERE ft.feature_id = cc.feature_id
                                      ) THEN DECODE(:REQUEST, 'CSV', 'Yes', '<a class=feature_commitments href=f?p=&APP_ID.:72:&SESSION.::NO::P72_FEAT_NUM_DESC_SRCH:'||ft.feature_id||'||'><font color="blue"><u>Yes</u></font></a>')
                                        ELSE 'No'
           END "Commitment_Exists"
    ....
    FROM feature ft
    ...
    The URL is: 'a class=feature_commitments href=f?p=&APP_ID.:72:&SESSION.::NO::P72_FEAT_NUM_DESC_SRCH:'||ft.feature_id||'||'>font color="blue">u>Yes /u>/font>/a>')
    (I removed the < from the beginning of the anchor tag and the underline tag and the closeing underline, font, and anchor tags so the URL would show up)

    On the plug-in:
    I have a Dynamic Action
    When:
    Event -> Click
    Selection Type -> jQuery Selector
    jQuery Selector -> $(".feature_commitments")

    Under True Actions:
    Action -> SkillBuilders Modal Page (2.0.0) [Plug-In]

    Execution Options:
    Fire When Event Result Is -> True

    Settings:
    URL Location -> Statically Defined
    Static URL -> f ?p=&APP_ID.:1:&APP_SESSION.:::1:::
    Auto-close On Element Selector -> div#success-message

    When I bring up the Developer Tools and I click on Console there is an error: SCRIPT5022: Exception thrown and not caught

    Can you see what I'm doing wrong?

    Thanks,
    Joe
  • 7. Re: Help Creating A Link In An IR Report Passing The Value From A Different Col
    jrimblas Expert
    Currently Being Moderated
    Two things:
    1. Change the jQuery selector to simply:
    .feature_commitments

    The $() is redundant when you specify jQuery Selector.

    2. Instead of URL Location -> Statically Defined
    Use Attribute of Triggering Element and let it use href as the Attribute Name. This will allow the plugin to use whatever URL you have defined in the report.

    Thanks
    -Jorge

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points