1 2 Previous Next 15 Replies Latest reply: Sep 25, 2009 7:00 AM by MatthiasHoys RSS

    ApEx 3.2.1 and AnyGannt - Building the data

    MartinF
      Hi all,

      I am really excited about AnyGantt being supported in 3.2.1 - all I need to do now is to get it to work!!

      Here is what I have done so far:

      1 - Created a new page and added a '2D Column' chart with a query of 'Select NULL,'A',1 from dual'. This page runs fine.
      2 - Edited the Region Source for the 2D Column chart to replace the 'flashchart\#CHART_TYPE#.swf with 'flashchart\anygantt_4.0.3\swf\AnyGantt.swf' - in two sections of the code
      3 - Went to AnyGantt Demo Library and copied the XML code for the Human Resorces Chart
      4 - Updated the Chart to have Custom XML and pasted the XML Code from the AnyGantt Demo Library.

      I now have a page that will pop up the Gantt chart with the demo data from the AnyGantt Demo Library.

      So, the problem I have is 'How do I make the data shown dynamic (based on a query from my database)?'

      The structure of the XML for the data is:
      <resource_chart>
       <resources>
        <resource id="555" name="Person 1" />
        <resource id="571" name="Perosn 2" />
       </resources>
       <periods>
        <period resource_id="555" start="2009/02/02" end="2009/02/03" style="green" />
        <period resource_id="555" start="2009/02/03" end="2009/02/04" style="green" />
       </periods>
      </resource_chart>
      I can build a query to return this data, but how do I get it into the XML? I think that I can define the Series and that this will return a string to replace #DATA# in the XML, but does this need any particular structure?

      Nothing I try seems to work.

      Is anyone else playing with this yet? Has anyone got any ideas?

      Thanks,
      Martin
        • 1. Re: ApEx 3.2.1 and AnyGannt - Building the data
          MartinF
          Hi all,

          I am making progress. An update for you.....

          Had to get a bit creative but this is what I have done so far:

          1. Create a Text Area Page Item and populate it with the XML string I need using the code:
           
          declare 
          x clob; 
          cursor c 
          is 
          select xml_data_row from ( 
          select 1 block_number, '<resources>' xml_data_row from dual 
          UNION 
          select DISTINCT 2 block_number, '<resource id="'||person_id||'" name="'||requester_name||'" />' xml_data_row 
          from ams_requests_v where to_char(start_date,'MON-YYYY') = :P1_MONTH_NAME 
          UNION 
          select 3 block_number, '</resources>' xml_data_row  from dual 
          UNION 
          select 4 block_number, '<periods>' xml_data_row  from dual 
          UNION 
          select 5 block_number, '<period resource_id="'||person_id||'" start="'||to_Char(start_date,'YYYY/MM/DD')||
          '" end="'||to_Char(end_date + 1,'YYYY/MM/DD')||'" style="'||decode(ams_type_code,'WFH','green','red')||'" />' xml_data_row 
          from ams_requests_v where to_char(start_date,'MON-YYYY') = :P1_MONTH_NAME 
          UNION 
          select 6 block_number, '</periods>' xml_data_row from dual) data_table 
          order by block_number, xml_data_row; 
          begin 
          delete from xxclob; 
              for i in c 
              loop 
                  x:=x||i.xml_data_row; 
              end loop; 
              insert into xxclob values(x); 
          commit; 
          end; 
          select xmldata into :P1_DATA_SET from xxclob; 
          2. Update the Chart XML to replace the Resource and Period data with the field name:
           
            <resource_chart> 
          &P1_DATA_SET. 
            </resource_chart> 
          I now have a working Gantt Chart.

          The problem I am still facing is a String Overflow. You will see from the first set of code that I have had to restrict the Data to show only One Month at a time. This is because VARCHAR2 cannot hold enough data but CLOB does not work.

          If I remove the month restriction the first set of code still works and populates the TextArea with the full XML Data, but the chart reports 'No project data was found' which seems to be a catch-all error when something is not right with the data defined.

          So, I am still looking for other people in the same boat. Is anyone else making any progress? Does anyone have any ideas for large data sets?

          Thanks,
          Martin
          • 2. Re: ApEx 3.2.1 and AnyGannt - Building the data
            Carsten Czarski-Oracle
            Hi

            I was successful using the following approach - I did not use the APEX "charting" infrastructure - since
            AnyGantt Charts require completely different XML compared to the other AnyChart diagram types. For
            instance the root element is "AnyGannt" whereas it is "AnyChart" in the other chart types. So IMHO we
            can't use the XML skeletons of the other AnyChart diagram types.

            So here is my approach - the sample table URLAUB is being created firstly.

            I also took the XML from the AnyChart Demo page. XQuery is a nice helper when it's about to change
            small fragments in a large XML file ... note the "for $i in .." code fragments at the end of the XML document.
            drop table urlaub
            /
            
            create table urlaub(
              id number,
              name varchar2(200),
              datum_von date,
              datum_bis date
            )
            /
            
            create or replace view v_urlaub
            as 
            select 
              id, 
              name, 
              to_char(datum_von, 'YYYY/MM/DD') datum_von,
              to_char(datum_bis, 'YYYY/MM/DD') datum_bis
            from urlaub
            /
            
            insert into urlaub values (1, 'Carsten', to_date('2009-08-01','YYYY-MM-DD'), to_date('2009-08-15','YYYY-MM-DD'));
            insert into urlaub values (2, 'Ulrike', to_date('2009-08-10','YYYY-MM-DD'), to_date('2009-08-25', 'YYYY-MM-DD'));
            
            commit
            /
            
            
            create or replace procedure generate_gantt_xml
            as 
              v_xml xmltype;
              v_blob blob;
            begin
            select xmlquery(
            '<anygantt>
                 <settings>
                      <navigation enabled="True" position="Top" size="30">
                           <buttons collapse_expand_button="false" align="Far"/>
                           <text>Human Resource Chart</text>
                           <font face="Verdana" size="10" bold="true" color="White"/>
                           <background>
                                <fill type="Gradient">
                                     <gradient>
                                          <key color="#B0B0B0" position="0"/>
                                          <key color="#A0A0A0" position="0.3"/>
                                          <key color="#999999" position="0.5"/>
                                          <key color="#A0A0A0" position="0.7"/>
                                          <key color="#B0B0B0" position="1"/>
                                     </gradient>
                                </fill>
                                <border type="Solid" color="#494949"/>
                           </background>
                      </navigation>
                 </settings>
                 <datagrid width="132">
                      <columns>
                           <column attribute_name="RowNum" width="30">
                                <header>
                                     <text>#</text>
                                </header>
                           </column>
                           <column attribute_name="Name" cell_align="LEFTLEVELPADDING" padding="0">
                                <header>
                                     <text>Person</text>
                                </header>
                           </column>
                      </columns>
                 </datagrid>
                 <timeline>
                      <scale show_start="2007/02/10" show_end="2007/04/19"/>
                 </timeline>
             <styles>
                <period_styles>
                  <period_style name="green">
                    <bar_style>
                      <middle shape="Full">
                        <fill enabled="true" type="Gradient">
                                <gradient angle="-90">
                                     <key color="#689663" position="0"/>
                                     <key color="#6B9866" position="0.38"/>
                                     <key color="#B4FFAB" position="1"/>
                                </gradient>
                           </fill>
                      </middle>
                        <states>
                             <hover>
                                  <middle>
                                       <border enabled="true" type="Solid" color="Yellow"/>
                                          <fill enabled="true" type="Gradient">
                                               <gradient angle="-90">
                                                    <key color="#689663" position="0"/>
                                                    <key color="#6B9866" position="0.38"/>
                                                    <key color="#B4FFAB" position="1"/>
                                               </gradient>
                                          </fill>
                                  </middle>
                             </hover>
                        </states>
                    </bar_style>
                  </period_style>
                   <period_style name="yellow">
                    <bar_style>
                      <middle shape="Full">
                        <fill enabled="true" type="Gradient">
                                <gradient angle="-90">
                                     <key color="#959663" position="0"/>
                                     <key color="#989967" position="0.38"/>
                                     <key color="#FEFFAB" position="1"/>
                                </gradient>
                           </fill>
                      </middle>
                        <states>
                             <hover>
                                  <middle>
                                       <border enabled="true" type="Solid" color="Yellow"/>
                                          <fill enabled="true" type="Gradient">
                                               <gradient angle="-90">
                                                    <key color="#959663" position="0"/>
                                                    <key color="#989967" position="0.38"/>
                                                    <key color="#FEFFAB" position="1"/>
                                               </gradient>
                                          </fill>
                                  </middle>
                             </hover>
                        </states>
                    </bar_style>
                  </period_style>
                   <period_style name="red">
                    <bar_style>
                      <middle shape="Full">
                        <fill enabled="true" type="Gradient">
                                <gradient angle="-90">
                                     <key color="#963E3E" position="0"/>
                                     <key color="#994949" position="0.38"/>
                                     <key color="#FF9191" position="1"/>
                                </gradient>
                           </fill>
                      </middle>
                        <states>
                             <hover>
                                  <middle>
                                       <border enabled="true" type="Solid" color="Yellow"/>
                                          <fill enabled="true" type="Gradient">
                                               <gradient angle="-90">
                                                    <key color="#963E3E" position="0"/>
                                                    <key color="#994949" position="0.38"/>
                                                    <key color="#FF9191" position="1"/>
                                               </gradient>
                                          </fill>
                                  </middle>
                             </hover>
                        </states>
                    </bar_style>
                  </period_style>
                </period_styles>
              </styles>
              <resource_chart>
                   <resources>{for $i in ora:view("V_URLAUB")/ROW return <resource id="{$i/ID/text()}" name="{$i/NAME/text()}"/>}
               </resources>
            <periods>{for $i in ora:view("V_URLAUB")/ROW return <period resource_id="{$i/ID/text()}" start="{$i/DATUM_VON/text()}" end="{$i/DATUM_BIS/text()}"/>}
            </periods>
              </resource_chart>
            </anygantt>' returning content) into v_xml from dual;
              v_blob := v_xml.getblobval(nls_charset_id('AL32UTF8'));
              owa_util.mime_header('text/xml');
              wpg_docload.download_file(v_blob);
            end;
            /
            sho err
            After running this script create an "on demand" application process named getUrlaubXML with the
            following PL/SQL code ...
            begin
              generate_gantt_xml;
            end;
            and finally create the chart region with any chart in it - navigate then to the regions' attributes
            and change the region source as follows - the loading process is also a bit different in comparison
            to the other Anychart Diagrams ..
            <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000"
                 codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0"
                 width="#WIDTH#"
                 height="#HEIGHT#"
                 id="#CHART_NAME#"
                 align="">
            <param name="movie" value="#IMAGE_PREFIX#flashchart/anygantt_4.0.3/swf/Preloader.swf">
            <param name="quality" value="high">
            <param name="allowScriptAccess" value="sameDomain">
            <param name="allowNetworking" value="all">
            <param name="scale" value="noscale">
            <param name="wmode" value="transparent">
            <param name="FlashVars" value="waiting=#FLASH_WAITING#&amp;loading=#FLASH_LOADING#&amp;swffile=#IMAGE_PREFIX#flashchart/anygantt_4.0.3/swf/AnyGantt.swf&amp;XMLFile=#HOST#apex_util.flash?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=getUrlaubXml">
            
            <embed src="#IMAGE_PREFIX#flashchart/anygantt_4.0.3/swf/Preloader.swf"
                   quality="high"
                   width="#WIDTH#"
                   height="#HEIGHT#"
                   name="#CHART_NAME#"
                   scale="noscale"
                   align=""
                   allowScriptAccess="sameDomain" 
                   allowNetworking="all"
                   type="application/x-shockwave-flash"
                   pluginspage="http://www.macromedia.com/go/getflashplayer"
                   wmode="transparent"
                   FlashVars="waiting=#FLASH_WAITING#&amp;loading=#FLASH_LOADING#&amp;swffile=#IMAGE_PREFIX#flashchart/anygantt_4.0.3/swf/AnyGantt.swf&amp;XMLFile=#HOST#apex_util.flash?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=getUrlaubXml">
            </embed>
            </object>
            #CHART_REFRESH#
            Let me know whether this is useful for you ...

            Best regards

            -Carsten
            • 3. Re: ApEx 3.2.1 and AnyGannt - Building the data
              MartinF
              Hi Carsten,

              Thanks for this. I will have a play and let you know how I get on.

              Regards,
              Martin
              • 4. Re: ApEx 3.2.1 and AnyGannt - Building the data
                MartinF
                Hi Carsten,

                When I run the on demand process I get:

                PLS-00201: identifier ‘SYS.DBMS_XQUERYINT’ must be declared

                I have tracked this down to an XE limitation so I will have to wait until I upgrade our Enterprise Edition system to 3.2.1.

                Thanks for you help. I will update this thread when I can progress.

                Regards,
                Martin
                • 5. Re: ApEx 3.2.1 and AnyGannt - Building the data
                  Carsten Czarski-Oracle
                  Hi Martin,

                  this is true - XQuery is not available in OracleXE - so it would run on an XE database but the XML generation
                  would be more difficult then. The general approach should work for any XML size - since no APEX items
                  are involved.

                  Best regards

                  -Carsten
                  • 6. Re: ApEx 3.2.1 and AnyGannt - Building the data
                    MatthiasHoys
                    Hello Carsten

                    I tried to reproduce your example on apex.oracle.com but I can't get it to work - the Gantt chart seems to hang when loading the page.
                    I created the table, view and the procedure and granted executed rights on the procedure to apex_public_user.

                    Could you have a look?

                    http://apex.oracle.com/pls/otn/f?p=50310:3
                    username: admin
                    password: apex

                    Thanks,
                    Matthias

                    Edited by: mhoys on Sep 18, 2009 3:32 PM
                    • 7. Re: ApEx 3.2.1 and AnyGannt - Building the data
                      Carsten Czarski-Oracle
                      Hi Matthias,

                      The HTML source of your page is interesting ...
                      <embed src="/i/flashchart/anygantt_4.0.3/swf/Preloader.swf"
                             quality="high"
                             width="#WIDTH#"
                             height="#HEIGHT#"
                             name="#CHART_NAME#"
                             scale="noscale"
                             align=""
                             allowScriptAccess="sameDomain" 
                             allowNetworking="all"
                             type="application/x-shockwave-flash"
                             pluginspage="http://www.macromedia.com/go/getflashplayer"
                             wmode="transparent"
                             FlashVars="waiting=#FLASH_WAITING#&loading=#FLASH_LOADING#&swffile=/i/flashchart/anygantt_4.0.3/swf/AnyGantt.swf&XMLFile=#HOST#apex_util.flash?p=50310:3:4091263672721683:APPLICATION_PROCESS=getUrlaubXml">
                      </embed>
                      So the substitution strings are not being replaced in your case. So is yout region really of
                      the type "Flash Chart". You also might try another template ...

                      Does this help ...

                      -Carsten

                      Oracle 11g Release 2 ist freigegeben!
                      http://www.oracle.com/global/de/community/index.html

                      SQL und PL/SQL: Tipps, Tricks & Best Practice
                      http://sql-plsql-de.blogspot.com
                      • 8. Re: ApEx 3.2.1 and AnyGannt - Building the data
                        MatthiasHoys
                        Yes, I just found this out myself. I now filled in the substitution variables manually and this works fine on my local installation but not on apex.oracle.com.

                        I'm trying to debug this further...

                        Thanks,
                        Matthias
                        • 9. Re: ApEx 3.2.1 and AnyGannt - Building the data
                          MatthiasHoys
                          I created a new page on apex.oracle.com using the chart region and I can see from the html source that the substitution variables are filled in but still the Gantt chart is not loading:

                          http://apex.oracle.com/pls/otn/f?p=53750:4

                          username: admin
                          password: apex

                          Any idea?
                          Matthias
                          • 10. Re: ApEx 3.2.1 and AnyGannt - Building the data
                            Carsten Czarski-Oracle
                            Hi Matthias,

                            The XML is being loaded from this URL ...
                            http://apex.oracle.com/pls/otn/apex_util.flash?p=53750:4:{SESSIONID}:APPLICATION_PROCESS=getUrlaubXML

                            So there must be an Application Process "getUrlaubXML" in your APEX APP calling the PL/SQL procedure
                            which then generates the XML. You can check by testing the URL in a browser. When you see the XML
                            in the brower window your application process works correctly ...

                            Best regards

                            -Carsten

                            Oracle 11g Release 2 ist freigegeben!
                            http://www.oracle.com/global/de/community/index.html

                            SQL und PL/SQL: Tipps, Tricks & Best Practice
                            http://sql-plsql-de.blogspot.com
                            • 11. Re: ApEx 3.2.1 and AnyGannt - Building the data
                              MatthiasHoys
                              Yes, the application process is there, but when I try to call it using the full URL nothing is found...

                              Do you have a working example using AnyGantt on apex.oracle.com?


                              Matthias
                              • 12. Re: ApEx 3.2.1 and AnyGannt - Building the data
                                Carsten Czarski-Oracle
                                Hi Matthias,

                                unfortunately not ... but if you either post me an export of your application or post me
                                access credentials to your workspace on apex.oracle.com I could have a look into your
                                application ...

                                carsten[dot]czarski{at}oracle(dot)com

                                Regards

                                -Carsten

                                Oracle 11g Release 2 ist freigegeben!
                                http://www.oracle.com/global/de/community/index.html

                                SQL und PL/SQL: Tipps, Tricks & Best Practice
                                http://sql-plsql-de.blogspot.com
                                • 13. Re: ApEx 3.2.1 and AnyGannt - Building the data
                                  MatthiasHoys
                                  Hello Carsten,

                                  These are the credentials:

                                  workspace: MATTHIAS
                                  username: ADMIN
                                  password: apex

                                  Then it's application 9703 - Test AnyGantt

                                  Thanks,
                                  Matthias
                                  • 14. Re: ApEx 3.2.1 and AnyGannt - Building the data
                                    Carsten Czarski-Oracle
                                    Hi Matthias,

                                    when calling your application process directly via ...
                                    http://apex.oracle.com/pls/otn/f?p=9703:4:1980857848297773:APPLICATION_PROCESS=getUrlaubXML::::

                                    ... I got the following error message
                                    ORA-19114: XPST0003 - Fehler beim Parsen des XQuery-Ausdrucks:
                                    LPX-00801: XQuery syntax error at 'EOF'
                                    213   <m
                                    -      ^
                                    ORA-06512: in "PARTNER.GENERATE_GANTT_XML11", Zeile 6
                                    ORA-06512: in Zeile 1
                                    I investigated your PL/SQL code but could not find any error within the XML - So I created the procedure generate_gantt_xml again
                                    from my own script. I assume that some illegal characters (0x09, 0x00) got into the XML while copying and pasting them from
                                    the forum to your application ... This then lead to corrupt XML and the seen problems ...

                                    Try it out ...

                                    Regards

                                    -Carsten


                                    Oracle 11g Release 2 ist freigegeben!
                                    http://www.oracle.com/global/de/community/index.html

                                    SQL und PL/SQL: Tipps, Tricks & Best Practice
                                    http://sql-plsql-de.blogspot.com
                                    1 2 Previous Next