12 Replies Latest reply: Jun 6, 2014 1:06 AM by Sunil Bhatia RSS

    How to create a Trend Chart

    MaryM

      Good Morning,

       

      I have already created numerous Trend reports, with headings that are dynamically returning the column heading.  I am trying to create Trend charts the same way, but don't understand how to do them.  I use views for the reports and a temp table for the headings.

       

      Report Ex:

                   JAN-13      FEB-13   MAR-13

      AC           10%          50%        50%

      LC           100%          -              -

      ALL           90%         50%         50%

       

      I thought I could use the same query from the report, but I was wrong, the dates don't show at the bottom of the chart, I am not sure what to do.

       

      Thanks,

      Mary

        • 1. Re: How to create a Trend Chart
          jwellsnh

          Hi Mary,

           

          From what I have read in the AnyChart documentation, you will need to build your chart and then override the Chart XML.  This will require that you set the Use Custom XML to Yes.

           

          My demo's XML was altered to show Max, Average, Median and Min by adding the following code to the Chart XML.  The code needs to be added to the y_axis section, I placed mine just prior to the </y_axis> tag.

           

          Demo: https://apex.oracle.com/pls/apex/f?p=82809:1

           

          <axis_markers>
           <lines>
           <line value="{%AxisMax}" color="Red">
           <label enabled="true">
           <font color="red" />
           <format><![CDATA[AxisMax {%Value} ]]></format>
           </label>
           </line>
           <line value="{%AxisMin}" color="Green">
           <label enabled="true">
           <font color="green" bold="True" />
           <format><![CDATA[AxisMin {%Value} ]]></format>
           </label>
           </line>
           <line value="{%AxisAverage}" color="RoyalBlue">
           <label enabled="true">
           <font color="RoyalBlue" bold="True" />
           <format><![CDATA[AxisAverage {%Value} ]]></format>
           </label>
           </line>
           <line value="{%AxisMedian}" color="Gold">
           <label enabled="true">
           <font color="Gold" bold="True" />
           <format><![CDATA[AxisMedian {%Value} ]]></format>
           </label>
           </line>
           </lines>
           </axis_markers> 
          
          
          

           

          Jeff

          • 2. Re: How to create a Trend Chart
            MaryM

            How do I change the query?

             

            current quey

            SELECT NULL LINK, UNIT_TYPE LABEL,

                    ROUND(OUTPUT1,2)*100 VALUE1,

                    ROUND(OUTPUT2,2)*100 VALUE2,

                    ROUND(OUTPUT3,2)*100 VALUE3,

                    ROUND(OUTPUT4,2)*100 VALUE4

                   FROM V_ACHIEVED_READNIESS_TREND_RPT

                  ORDER BY CASE UNIT_TYPE         

                                      WHEN 'AC' THEN 1         

                                      WHEN 'RC' THEN 2          

                                      ELSE 3 END;

            RC, AC, ALL I want as my Y axis, but their values always change.  I have two parameters of start and end dates that I want displayed on my x axis.  I have 24 outputs like this.

             

            Thanks,

            Mary

            • 3. Re: How to create a Trend Chart
              jwellsnh

              MaryM wrote:

               

              How do I change the query?

               

              current quey

              SELECT NULL LINK, UNIT_TYPE LABEL,

                      ROUND(OUTPUT1,2)*100 VALUE1,

                      ROUND(OUTPUT2,2)*100 VALUE2,

                      ROUND(OUTPUT3,2)*100 VALUE3,

                      ROUND(OUTPUT4,2)*100 VALUE4

                     FROM V_ACHIEVED_READNIESS_TREND_RPT

                    ORDER BY CASE UNIT_TYPE        

                                        WHEN 'AC' THEN 1        

                                        WHEN 'RC' THEN 2         

                                        ELSE 3 END;

              RC, AC, ALL I want as my Y axis, but their values always change.  I have two parameters of start and end dates that I want displayed on my x axis.  I have 24 outputs like this.

               

              Thanks,

              Mary

              So If I understand you correctly you still need to establish your chart.

               

              Would be possible to stage some sample data up on a workspace of apex.oracle.com?

               

              What is meant by "I have 24 outputs like this."?  Does that mean your chart will have 24 series?  This requirement is not clear.

               

              Jeff

              • 4. Re: How to create a Trend Chart
                MaryM

                It would be the dates on the bottom and I think I should use 3 series. There are 24 possible dates, due to start and end dates. I have never programmed charts before. The problem comes from the column headings from the reports, were from the pl/sql that I used for the report header..

                 

                Example of the report:

                 

                           Mar-2013    Apr-2013    May-2013   Jun-2013    July-2013

                 

                AC         10                12              10                 20              10

                RC          50                50               -                    -                 -

                ALL        30                35              10                 10              10

                 

                Sample report query

                 

                SELECT
                   UNIT_TYPE,
                       ROUND(MAX(OUTPUT1),2)*100 AS OUTPUT1,
                       ROUND(MAX(OUTPUT2,2)*100 AS OUTPUT2
                FROM V_TREND_RPT
                GROUP BY UNIT_TYPE
                ORDER BY CASE UNIT_TYPE
                                        WHEN 'AC' THEN 1
                                        WHEN 'RC' THEN 2
                                        ELSE 3 END;
                

                 

                The current report uses a sql query from a view and the headers I use the PL/SQL (function returning colon delimited list).

                 

                Thanks,

                Mary

                • 5. Re: How to create a Trend Chart
                  jwellsnh

                  Hi Mary,

                   

                  I had been wondering where this thread was headed or if it had ended.

                   

                  It would aid the situation if you could create a sample application up on apex.oracle.com, so please do so if possible.  That will enable me to view your table structure and work through some examples.

                   

                  You said:

                  I think I should use 3 series

                   

                  So are we talking about a series for AC, RC and ALL?

                   

                  Jeff

                  • 6. Re: How to create a Trend Chart
                    MaryM

                    Yes, I think that would be best, but I don't know, because I never programmed a chart before, not even in Access.  I have only made charts in excel and those charts are very easy to make.  All of the examples I have seen for charts on the Oracle site are for simple charts and I thought I could just take my report query and use it to make a chart and I am wrong.

                     

                    Please send email address so I can send you the info..

                     

                    thanks

                    • 7. Re: How to create a Trend Chart
                      jwellsnh

                      Hi Mary,

                       

                      Please send email address so I can send you the info..

                       

                      We need to do this in reverse since I have no idea what email address is, mine is on my profile.

                       

                      I thought I could just take my report query and use it to make a chart and I am wrong.

                       

                      The query syntax depends on the type of chart your are crafting since the rendering of the chart is then based on XML that is created along with the chart type.  See this link for documentation.

                      Creating Charts

                       

                      We will get a chart done first and then hopefully get under the covers of that chart to set a trend line.

                       

                      Jeff

                      • 8. Re: How to create a Trend Chart
                        MaryM

                        I changed the query to point to a different view, so all of the calculations are being done there.  Where I do a simple select statement to the view all I get are dots, no line. I'm not sure what I am missing.

                         

                        chart query
                          SELECT NULL LINK,
                               MONYEAR LABEL,
                                OUTPUT1,OUTPUT2,OUTPUT3
                          FROM V_ACHIEVD_CHRT
                           ORDER BY HEADING_NUMBER
                        

                         

                        The view does the calculations

                         

                         

                        select unit_type, mony_year
                         heading_number,
                         round(max(output1,2_*100) sa output1,
                         round(max(output2,2)*100) output2
                         from v_achieved_data
                         group by unit_type, monyear, heading_number
                         
                        

                         

                         

                        Your email address is set to private

                         

                        my workspace:

                         

                        frs_demo

                         

                        application# 51892

                        • 9. Re: How to create a Trend Chart
                          jwellsnh

                          Mary,

                           

                          You said:

                          Where I do a simple select statement to the view all I get are dots, no line.

                            SELECT NULL LINK,  
                                 MONYEAR LABEL,  
                                  OUTPUT1,OUTPUT2,OUTPUT3  
                            FROM V_ACHIEVD_CHRT  
                             ORDER BY HEADING_NUMBER  
                          
                          

                           

                          Possibly there are NULL values in some of your data?

                           

                          Try this version of the query and see if it doesn't connect the dots.

                            SELECT NULL link,
                                   monyear label,
                                   NVL (output1, 0),
                                   NVL (output2, 0),
                                   NVL (output3, 0)
                              FROM v_achievd_chrt
                          ORDER BY heading_number
                          

                           

                          Jeff

                          • 10. Re: How to create a Trend Chart
                            MaryM

                            I got the chart to work the way I want. I ended up with 3 series, and using custom colors and a little custom xml.

                             

                            I have one question.  Is there a way to export the chart to pdf or print in landscape instead of portrait?  I have been looking at the forum and haven't seen anything.

                             

                            Thanks,

                             

                            Mary

                            • 11. Re: How to create a Trend Chart
                              jwellsnh

                              Hi Mary,

                               

                              Glad this worked out well for you.

                               

                              You said:

                              I have one question.  Is there a way to export the chart to pdf or print in landscape instead of portrait?

                               

                              I don't have an answer to this question at the moment but please start this question as a separate thread.

                               

                              Jeff

                              • 12. Re: How to create a Trend Chart
                                Sunil Bhatia

                                Hi Mary,

                                 

                                If there are No fancy outputs in PDF and just a chart PDF, you can simply right click on the chart and select "Save to PDF" option

                                 

                                -- Sunil Bhatia