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

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

    Re: Top 5 Pie Chart

    ‏2012-02-29T20:58:31Z  
    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

    Re: Top 5 Pie Chart

    ‏2012-02-29T21:09:49Z  
    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.
    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

    Re: Top 5 Pie Chart

    ‏2012-02-29T21:30:10Z  
    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!
    Amanda - another possibility would be using an SQL view to limit results to top 5.
  • SystemAdmin
    SystemAdmin
    3614 Posts

    Re: Top 5 Pie Chart

    ‏2012-03-01T15:38:22Z  
    Amanda - another possibility would be using an SQL view to limit results to top 5.
    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

    Re: Top 5 Pie Chart

    ‏2012-03-01T16:26:53Z  
    Hi Gene, I thought about his as well. However could I create a SQL view and still have parameters a user will enter? (dates)
    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

    Re: Top 5 Pie Chart

    ‏2012-03-03T19:56:51Z  
    Hi Gene, I thought about his as well. However could I create a SQL view and still have parameters a user will enter? (dates)
    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

    Re: Top 5 Pie Chart

    ‏2012-03-13T13:53:04Z  
    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
    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

    Re: Top 5 Pie Chart

    ‏2012-03-13T13:59:58Z  
    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.
    Nevermind, I see when you switch to pie it shows percentages.

    Thank you.
    Amanda Walsh
  • SystemAdmin
    SystemAdmin
    3614 Posts

    Re: Top 5 Pie Chart

    ‏2012-03-20T13:23:40Z  
    Nevermind, I see when you switch to pie it shows percentages.

    Thank you.
    Amanda Walsh
    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

    Re: Top 5 Pie Chart

    ‏2012-03-20T14:05:44Z  
    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
    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
  • RobHart
    RobHart
    2 Posts

    Re: Top 5 Pie Chart

    ‏2012-03-22T03:54:36Z  
    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
  • SystemAdmin
    SystemAdmin
    3614 Posts

    Re: Top 5 Pie Chart

    ‏2012-03-27T13:41:26Z  
    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
    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

    Re: Top 5 Pie Chart

    ‏2012-04-04T13:25:42Z  
    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?
    Any ideas on my last post??
  • SystemAdmin
    SystemAdmin
    3614 Posts

    Re: Top 5 Pie Chart

    ‏2012-04-04T13:28:47Z  
    Any ideas on my last post??
    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

    Re: Top 5 Pie Chart

    ‏2012-04-10T20:44:02Z  
    Any ideas on my last post??
    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.
  • SystemAdmin
    SystemAdmin
    3614 Posts

    Re: Top 5 Pie Chart

    ‏2012-04-26T13:24:14Z  
    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
    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

    Re: Top 5 Pie Chart

    ‏2012-04-27T01:13:20Z  
    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.
    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