Topic
17 replies Latest Post - ‏2012-04-27T01:13:20Z by SystemAdmin
SystemAdmin
SystemAdmin
3614 Posts
ACCEPTED ANSWER

Pinned topic Top 5 Pie Chart

‏2012-02-29T20:03:57Z |
I'm trying to create a pie chart, "Top 5 Molding Downtime". The chart will show the top 5 reasons for downtime and the percentage of downtime. I'm using Graph Assistant and have created the pie chart, however, it shows all results, not the top 5. How can I get just the top 5 results?? Also, start date and end date are entered as parameters.

Thank you,
Amanda Walsh
Consultech Services, Inc.
Updated on 2012-04-27T01:13:20Z at 2012-04-27T01:13:20Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3614 Posts
    ACCEPTED ANSWER

    Re: Top 5 Pie Chart

    ‏2012-02-29T20:58:31Z  in response to SystemAdmin
    Amanda - this is one of those things that can not be done with Graph Assistant. IA currently does not support it either. I am pretty sure I have done something like this with PowerPainter - I will try to find my example or create a new one.
    • SystemAdmin
      SystemAdmin
      3614 Posts
      ACCEPTED ANSWER

      Re: Top 5 Pie Chart

      ‏2012-02-29T21:09:49Z  in response to SystemAdmin
      Hi Gene,

      Jackie Jansen said the same thing, but I didn't readily see how to do it in power painter. I'll keep trying, and if you can come up with an example that will be great. Also, I don't want an 'Others' category. I just want the top 5 and percentages based on those 5.

      Thanks!
      • SystemAdmin
        SystemAdmin
        3614 Posts
        ACCEPTED ANSWER

        Re: Top 5 Pie Chart

        ‏2012-02-29T21:30:10Z  in response to SystemAdmin
        Amanda - another possibility would be using an SQL view to limit results to top 5.
        • SystemAdmin
          SystemAdmin
          3614 Posts
          ACCEPTED ANSWER

          Re: Top 5 Pie Chart

          ‏2012-03-01T15:38:22Z  in response to SystemAdmin
          Hi Gene, I thought about his as well. However could I create a SQL view and still have parameters a user will enter? (dates)
          • SystemAdmin
            SystemAdmin
            3614 Posts
            ACCEPTED ANSWER

            Re: Top 5 Pie Chart

            ‏2012-03-01T16:26:53Z  in response to SystemAdmin
            hmmmm..that does complicate things. As far as Power Painter being able to do this, I am starting to think it can't but have an email into one of the "WebFOCUS" wizards.

            You're right - adding input parms pretty much eliminates a view. I am starting to think a stored procedure might be the only way to pull this off. The stored proc would have to dynamically build the SQL statement, prepare the statement, and open the cursor.

            The statement would look something like this (using the sample Web Query Century database):

            SELECT storename, sum(linetotal) AS totalrev,
            RANK() OVER(ORDER BY sum(linetotal) DESC) AS rankrev
            FROM orders a
            inner join stores b on a.storecode = b.storecode
            where orderdate >= DATE('01/01/2009')
            and orderdate <= DATE('01/31/2009')
            GROUP BY storename
            fetch first 5 rows only

            You could do this in SQL procedural language or even RPG/COBOL if you are more comfortable with either of those languages. You would just have to create the stored proc as an external one.

            Thoughts on this?
          • SystemAdmin
            SystemAdmin
            3614 Posts
            ACCEPTED ANSWER

            Re: Top 5 Pie Chart

            ‏2012-03-03T19:56:51Z  in response to SystemAdmin
            Okay, I found a way to do this in IA. It takes a couple steps. Here is what I did:

            1. Created a report that had the category and quantity on it.
            2. Sorted by quantity decending
            3. Limit the sort to top x (I did 5)
            4. Click the Chart button on the home tab to generate a new chart based on the report
            5. Change the report type from bar to pie

            The limit will be built in, but not changable.

            Robert Andrews
            Managing Consultant
            IBM Systems & Technology Group, Rochester
            • SystemAdmin
              SystemAdmin
              3614 Posts
              ACCEPTED ANSWER

              Re: Top 5 Pie Chart

              ‏2012-03-13T13:53:04Z  in response to SystemAdmin
              Sorry, got a little side tracked. I'm going to give IA a try. Robert, this will work except I need percentages not totals. Is there a way I could get percentages of just the top 5 using IA??

              Thank you.
              • SystemAdmin
                SystemAdmin
                3614 Posts
                ACCEPTED ANSWER

                Re: Top 5 Pie Chart

                ‏2012-03-13T13:59:58Z  in response to SystemAdmin
                Nevermind, I see when you switch to pie it shows percentages.

                Thank you.
                Amanda Walsh
                • SystemAdmin
                  SystemAdmin
                  3614 Posts
                  ACCEPTED ANSWER

                  Re: Top 5 Pie Chart

                  ‏2012-03-20T13:23:40Z  in response to SystemAdmin
                  Hi again, I also wondering if you can do drill down in IA and if so is there any documentation on how to do so?
                  Thanks.
                  Amanda
                  • SystemAdmin
                    SystemAdmin
                    3614 Posts
                    ACCEPTED ANSWER

                    Re: Top 5 Pie Chart

                    ‏2012-03-20T14:05:44Z  in response to SystemAdmin
                    Amanda - you sure can. Download the InfoAssist tutorial (see section 2.2.5 How to Use Auto Drill & Analysis )
                    https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en#/wiki/W516d8b60d32c_4fc5_a811_5f3d840bf524/page/InfoAssist%20Tutorials
                    • SystemAdmin
                      SystemAdmin
                      3614 Posts
                      ACCEPTED ANSWER

                      Re: Top 5 Pie Chart

                      ‏2012-03-27T13:41:26Z  in response to SystemAdmin
                      So I have created a pie chart, bar chart and report using the same data and filters, etc. Now I am trying to create a compound document in IA which will have all 3 included. All 3 have parameters. I am have issues with this. I can't just insert the pie chart and bar chart that I already created because I get an error message, Java.lang.ClassCastException: ... And I can't create these from scratch because originally, I had to create a report and then turn the report into a chart (because I wanted a limit of 5), and the document compound in IA will not allow me to turn a report into a chart in this mode. Any ideas how I might accomplish this?
                      • SystemAdmin
                        SystemAdmin
                        3614 Posts
                        ACCEPTED ANSWER

                        Re: Top 5 Pie Chart

                        ‏2012-04-04T13:25:42Z  in response to SystemAdmin
                        Any ideas on my last post??
                        • SystemAdmin
                          SystemAdmin
                          3614 Posts
                          ACCEPTED ANSWER

                          Re: Top 5 Pie Chart

                          ‏2012-04-04T13:28:47Z  in response to SystemAdmin
                          You can open a PMR on this. However, development has already said what I discovered is not supported so you may or may not get an answer.

                          Robert Andrews
                          Managing Consultant
                          IBM Systems & Technology Group, Rochester
                          • SystemAdmin
                            SystemAdmin
                            3614 Posts
                            ACCEPTED ANSWER

                            Re: Top 5 Pie Chart

                            ‏2012-04-26T13:24:14Z  in response to SystemAdmin
                            Thank you. I have a few more questions about web query:

                            1. Can you decompose a date into weeks, I need a date in WW YY format?
                            2. In Info Assist, can you do a "group by"?
                            3. If I have a stored procedure and make a change to the stored procedure, can I just refresh the synonym or does a new one have to be created?

                            Thanks for all your help.
                            • SystemAdmin
                              SystemAdmin
                              3614 Posts
                              ACCEPTED ANSWER

                              Re: Top 5 Pie Chart

                              ‏2012-04-27T01:13:20Z  in response to SystemAdmin
                              1. Yes, you can use a defined field to modify a date with one of four formats:

                              W Day of week
                              On its own, W displays the number of the day of the week (1–7, Mon=1). Used in combination with other date options, W displays a 3-letter abbreviation of the day of the week in uppercase.

                              w Day of week
                              Functions as uppercase W (described previously), except that the first letter is uppercase and the following letters are lowercase.

                              WR Day of week
                              Functions the same as uppercase W (described above), except that the entire day name is displayed instead of an abbreviation.

                              wr Day of week
                              Functions the same as lowercase w (described above), except that the entire day name is displayed instead of an abbreviation.

                              2. Yes, the "By" in InfoAssist is both a Group By and Order by already.

                              3. Depends on what you change. If the signature is the same (input/output parms), then no need (i.e. if you just change logic). If you change the input or output parms, then you would need to recreate the synonym.

                              Robert Andrews
                              Managing Consultant
                              IBM Systems & Technology Group, Rochester
                        • SystemAdmin
                          SystemAdmin
                          3614 Posts
                          ACCEPTED ANSWER

                          Re: Top 5 Pie Chart

                          ‏2012-04-10T20:44:02Z  in response to SystemAdmin
                          Amanda - would you be open to using a stored procedure for this? As Robert indicated, its not something fully supported yet. It might be in the future, but in the meantime you run the risk of doing something that won't work and support not being able to help you.

                          A stored proc would be easy enough to write and would give you the input parm flexibility you would need. It could also be used as the data source for your report, graph, and dashboard.

                          Let me know - I could get you started with this.
  • RobHart
    RobHart
    2 Posts
    ACCEPTED ANSWER

    Re: Top 5 Pie Chart

    ‏2012-03-22T03:54:36Z  in response to SystemAdmin
    Hi, If you don't mind being a bit variable on the number reported then check your trends and use the selection criteria to get results higher than the value that will give you close to 5 results.

    I'm fairly new to Web Query so am feeling my way but this is one I have already had to solve for myself. I'll post a question shortly about another problem I have.

    Regards
    Rob