8 Replies Latest reply on Aug 17, 2013 10:47 AM by Sorbsbuster

    Stacked Bar Chart, FM12

    Sorbsbuster

      Title

      Stacked Bar Chart, FM12

      Post

           Great to see that FM 12 has added a stacked bar chart.  Rather than go through all the things I've tried, could someone tip me in the right direction, please?

           I have 4 fields

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                          Production Line                     Product                     Quantity                     Sequence
                          A                     Sausage                     2                     1
                          A                     Burger                     3                     2
                          A                     Steak                     4                     3
                          B                     Chipolata                     1                     1
                          B                     Steak                     2                     2
                          B                     Burger                     3                     3

           And, yes, you've guessed, I want it to display like this:

           Line A    XX YYY ZZZZ

           Line B    X YY ZZZ

           I cannot get it to stack the bar - usng the 'found set' as the data source shows me only the values for the current record.  Sorting by Production Line will bring show only two lines (any other shows one bar per record) but it still only shows the value of the active record.  I have tried adding a second value to the X Axis, but I don't really want a second value - I want the same field, stacked for all the records in the found set.  (If I don't sort the records it just displays like a simple bar chart.)

           My latest attempt is:

           Horizontal axis: Production Line
           Vertical Axis: Quantity
           Sorting: By Production Line, by Sequence
           Show: Summarized values

           That shows:

           Line A XX
           Line B Y

           (In other words summarised by line, but only the first value for each)

           I'm sure it's something simple,

           Thanks for your help,
           Alan

        • 1. Re: Stacked Bar Chart, FM12
          philmodjunk

               You may not want it, but it's the only way to get a stacked bar chart to work. You need a separate data series for each section of the stacked bar. You'd set this up just like you'd do to get more than one line on a line chart.

               One calc field for each possible value in Product:

               If ( Product = "Sausage" ; Quantity )

               One matching summary field for each added calc field.

               Use the summary fields for your different y-axis data series

          • 2. Re: Stacked Bar Chart, FM12
            Sorbsbuster

                 Thanks very much for the tips, Phil.  I actually think that the FM Help on this is misleading to the point of gross over-simplicity:

                 "Each column or bar in the chart compares multiple data points within a category. Use a stacked bar chart to compare the annual sales figures for products over several years. Each segment of each bar compares specific product sales, each bar shows total product sales per year, and the entire chart compares total sales for all years."

                 That simple description is exactly what I understand a stacked bar chart to represent, but the mechanism to achieve it is very contorted.  I would appreciate a 'worked example' from FM Inc showing how they expect it to be used.  The example in their video uses a very limited, and fixed, range of fileds, and with a summary created for each, as you suggest.

                 Once I realised that it was not as simple as it should be, I found this (just confirms your Bad News).

            http://fmforums.com/forum/topic/84648-stacked-column-example/

                 I would want the chart to be extensible, as I can't predict the range of products the company will make.

                 I have created a script to generate an 'Old Skool' chart using pipe characters and setting a colour change between each value, so it draws a 'chart' that looks something like this:

            Line A: |||||||||||||||||||||||||||||||||||||||||||||

            Line B: |||||||||||||||||||||||||||||||||||||

            But it was more as a coffee-time challenge than a serious suggestion.  I would be happy to build up the chart data into a field of tab-delimited and return-separated values, but I can't make that work either.

            • 3. Re: Stacked Bar Chart, FM12
              philmodjunk

              I would be happy to build up the chart data into a field of tab-delimited and return-separated values, but I can't make that work either.

              Hmmm, with the right relationships with calcuations that use List or a few ExecuteSQL functions, I would think that approach might work quite well from a 'chart' table where you could specify the values used to pull together the lists of values for each series without munging up the original data table with a bunch of calculation and summary fields...

              • 4. Re: Stacked Bar Chart, FM12
                Sorbsbuster

                     When I said I couldn't get it to work, I meant that I tried creating the chart from a manually-entered tab-sperated list, and I couldn't see it do anything other than draw a single bar of the first value.  I think I need to explore your suggestion better; understand how it would create the chart that I want, then work out how to generate the appropriate text file.  If I can get my head around the former I think the latter will be easy.  At first glance the only other problem I can see with the sub-summary requirement is that if they schedule the product 'Sausages' to be made in the morning and again in the afternoon, would it join them both together? ( I could probably get around that with sorting on a concatonation of Product & Sequence Number.)

                     In a minor case of a freaky coincidence, or a major case of Big Brother, 10 minutes after your reply and my searching, a TechNet mail arrived, suggesting I look at their latest briefing on... 'Charts'.  I've downloaded it to peruse later.

                     I think the idea of building up the data into a suitable list is the most likely way to go.  I need to explore what the correct format is to build.

                • 5. Re: Stacked Bar Chart, FM12
                  JeromeSimons

                       Dear Sorbsbuster,

                       Have you been able to find a solution? I have the exact same problem but only worse: my sequence number various across production lines.

                       I would greatly appreciate your response.

                       Kind regards,

                       Jerome

                  • 6. Re: Stacked Bar Chart, FM12
                    Sorbsbuster

                         Sorry, Jerome, I couldn't get it to work as I expect 'stacked columns' to work.  Phil's suggestion is still the only way that I can see, and it has limitations as discussed.  All the other negatives raised in the post still stand.

                         I don't quite see that your varying sequence numbers is in itself a problem; that could probably be worked around.  The problem as I see it is FM says you can make a stacked chart, and you can't.  End of.

                         In the end I went with my own fudged graphical representation with the coloured pip characters.

                         If someone can tip me off to how to draw a stacked chart at all, I'm happy to work on generating the data in the right format of tab-separated data.

                    • 7. Re: Stacked Bar Chart, FM12
                      JeromeSimons

                           Well, what I ended up doing is that I made a field for each transaction and just added each field as a new data series. Now, I am hitting the wall with the maximum number of data series a stacked bar chart can display. :-(

                      • 8. Re: Stacked Bar Chart, FM12
                        Sorbsbuster

                             I needed the stacked bar chart to facilitate 'live' planning decisions by the production planner.  They needed to have an instant visual feedback of the frequency and size of their down-time between production runs if they went with the current plan, then click to change it, and immediately see the effect.

                             If I didn't need that immediate interactivity, but would be happy with just a representation of some final result, then I would write a script to export the data to Excel, draw the chart using Excel's extensive charting features, and show that to the user.  You can also embed that Excel object in a container field and let the user see it directly on your FM record, but that last one is a brilliant feature than FM have let languish, so it may not work consistently.