7 Replies Latest reply: Dec 3, 2012 3:21 PM by Kenny Hanberg RSS

    Multi-series line chart

    528063
      Hello everyone, I am trying to create a multi-series line chart as follows:
      My table is ( Class, Name, X1, X2, X3, X4 ).
      My select statement will be: select Name, X1, X2, X3, X4 From tbl1 where Class = 4
      This select will return multiple records based on Class, number of returned records may range (1-250) records.

      Is it possible to implement this in APEX?

      Any help is appreciated!
      Thanks
        • 1. Re: Multi-series line chart
          Matthew Morris
          Is it possible to implement this in APEX?
          It is possible to implement charts with multiple series in Apex. You'll do this with multiple SELECTs, each representing a different series. Using your example, they'll look something like this:
          Series 1
          SELECT NULL AS LINK,
                 Name AS LABEL, 
                 X1   AS VALUE
          FROM  tbl1
          WHERE  Class = 4;
          
          Series 2
          SELECT NULL AS LINK,
                 Name AS LABEL, 
                 X2   AS VALUE
          FROM  tbl1
          WHERE  Class = 4;
          
          Series 3
          SELECT NULL AS LINK,
                 Name AS LABEL, 
                 X3   AS VALUE
          FROM  tbl1
          WHERE  Class = 4;
          
          Series 4
          SELECT NULL AS LINK,
                 Name AS LABEL, 
                 X4   AS VALUE
          FROM  tbl1
          WHERE  Class = 4;
          • 2. Re: Multi-series line chart
            528063
            Thanks Matthew, I'll give it a try and get back to you
            • 3. Re: Multi-series line chart
              528063
              I tried your code, it is different from what i wanted, I was not clear enough, sorry. I made a quick excell chart of what i want and took a snapshot, please take a look at it.

              [http://tinypic.com/view.php?pic=33lyrdh&s=6|http://tinypic.com/view.php?pic=33lyrdh&s=6]


              Thanks!
              • 4. Re: Multi-series line chart
                Matthew Morris
                K -- you not only have to create multiple series, but you also need to make a crosstab view/query of that table. The values of X1, X2, X3, and X4 need to be rows rather than columns when graphing.
                • 5. Re: Multi-series line chart
                  528063
                  I am not really sure if i can do this, any examples would be very helpful.

                  Thanks a lot
                  • 6. Re: Multi-series line chart
                    Matthew Morris
                    The following each have examples on this:

                    http://oracletuts.net/tutorials/three-ways-to-transpose-rows-into-columns-in-oracle-sql/
                    http://www.orafaq.com/node/1871
                    • 7. Re: Multi-series line chart
                      Kenny Hanberg
                      Hi

                      One of my applications has severel charts created using only one Serie, but displaying 1-180 lines dynamicly.

                      In order to archive "Dynamic Multiple Series (1-250)" you need to create the series as a-in-one serie.

                      Create only one serie query.
                      The sql query is created as the return value of a pl/sql function.

                      The returned sql should look somewhat like this
                      select null link,
                             some_category label,
                             somevalue1 "Serie 1",
                             somevalue2 "Serie 2",
                             somevalue3 "Serie 3",
                             somevalue4 "Serie 4",
                             somevalue5 "Serie 5",
                             somevalue6 "Serie 6"
                        from (your query)
                      order by some_category
                      In your case the returned sql query will rely on either sql and pivot syntax or a table function.

                      Drawbacks:
                      You loose control of the line colouring.
                      Serie labels has a max length of 30-32 characters .

                      Hove you get the idea :)