1 2 Previous Next 15 Replies Latest reply: Nov 22, 2012 2:52 AM by Shunt RSS

    Use filter in an Interactive reporting

    Apex4.0+HTML_DB1.6
      Hi everyone,

      We’re developing with APEX 4.1.

      I have a page with an interactive reporting. I have different columns.

      In the Region Source, one of them:

      SELECT ……..
      …..
      DECODE(ACT.INACTIVE_INDICATOR,1,'<img src="&APP_RESOURCES_EXTERNES.themes/theme_13/check2.gif">',' ') Inactive
      ….
      ….
      Like we’ve seen before, if our INACTIVE_INDICATOR is true (1) we show a check sign if not nothing will be shown.

      But, when we do a Filter on this column, the url is shown. But, it’s not beautiful to see. Is it possible to show the check sign or something different than the url?

      In general, is it possible to control the Filter options ?

      Thanks. Bye.
        • 1. Re: Use filter in an Interactive reporting
          matthew_morris
          Is it possible to show the check sign or something different than the url?
          No. Special characters are being escaped in the IR filter and there is no means of altering that. I tried a couple of variants just to see if it could be finessed, and the results were always the same.
          • 2. Re: Use filter in an Interactive reporting
            714270
            In general, is it possible to control the Filter options ?
            The column filter options can be controlled using the "List of Values" option under "Column Attributes".

            Thanks,
            Rohit
            • 3. Re: Use filter in an Interactive reporting
              Apex4.0+HTML_DB1.6
              Thank you for your help Mr. Matthew Morris

              A part of the message have been answered "Is it possible to show the check sign or something different than the url?"


              Bye.
              • 4. Re: Use filter in an Interactive reporting
                Apex4.0+HTML_DB1.6
                Thank you Mr Kapoor,

                I'm not sure to understand what I can do with LOV column filter options ?

                Can you tell me more about that ?

                Bye.
                • 5. Re: Use filter in an Interactive reporting
                  714270
                  Hi,

                  The LOV filter options help you override the default filter query for the columns on the reports. The following is from the help text of the option:

                  Select how to derive list of values as filter on the report column drop down list. Available options include:
                  None - Disable filter on list of values.
                  Default Based on Column Type - STRING and NUMBER columns derive the list of values from distinct column values. DATE columns derive the list of values from predefined date ranges.
                  Use Defined List of Values to Filter Exact Match - Derive the list of values from a defined list of values SQL query to create an equal filter.
                  Use Defined List of Values to Filter Word Contains - Derive the list of values from a defined list of values SQL query to create a contains filter.
                  Use Named List of Values to Filter Exact Match - Derive the list of values from the named list of values to create an equal filter.
                  Use Named List of Values to Filter Word Contains - Derive the list of values from the named list of values to create a contains filter.


                  Thanks,
                  Rohit
                  • 6. Re: Use filter in an Interactive reporting
                    Shunt
                    You could try using JQuery as follows:

                    1. Change the sql to return a sensible value for the column sorting e.g.

                    SELECT ……..
                    …..
                    DECODE(ACT.INACTIVE_INDICATOR,1,'Yes','No') Inactive
                    ….
                    ….


                    2. Create an Advanced Dynamic Action to replace the 'Yes' value with an image and 'No' with null

                    Event: After Refresh
                    Selection Type: Region
                    Region: [your region]
                    Event: Scope: Bind
                    Action: Execute JS Code
                    Fire when result is: True
                    Fire on page load: Checked

                    Code:
                    $("[headers='INACTIVE']").each(function() {
                      if ($(this).text() == 'Yes') {
                        $(this).html('<img src="&APP_RESOURCES_EXTERNES.themes/theme_13/check2.gif">');
                      }
                      else {
                        $(this).text('');
                      }
                    });
                    Make sure that the IR has a region template :)

                    I hope that helps

                    Shunt
                    • 7. Re: Use filter in an Interactive reporting
                      Apex4.0+HTML_DB1.6
                      Thank you Shunt,

                      I made a modification on my Interactive Reporting Region
                      Select....
                      DECODE(ACT.IND_INACTIF,1,'Oui','Non') Inactive,
                      ....

                      Oui (french) means Yes (english)
                      Non (french) means No (english)

                      I also made a dynamic action

                      Event: After Refresh
                      Selection Type: Region
                      Region: Activités
                      Event: Scope: Bind
                      Action: Execute JS Code
                      Fire when result is: True
                      Fire on page load: Checked

                      headers='Inactive' is it linked to my DECODE(ACT.IND_INACTIF,1,'Oui','Non') Inactive


                      (Code section)

                      $("[headers='Inactive']").each(function() {
                      if ($(this).text() == 'Oui') {
                      $(this).html('<img src="&APP_RESOURCES_EXTERNES.themes/theme_13/check2.gif">');
                      }
                      else {
                      $(this).text('');
                      }
                      }
                      );

                      I'm not sure my dynamic action is fired , no image showed !!

                      Thanks in advance. Bye.
                      • 8. Re: Use filter in an Interactive reporting
                        Shunt
                        The 'headers' value in the IR is likely to be Upper Case; it's the Column Name not the Column Label. You can View Source in the browser of use Firebug to inspect the element to see the value.

                        if that doesn't work try using the alert function to isolate which bit is working and which is not e.g.
                        $("[headers='INACTIVE']").each(function() {
                          alert($(this).text());
                        });
                        I hope that helps. If you still cant get it to work I'll put an example on apex.oracle.com.

                        S
                        • 9. Re: Use filter in an Interactive reporting
                          Apex4.0+HTML_DB1.6
                          Thanlk you Shunt. I appreciate your collaboration.

                          I tried something like this:

                          IND_INACTIF it's my table column name.

                          My code is :

                          $("[headers='IND_INACTIF']").each(function() {
                          alert("I am an alert box_YES!");
                          if ($(this).text() == 'Oui') {
                          $(this).html('<img src="&APP_RESOURCES_EXTERNES.themes/theme_13/check2.gif">');
                          }
                          else {
                          alert("I am an alert box_NO!");
                          }
                          }
                          );

                          But it does'nt work.
                          If you want to it will be very interesting if you'll put an example on apex.oracle.com.

                          Thanks in advance. Bye.
                          • 10. Re: Use filter in an Interactive reporting
                            fac586
                            Apex 4.0 + HTML_DB 1.6 wrote:

                            If you want to it will be very interesting if you'll put an example on apex.oracle.com.
                            This is your problem. Wouldn't it be more appropriate for you to create an example on apex.oracle.com and provide guest developer credentials for the workspace? Had you done so originally, I guarantee that you'd have had a solution on the first day, instead of still waiting 2 weeks later...
                            • 11. Re: Use filter in an Interactive reporting
                              Shunt
                              Your Interactive Report MUST have a region template in order for the Dynamic Action to work; otherwise there is no region ID in the DOM.

                              This is the usual issue with using JQuery on Interactive Reports

                              Shunt
                              • 12. Re: Use filter in an Interactive reporting
                                Apex4.0+HTML_DB1.6
                                Hi Stunt,

                                I apologize for this.

                                Because you told me : "I hope that helps. If you still cant get it to work I'll put an example on apex.oracle.com."
                                that I asked you to put an example on apex.oracle.com.

                                I will check if it's possible to put my case on apex.oracle.com. But, I think my organization won't give me this possibility.

                                For your solution, do I need to add tags on my code ?

                                Thanks. Bye.
                                • 13. Re: Use filter in an Interactive reporting
                                  Shunt
                                  No problem; try to get an example working using the emp table.

                                  Here's one I did, which replaces the value '1' in the JOB column with a pencil icon.

                                  http://apex.oracle.com/pls/apex/f?p=579:63

                                  This is the javascript code in the Dynamic Action.
                                  $("[headers='JOB']").each(function() {
                                    if ($(this).text() == '1') {
                                      $(this).html('<img src="#IMAGE_PREFIX#menu/pencil2_16x16.gif" alt="">');
                                    }
                                    else {
                                      $(this).text('');
                                    }
                                  });
                                  Shunt
                                  • 14. Re: Use filter in an Interactive reporting
                                    Apex4.0+HTML_DB1.6
                                    Thank you very much Stunt.

                                    I really appreciate your great help. Your application is great.

                                    At first, in my interactive reporting region I had this in my select:

                                    DECODE(ACT.IND_INACTIF,1,'<img src="&APP_RESOURCES_EXTERNES.themes/theme_13/check2.gif">',' ') Inactive

                                    If my indicator is 1, I put a check gif in my column. It's ok for this.

                                    Filter

                                    But, our real problem is when we go to Actions Menu and we want a filter on this indicator column, we see
                                    the Url link : img src="&APP_RESOURCES_EXTERNES.themes/theme_13/check2.gif" but we want a check gif.

                                    Thanks. Bye.
                                    1 2 Previous Next