1 2 Previous Next 20 Replies Latest reply on Feb 22, 2019 4:49 PM by JasonSA

    Badge Color Coding

    JasonSA

      Hello,

       

      I have a question regarding the color coding of badges.  We are currently running APEX version 5.1.2.  I would like to make reference to the following thread between Keegan Walker (who I work with) and Pierre Yotti:

       

      https://community.oracle.com/thread/4134119 

       

      I understand the concepts outlined in this post, however, it doesn’t completely meet the needs of the report I’m doing.  In Keegan’s example, she wants to color code based on the count of the badge.  She is making reference to that count in her javascript this way (highlighted in yellow):

       

          if (parseInt($(this).text()) >= 1 && parseInt($(this).text()) < 3)

          {

       

       

            $(this).css({

      "background-color": "orange"

            });

       

       

            $(this).css({

      "box-shadow": "0 0 0 1px #ed813e inset"

            });

       

       

      $(this).find("a").find("a").css({

              "color": "yellow"

            });

          }

       

       

      I have the same kind of badges as Keegan, but my count is an overall count of both “Widgets that are coming due” and Widgets that are past due.  So one of my badges could have a count of 750, 600 of which might be coming due, while 150 are past due.  If the count in the badge has some overdue counts in it, I want to set the color red, if there are no past due counts (only coming due counts), I want to set the color to yellow.

       

      Naturally I want to replace $(this).text() with a sql statement that would be something like this:

       

      I (parseInt(select count(*) from table where pastdue = ‘Y’)) > 0 set badge to red.

       

      I understand that isn’t possible in this context, but I wanted to get the idea across.  So, how would I go about achieving this? Originally I thought I could put a case statement in the SQL of my badge report and set the color there.  The same way I do with Interactive Reports, but that doesn’t seem to work.  If I’m not being clear enough or if you need more info, please let me know.

       

      Thanks,

      Jason

        • 1. Re: Badge Color Coding
          fac586

          JasonSA wrote:

           

          Hello,

           

          I have a question regarding the color coding of badges. We are currently running APEX version 5.1.2. I would like to make reference to the following thread between Keegan Walker (who I work with) and Pierre Yotti:

           

          https://community.oracle.com/thread/4134119

           

          I understand the concepts outlined in this post, however, it doesn’t completely meet the needs of the report I’m doing. In Keegan’s example, she wants to color code based on the count of the badge. She is making reference to that count in her javascript this way (highlighted in yellow):

           

          if (parseInt($(this).text()) >= 1 && parseInt($(this).text()) < 3)

          {

           

           

          $(this).css({

          "background-color": "orange"

          });

           

           

          $(this).css({

          "box-shadow": "0 0 0 1px #ed813e inset"

          });

           

           

          $(this).find("a").find("a").css({

          "color": "yellow"

          });

          }

           

           

          I have the same kind of badges as Keegan, but my count is an overall count of both “Widgets that are coming due” and Widgets that are past due. So one of my badges could have a count of 750, 600 of which might be coming due, while 150 are past due. If the count in the badge has some overdue counts in it, I want to set the color red, if there are no past due counts (only coming due counts), I want to set the color to yellow.

           

          Naturally I want to replace $(this).text() with a sql statement that would be something like this:

           

          I (parseInt(select count(*) from table where pastdue = ‘Y’)) > 0 set badge to red.

           

          I understand that isn’t possible in this context, but I wanted to get the idea across. So, how would I go about achieving this? Originally I thought I could put a case statement in the SQL of my badge report and set the color there. The same way I do with Interactive Reports, but that doesn’t seem to work. If I’m not being clear enough or if you need more info, please let me know.

          Don't think this is possible using the Badge List report template. It is a generic column column template so it only supports the rendering of two pieces of information: the column name and value. There is therefore no way to get your third data point—the existence of overdue counts—into the generated mark-up for use by any conditional logic.

           

          I would suggest trying the list implementation of the Badge List pattern rather than the report. The list template enables the #A02# and #A03# placeholders to be used to include

          custom HTML attributes:

           

          <li class="t-BadgeList-item #A02#">
            <span class="t-BadgeList-label">#TEXT#</span>
            <span class="t-BadgeList-value"><a href="#LINK#" #A03#>#A01#</a></span>
          </li>
          

           

          In this example I have created a dynamic list in Shared Components based on the following query:

           

          with requests_data as
          (select
              d.department_name
            , count(*)                                                    n_requests
            , count(case when e.hire_date > date '2007-04-01' then 1 end) n_due
          from
              departments d
                join employees e
                  on d.department_id = e.department_id
          group by
              d.department_name)
          select
              null              lvl
            , department_name  text
            , '#'              url
            , 'NO'              is_current
            , null              img
            , null              img_attr
            , null              img_alt
            , n_requests        a01
            , null              a02
            , apex_string.format('data-requests="%s" data-requests-due="%s"', n_requests, n_due) a03
          from
              requests_data
          

           

          This uses #A03# to inject data-* attributes containing the total number of requests and the number of requests due. These values can be accessed in JavaScript or—as in this case—used as selectors to apply the required styling in the page Inline CSS property:

           

          #requests .t-BadgeList-value a {
            background-color: #e95b54;
            color: #fff;
            box-shadow: 0 0 0 1px #e95b54 inset;
          }
          #requests .t-BadgeList-value a[data-requests-due="0"] {
            background-color: #fbce4a;
            color: #000;
            box-shadow: 0 0 0 1px #fbce4a inset;
          }
          
          • 2. Re: Badge Color Coding
            JasonSA

            Thank you very much for your response.  I tried creating the dynamic list but have little else to go on in terms of how to make it work.  I’m not an expert in HTML so I’m having a hard time translating what you have in your HTML screenshots to what needs to be done in the dynamic list and JavaScript. The following is the simple SQL query I originally used to create the badges:

             

            select

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W1' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_1,

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W2' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_2,

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W3' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_3,

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W4' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_4,

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W5' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_5,

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W6' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_6,

            (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W7' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_7

            from dual

             

            I’m not too sure how to translate this to the SQL statement that I need to put into a dynamic list with both the coming due and overdue counts.  Would you be able to show me how to do that using my sql as an example?

             

            Also, I was thinking, what if I created 7 more badges along with the original 7 that contained the overdue counts.  Would I then be able to use JavaScript to color 1 badge based on the count of another? Those extra 7 overdue count badges could be hidden in the report so that only the original badges can be seen.  The viewable badges would essentially be colored based on the counts of the hidden ones.  Is that possible?

            • 3. Re: Badge Color Coding
              fac586

              JasonSA wrote:

               

              Thank you very much for your response. I tried creating the dynamic list but have little else to go on in terms of how to make it work. I’m not an expert in HTML so I’m having a hard time translating what you have in your HTML screenshots to what needs to be done in the dynamic list and JavaScript.

              There is no HTML or JavaScript involved, just the built-in Badge List list template and a little extra CSS.

              The following is the simple SQL query I originally used to create the badges:

               

              select

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W1' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_1,

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W2' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_2,

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W3' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_3,

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W4' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_4,

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W5' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_5,

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W6' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_6,

              (select count(*) from WIDGET_TABLE where WIDGET_CODE = 'W7' and (coming_due_flag = 'Y' or overdue_flag = 'Y')) as Widget_7

              from dual

               

              I’m not too sure how to translate this to the SQL statement that I need to put into a dynamic list with both the coming due and overdue counts. Would you be able to show me how to do that using my sql as an example?

              Dynamic list definition:

               

              with widgets as (
                    select
                        w.widget_code
                      , count(*)                                       n_total
                      , count(case when overdue_flag = 'Y' then 1 end) n_overdue
                    from
                        widget_table w
                    where
                        w.coming_due_flag = 'Y'
                    or  w.overdue_flag = 'Y'
                    group by
                        w.widget_code)
              select
                  null          lvl
                , widget_code   text
                , '#'           url
                , 'NO'          is_current
                , null          img
                , null          img_attr
                , null          img_alt
                , n_total       a01
                , null          a02
                , apex_string.format('data-widgets="%s" data-overdue="%s"', n_total, n_overdue) a03
              from
                  widgets
              order by
                  widget_code
              

               

              Page Inline CSS:

               

              #requests .t-BadgeList-value a {  
                background-color: #e95b54;  
                color: #fff;  
                box-shadow: 0 0 0 1px #e95b54 inset;
                cursor: default;
              }  
              #requests .t-BadgeList-value a[data-overdue="0"] {  
                background-color: #fbce4a;  
                color: #000;  
                box-shadow: 0 0 0 1px #fbce4a inset;  
              }  
              

               

              Also, I was thinking, what if I created 7 more badges along with the original 7 that contained the overdue counts. Would I then be able to use JavaScript to color 1 badge based on the count of another? Those extra 7 overdue count badges could be hidden in the report so that only the original badges can be seen. The viewable badges would essentially be colored based on the counts of the hidden ones. Is that possible?

              Possible? Yes. Sensible? ...

              • 4. Re: Badge Color Coding
                JasonSA

                The dynamic list now produces 4 badges (the only ones with counts).  I need to get the ones with 0 counts as well.  They stack on top of each other and the labels and counts overlap.  Color coding doesn't seem to work.  Also. I noticed that going this route does not produce the columns in the Designer, so I can't access the column properties.  I'll try to work with what I have and figure it out.  If I can't make it work, I may have to try something else.  Thanks for your input.  Very much appreciated

                • 5. Re: Badge Color Coding
                  fac586

                  JasonSA wrote:

                   

                  The dynamic list now produces 4 badges (the only ones with counts). I need to get the ones with 0 counts as well. They stack on top of each other and the labels and counts overlap. Color coding doesn't seem to work. Also. I noticed that going this route does not produce the columns in the Designer, so I can't access the column properties. I'll try to work with what I have and figure it out. If I can't make it work, I may have to try something else. Thanks for your input. Very much appreciated

                  It is difficult to provide detailed instructions when we don't have access to the database objects and data involved.

                   

                  Dynamic list definition:

                   

                  with widgets as (
                        select
                            w.widget_code
                          , count(case when w.coming_due_flag = 'Y' then 1 end)  n_total
                          , count(case when w.overdue_flag = 'Y' then 1 end)      n_overdue
                        from
                            widget_table w
                        group by
                            w.widget_code)
                  select
                      null          lvl
                    , widget_code   text
                    , '#'           url
                    , 'NO'          is_current
                    , null          img
                    , null          img_attr
                    , null          img_alt
                    , n_total       a01
                    , null          a02
                    , apex_string.format('data-widgets="%s" data-overdue="%s"', n_total, n_overdue) a03
                  from
                      widgets
                  order by
                      widget_code
                  
                  
                  

                   

                  Page Inline CSS:

                   

                  #requests .t-BadgeList-value a {
                    background-color: #e95b54;
                    color: #fff;
                    box-shadow: 0 0 0 1px #e95b54 inset;
                    cursor: default;
                  }
                  #requests .t-BadgeList-value a[data-overdue="0"] {
                    background-color: #fbce4a;
                    color: #000;
                    box-shadow: 0 0 0 1px #fbce4a inset;
                  }
                  
                  • 6. Re: Badge Color Coding
                    JasonSA

                    Again thanks.  The update brought my zero counts.  I'm sure I can get the coloring figured out.. However making the badge list a list instead of a report takes away my columns in the designer and I need to be able to link those badges to other pages/reports and pass the count parameter.  So my question is this, can I maintain the badge list as a report type, create another set of badges with the overdue counts, make those badges hidden and use their values in the javascript to color the original set of badges.  I'm trying to understand what that would look like in the javascript.  I think I would have to be specific about the badges I'm taking the count from and the badges that I'm coloring.  Right now the javascript loops through each badge in the list and uses (this) to make reference.  Is there a way for me to specify the badges and color accordingly?

                    • 7. Re: Badge Color Coding
                      fac586

                      JasonSA wrote:

                       

                      Again thanks. The update brought my zero counts. I'm sure I can get the coloring figured out.

                      The colouring is working using the code posted above in my 5.1 installation, and in apex.oracle.com with slight modifications to accommodate changes to the Badge List template.

                      However making the badge list a list instead of a report takes away my columns in the designer and I need to be able to link those badges to other pages/reports and pass the count parameter. So my question is this, can I maintain the badge list as a report type, create another set of badges with the overdue counts, make those badges hidden and use their values in the javascript to color the original set of badges.

                      XY question. There is absolutely no need for anything remotely as complex as this.

                       

                      Lists are a navigation component and include links by default. Since there was no mention of links or navigation in the original problem statement, I purposely eliminated this in order to create a static, more report-like presentation. If links are required, then:

                       

                      • Generate the required target URLs in the URL column of the list query. The apex_page.get_url API method is a good way to do this in list queries.
                      • Remove the cursor:default property from the CSS.

                      I'm trying to understand what that would look like in the javascript. I think I would have to be specific about the badges I'm taking the count from and the badges that I'm coloring. Right now the javascript loops through each badge in the list and uses (this) to make reference. Is there a way for me to specify the badges and color accordingly?

                      I'm trying to understand why you are so keen to use JavaScript. There is really no reason to do so where declarative or data-based alternatives exist.

                      • 8. Re: Badge Color Coding
                        JasonSA

                        My apologies for not mentioning the links.  I'm not really keen on using JavaScript at all.  The original example I went by (another app) used JavaScript to color badges.  I'm new to this part of APEX and I haven't really used badge lists before nor have I used JavaScript within APEX.  I'm still learning.  I will try your suggestions on creating links, and if there is a way to color badges without using JavaScript, please let me know.  Again, thanks for all your help, and I hope I didn't waste too much of your time by not telling you about the links.

                        • 9. Re: Badge Color Coding
                          JasonSA

                          I'm still trying to make the color coding work for the badges.  I've put the code you provided in the Inline CSS of the page itself.  I understand what it's supposed to be doing but I can't generate a color for any of the badges.  I even tried using the other data item (data-widgets) to no avail.  I also tried using different hex codes for different colors as well as specific color names like red and yellow.  I'm wondering if the javascript is even triggering. I can misspell words in there and remove a brace, and I get no error which makes me think it's not triggering.  However, I do see it in the HTML output and it looks like this:

                           

                          <style type="text/css">

                              #requests .t-BadgeList-value a { 

                                background-color: #e95b54; 

                                color: #fff; 

                                box-shadow: 0 0 0 1px #e95b54 inset;   

                              } 

                              #requests .t-BadgeList-value a[data-overdue="0"] { 

                                background-color: #fbce4a; 

                                color: #000; 

                                box-shadow: 0 0 0 1px #fbce4a inset;

                              }

                          </style>

                           

                          Not sure what else to do.  Also, could you point me in the right direction in terms if how to use apex_page.get_url?  I;m not sure where this would go or how to implement it.  I couldn't find anything in the APEX documentation that was useful.

                          • 10. Re: Badge Color Coding
                            fac586

                            JasonSA wrote:

                             

                            I'm still trying to make the color coding work for the badges. I've put the code you provided in the Inline CSS of the page itself. I understand what it's supposed to be doing but I can't generate a color for any of the badges. I even tried using the other data item (data-widgets) to no avail. I also tried using different hex codes for different colors as well as specific color names like red and yellow. I'm wondering if the javascript is even triggering. I can misspell words in there and remove a brace, and I get no error which makes me think it's not triggering. However, I do see it in the HTML output and it looks like this:

                             

                            <style type="text/css">

                            #requests .t-BadgeList-value a {

                            background-color: #e95b54;

                            color: #fff;

                            box-shadow: 0 0 0 1px #e95b54 inset;

                            }

                            #requests .t-BadgeList-value a[data-overdue="0"] {

                            background-color: #fbce4a;

                            color: #000;

                            box-shadow: 0 0 0 1px #fbce4a inset;

                            }

                            </style>

                            My bad. Just spotted that I missed a step in the instructions.

                             

                            Add a Static ID to the list region—the example uses "requests". This is used as a unique selector in the CSS rules to target only the badges in that region.

                             

                            You can use any valid identifier you want here, but exactly the same value must be used in the region attribute value and the name after the "#" in the CSS rules.

                            Not sure what else to do. Also, could you point me in the right direction in terms if how to use apex_page.get_url? I;m not sure where this would go or how to implement it. I couldn't find anything in the APEX documentation that was useful.

                            Are you familiar with the APEX URL syntax? apex_page.get_url generates a URL in this format, and automatically adds a checksum to support session state protection if required.

                             

                            A really nice feature of this API method is that it supplies default values from the current session/app/page context, so you only have to include those that vary according to the requirements.

                             

                            What page(s) are targeted by the links and what item parameter(s) do they set?

                            • 11. Re: Badge Color Coding
                              JasonSA

                              I'm not that familiar with the syntax nor do I know where it goes, but if I have an example, and along with the documentation, I'll learn how it works.  Basically, the badges represent counts for business sectors.  I want them to link to a page that has the same kind of badges but for business units (business sectors contain more than one business unit).  All I want to be able to do is click on the sector badge, pass the sector code, and use the sector code to generate counts for the business units (breaking the sector count down by business unit).  From there I may want to break it down further, and then eventually link to an interactive report (detail) when the counts can't be broken down further.  I think if I could figure out how to link to another set of badges and pass the sector code or anything else, I could figure the rest out from there.

                               

                              So basically, the main badges for sector are on page 3 (which you've been helping me with).  I want to drill down to the next level passing the sector code to page 4.  I have to be careful what I'm posting as it's proprietary data.  If you need more info, please let me know.

                              • 12. Re: Badge Color Coding
                                JasonSA

                                By the way, I got the color coding to work.  Funny, I was wondering what 'requests' stood for, while at the same time I was wondering if a static ID was needed.  I didn't connect the two.  Thanks very much for that.  I'm glad we got that to work.

                                • 13. Re: Badge Color Coding
                                  fac586

                                  JasonSA wrote:

                                   

                                  I'm not that familiar with the syntax nor do I know where it goes, but if I have an example, and along with the documentation, I'll learn how it works. Basically, the badges represent counts for business sectors. I want them to link to a page that has the same kind of badges but for business units (business sectors contain more than one business unit). All I want to be able to do is click on the sector badge, pass the sector code, and use the sector code to generate counts for the business units (breaking the sector count down by business unit). From there I may want to break it down further, and then eventually link to an interactive report (detail) when the counts can't be broken down further. I think if I could figure out how to link to another set of badges and pass the sector code or anything else, I could figure the rest out from there.

                                   

                                  So basically, the main badges for sector are on page 3 (which you've been helping me with). I want to drill down to the next level passing the sector code to page 4. I have to be careful what I'm posting as it's proprietary data. If you need more info, please let me know.

                                  Use the function in the URL column of the list query, passing the number or alias of the target page and the names and values of any parameter items:

                                   

                                  with widgets as (
                                        select
                                            w.widget_code
                                          , count(case when w.coming_due_flag = 'Y' then 1 end)  n_total
                                          , count(case when w.overdue_flag = 'Y' then 1 end)     n_overdue
                                        from
                                            widget_table w
                                        group by
                                            w.widget_code)
                                  select
                                      null                              lvl
                                    , widget_code                       text
                                    , apex_page.get_url(
                                          p_page   => '4'
                                        , p_items  => 'p4_widget_code'
                                        , p_values => widget_code)      url
                                    , 'NO'                              is_current
                                    , null                              img
                                    , null                              img_attr
                                    , null                              img_alt
                                    , n_total                           a01
                                    , null                              a02
                                    , apex_string.format('data-widgets="%s" data-overdue="%s"', n_total, n_overdue) a03
                                  from
                                      widgets
                                  order by
                                      widget_code
                                  
                                  • 14. Re: Badge Color Coding
                                    JasonSA

                                    Thanks very much.  I'm still working on this among other things.  Once complete, I will let you know and we can mark this thread as "answered"

                                    1 2 Previous Next