This discussion is archived
7 Replies Latest reply: Dec 3, 2012 1:21 PM by Kenny Hanberg RSS

Multi-series line chart

528063 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Matthew, I'll give it a try and get back to you
  • 3. Re: Multi-series line chart
    528063 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points