This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 25, 2009 5:00 AM by MatthiasHoys RSS

ApEx 3.2.1 and AnyGannt - Building the data

MartinF Explorer
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

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