1 2 Previous Next 16 Replies Latest reply on Jan 10, 2014 1:25 PM by Giedrius S.

    Display float numbers in APEX report

    Giedrius S.

      Hello,

       

        I have an  interactive report and simple report based on a table with float numbers. Problem is, float numbers with value between 0 and 1 are shown ".1" not "0.1"

       

      For example, for values 0.1, 1 and 12.1 I get:

       

      .1
      1
      12.1

       

        If I add format mask FM999999999999990D99999 to this field, I fix ".1" value, but value "1" becomes "1." which is bad:

      1

      0.1
      1.
      12.1

       

      What I would like to see in a report is:

       

      0.1
      1
      12.1

       

       

      How could I do it?

       

      I wrote function which makes needed result as a varchar2 value. Data in reports shows fine, but then problem with sorting arised - data is being sorted as text, not as number, for example:

       

      1.1
      11.2
      7

       

      Any ideas?

      Thank you

        • 1. Re: Display float numbers in APEX report
          fac586

          What version of APEX are you using? (Always include this information when asking a question)

          • 2. Re: Display float numbers in APEX report
            Giedrius S.

            Application Express 4.1.1.00.23

            • 3. Re: Display float numbers in APEX report
              fac586

              The technique will only work with interactive reports on APEX 4.2, or with standard reports in previous versions. Is it feasible for you to upgrade to 4.2, or use a standard report instead?

              • 4. Re: Display float numbers in APEX report
                Giedrius S.

                Could you just show what technique are you talking about? Upgrading is not possible now, but I am interested to see. The same problem also is with simple reports.

                • 5. Re: Display float numbers in APEX report
                  fac586

                  Add an additional column to the report query projection that produces the data in the required format:

                   

                  rtrim(to_char(col, 'FM999999999999990D99999'), '.,') col_alias

                   

                  Hide the col_alias column in the report column attributes, and reference it in the HTML Expression property of the original column using #COL_ALIAS# syntax. This will result in the column showing the data in the display format, but sorting it using the original value.

                  1 person found this helpful
                  • 6. Re: Display float numbers in APEX report
                    Giedrius S.

                    Thanks, this is interesting idea, i will try this tomorrow at work.

                    • 7. Re: Display float numbers in APEX report
                      Giedrius S.

                      For interactive reports I found this technique working (similar to yours):

                      • Add an additional column (lets say with name col_alias ) to the report query projection that produces the data in the required format
                      • Make main column with float data as link. Link text should be  #col_alias#, link can refer to any page. Link attributes should contain some class, like: class="fix_float_ir"
                      • Add dynamic action, which removes link after page loads (execute javascript):

                      $(".fix_float_ir").each(function () {

                          $(this).replaceWith($(this).text());

                      });

                       

                      I got simple test case working, thanks for your comment fac586

                       

                      Giedrius

                      • 8. Re: Display float numbers in APEX report
                        fac586

                        If I was forced into using JS for this (as in a 4.1 IR without HTML Expressions), I think that I'd just display the original column and get the DA to trim the trailing decimal points directly, rather than subverting the column link.

                        Add dynamic action, which removes link after page loads (execute javascript)

                        You'll need to use an After Refresh DA (with the Fire on Page Load option checked) on the IR region so it is applied when users go to a new page or apply formatting from the Actions menu.



                        • 9. Re: Display float numbers in APEX report
                          Giedrius S.

                          When i was experimenting, I tried After Refresh DA with this IR, but it did not work. I tried again now, and when i change event type form "Page load" to "After refresh" and set IR region as "selection type" it do not work. When i change type back to "Page load" it works. Page Load option is checked

                           

                          Also your idea of just modifying value. Now every link to be removed has distinct class -  "fix_float_ir", so that dynamic action knows which links to remove. For that I use field "Link attributes". If I am not using link, how to mark values, which should be changed? Not every column in IR is float column which needs to be processed.

                          • 10. Re: Display float numbers in APEX report
                            Giedrius S.

                            Seems that "After Refresh" DA works with simple report. But does not work with IR

                            • 11. Re: Display float numbers in APEX report
                              fac586

                              Giedrius S. wrote:

                               

                              Seems that "After Refresh" DA works with simple report. But does not work with IR

                              It should: see under "Framework Events" in the documentation.

                               

                              Is the IR region set to No Template?

                              • 12. Re: Display float numbers in APEX report
                                Giedrius S.

                                Yes, it was without template. I added it and my test case works now with "After refresh"

                                • 13. Re: Display float numbers in APEX report
                                  fac586

                                  Giedrius S. wrote:

                                   

                                  Also your idea of just modifying value. Now every link to be removed has distinct class -  "fix_float_ir", so that dynamic action knows which links to remove. For that I use field "Link attributes". If I am not using link, how to mark values, which should be changed? Not every column in IR is float column which needs to be processed.

                                  Specify the cells in the required columns using a jQuery attribute selector in the DA:

                                   

                                  .apexir_WORKSHEET_DATA td[headers="COL_ALIAS"]

                                  1 person found this helpful
                                  • 14. Re: Display float numbers in APEX report
                                    Giedrius S.

                                    So finally seems everything is working now. I even do not need additional column with required format.  And i guess it will work on all apex 4 versions

                                     

                                    • Have float column (named lets say ir_float ) and do not use any format on it, leave it as it is. Then the only bad case is ".1" instead of "0.1"
                                    • Add dynamic action after refresh, specify region (which must have template, even for IR) and add java script action:


                                    For interactive reports:

                                    $('.apexir_WORKSHEET_DATA td[headers="ir_float"]').each(function(){   

                                        if(jQuery(this).text().substr(0,1) == '.'){

                                             jQuery(this).prepend('0')

                                        }

                                       })

                                     

                                    For classic reports:

                                    $('#R930945330216409887 td[headers="ir_float"]').each(function(){   

                                        if(jQuery(this).text().substr(0,1) == '.'){

                                             jQuery(this).prepend('0')

                                        }

                                       })

                                     

                                    BIG thanks for your ideas fac586

                                     

                                    Giedrius

                                    1 2 Previous Next