This discussion is archived
14 Replies Latest reply: Aug 20, 2009 10:22 AM by 581175 RSS

XML Charts - Unwrapped

Denes Kubicek Oracle ACE Director
Currently Being Moderated
I made the package available for download here:

http://htmldb.oracle.com/pls/otn/f?p=31517:58

There are three additional changes:

1. x- and y-axis titles may be specified,

2. no UTL_FILE package access necessary by default,

3. functions, for converting axis values may be specified.

A demo is available here:

http://htmldb.oracle.com/pls/otn/f?p=31517:74

Denes Kubicek
  • 1. Re: XML Charts - Unwrapped
    135285 Oracle ACE
    Currently Being Moderated
    Hi Denes,

    great to see that your package is now available with full source code. I think that will convince a lot of people to use it if the code isn't wrapped.

    Keep up the good work!
    Patrick
    PS: Did you already have a chance to try my APEX Builder Plugin?
    ------------------------------------------------------------------------------------
    Check out my APEX Blog http://inside-apex.blogspot.com
  • 2. Re: XML Charts - Unwrapped
    440236 Newbie
    Currently Being Moderated
    Hi Denes Kubicek,

    I am trying to install the charts and I can;t wait to be able to use them.

    So far, I managed to install it without errors, I've created the hidden item, added one more PLSQL item on the page which includes the source you provided in the readme file.
    I replaced :P1_ITEM with P200_c1 (my item name in the page. I make the tests in page 200), MY_TEMPLATE with my_template1, P200_ITEM with P200_C1.

    I have to say that I didn;t include this sql and don;t know where I am supposed to include it.:
    " SELECT xml_chart_pkg.get_chart_plsql
         FROM DUAL;
    "

    All I see in the page is a light brownish square with the flash circle animation and after some time this becomes orange square with an error message in the middle: Loading<xml_chart...

    Can you please advise?

    Thank you
    Anca
  • 3. Re: XML Charts - Unwrapped
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Anca,

    Did you read the provided README.pdf document? There, you will find all the
    required information on how to set it up - including the place for the query.

    Denes Kubicek
  • 4. Re: XML Charts - Unwrapped
    440236 Newbie
    Currently Being Moderated
    Hi Denes,

    Yes I did read the Redme file, but at that point is not very clear for me..

    So.. I've run this: " SELECT xml_chart_pkg.get_chart_plsql
         FROM DUAL;" and the result is the long plsql code which I've modified as explained earlier and placed it in a new region - PL/SQL Dynamic Content - PL/SQL block, Conditional Display Always.

    What Computation Point should I choose for the computation? Could this cause the issue?

    I really hope to get this work soon,

    Any idea is more than welcome

    Have a nice day!
    Anca

    Message was edited by:
    ancag
  • 5. Re: XML Charts - Unwrapped
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Ancag,

    Your questions are qualified. I see, that I didn't mention two things explicitly in my
    documentation:

    1. you need to grant execute on the package to public - I mentioned a public synonym

    2. the computation point is standard - on load and before header

    As far as I can see, getting the orange square is pointing to missing execute permission
    for public user - XML Charts are run by the web server.

    Denes Kubicek
  • 6. Re: XML Charts - Unwrapped
    440236 Newbie
    Currently Being Moderated
    Hi Denes Kubicek,

    I've granted access to public as you recommended : grant EXECUTE ANY PROCEDURE to public;
    And set the computation point to Before header (can;t find any onload there).

    Still doesn;t work.. :(

    help ?

    Have a nice day!
    Anca
  • 7. Re: XML Charts - Unwrapped
    545708 Newbie
    Currently Being Moderated
    Great Denes!

    Now I can change the default XML_CHART_SETTINGS table and use your code in more applications...

    You'll see your package in use in DG Tournament in a few days :-)
  • 8. Re: XML Charts - Unwrapped
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Well, that is the same package, which is also installed or apex.oracle.com. And there it
    works.

    1. did you create a public synonym for the procedure?

    2. give me the PL/SQL region code you are using.

    Denes Kubicek
  • 9. Re: XML Charts - Unwrapped
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Patrick,

    I have seen how your packages work on the slides you provided. I think you are touching
    the right topics. Both, the tabular form enhancement as well as the Builder Plugin are two
    of couple of areas where there is a huge potential for enhancement. I think APEX has a
    great potential - far beyond of the original scope. For sure more than only a second choice
    replacement for Access and Excel.

    I may say the same to you - Keep up the good work!

    Eventually, you want to send me an email. You can find my email address in my demo
    application:

    http://htmldb.oracle.com/pls/otn/f?p=31517:1

    Denes Kubicek
  • 10. Re: XML Charts - Unwrapped
    440236 Newbie
    Currently Being Moderated
    Hi Denes Kubicek,

    1. I don;t know how you mean by creating a public synonim for the procedure..

    2. MyPL/SQL is (in page 201 now )

    xml_chart_pkg.xml_chart_pr
    (item_for_query => :P201_A,
    --item storing your chart query
    chart_type_in => 'stacked column',
    --your chart type
    sort_series_in => 'ASC',
    --how to sort series
    width_in => 600,
    --width of your region
    height_in => 450,
    --height of your region
    debug_xml_in => 'N',
    --using debug option
    xml_output_dir_name_in => 'd:/chart_debug/',
    --debug output directory
    chart_template_in => 'MY_TEMPLATE,my_template1',
    --templates to be used with your chart
    chart_standard_ignore_in => 'N',
    --ignore all standard settings
    link_type_in => 'P',
    --link type P for popup and R for redirect
    page_to_pass_values_to => '200',
    --page to pass values in the link
    request_in => 'SORT',
    --request to pass within link
    items_to_pass_values_to => 'P201_A',
    --items to pass values to, comma delimited
    values_to_pass_to_items => '_category_',
    --values to pass to items, see XML reference
    link_pop_up_w_in => '1000',
    --link popup window width
    link_pop_up_h_in => '800',
    --link popup window height
    chart_background_color_in => '#ededd6',
    --region color
    unique_id_in => NULL,
    --unique string to identify your chart if the same chart
    --type is used on the same page
    turn_caching_on => 'N',
    --if your want to keep the session result per chart,
    --use collection to store the result set
    default_directory_xml => '/i/',
    --if your want to specifiy another directory,
    -- you need to determine this parameter
                   axis_val_conv_function => NULL,
    --if your want to convert axis values, you may include
    --any function here and it will convert your values to
    --whatever (standard functions or your own custom)
    x_axis_title => NULL,
    --title of the x axis
    y_axis_title => NULL
    --title of the y axis
    );


    Something else I've noticed is that if I run the sql which outputs this, I get :

    chart_template_in => 'MY_TEMPLATE:SWITCH_COLORS',

    but in the example it is:

    'MY_TEMPLATE,SWITCH_COLORS'

    Thank you
    Anca
  • 11. Re: XML Charts - Unwrapped
    440236 Newbie
    Currently Being Moderated
    Hi Denes Kubicek,

    Any other idea why this is not working? Any other suggestion on what else should I try?

    Thank you
    Anca
  • 12. Re: XML Charts - Unwrapped
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Anca,

    I opened the package to public. I created a demo site and I provide access to that
    demo site to see the coding behind. What else can I do for you? Once again, run the
    installation script again, make sure there are no errors. Then it should work. If there are
    errors than post those.

    Are you using it on XE?

    Denes Kubicek
  • 13. Re: XML Charts - Unwrapped
    440236 Newbie
    Currently Being Moderated
    Hi Denes,

    Sorry if my questions annoyed you.
    I am sure your code is fine, even if I didn;t manage to make it work.. :(
    No worries, will read the other open discussions on this topic and hopefully I can also use the beautiful charts ;)

    No, I am not using XE.

    Have a nice day!
    Anca
  • 14. Re: XML Charts - Unwrapped
    581175 Newbie
    Currently Being Moderated
    Hello

    I´m playing around with the xml-charts for a few days, but cant get them to work.
    I think i make some very simple mistake.

    Installation according to the readme-file was ok.

    Granted all things required.
    Made a debug-dir.
    No errors or invalid objects there.

    created a new page in my demo application.
    created a hidden item
    created a computation with static assignment for the item.

    created a report with the same query and it works.
    created a pl-sql-content with the provided block. checked it against that, what the "SELECT xml_chart_pkg.get_chart_plsql FROM DUAL;" returns. Looked equal.

    running the page, show me (after a while) a orange background with the following message:
    Loading<xml_chart_pkg.chart_region_pr?app_user_in=APEX_PUBLIC_USER...
    (i´m not able to read more, because the message is cut on the edge)

    debug = 'Y' shows no error (any more) but nothing is in the created oracle directory

    The session tells me that the sql is in the hidden item.
    And it tells me, that i have collection some values in the collection similar to my test-report.
    -----
    Collection Name | Sequence | C001 | C002
    CSTACKEDCOLUMN230582657841003510010 | 1 | OHare | 500
    CSTACKEDCOLUMN230582657841003510010 | 2 | LaGuardia | 4300
    CSTACKEDCOLUMN230582657841003510010 | 3 | Bradley | 500
    CSTACKEDCOLUMN230582657841003510010 | 4 | Logan | 790
    CSTACKEDCOLUMN230582657841003510010 | 5 | Hartsfield | 47798
    CSTACKEDCOLUMN230582657841003510010 | 6 | Dulles | 1240
    CSTACKEDCOLUMN230582657841003510010 | 7 | Lambert | 790
    -----

    h3. My environment is:
    oracle db 11g
    apex 3.2
    installed with mod_plsql not epg
    all running on windows 2008 server x64

    h3. my sql in the hidden item is:
    select cu.CUST_LAST_NAME as CUSTOMER, sum(o.ORDER_TOTAL) as ORDER_TOTAL
    from DEMO_ORDERS o, DEMO_CUSTOMERS cu
    where o.CUSTOMER_ID = cu.CUSTOMER_ID group by cu.CUST_LAST_NAME

    h3. my pl-sql for the chart is:
    BEGIN
    xml_chart_pkg.xml_chart_pr(
    item_for_query => :P10_SQL_HIDDEN, --item storing your chart query
    chart_type_in => 'stacked column', --your chart type
    sort_series_in => 'ASC', --how to sort series
    width_in => 600, --width of your region
    height_in => 450, --height of your region
    debug_xml_in => 'N', --using debug option
    xml_output_dir_name_in => 'chart_debug', --debug output directory
    chart_template_in => 'MY_TEMPLATE,SWITCH_COLORS', --templates to be used with your chart
    chart_standard_ignore_in => 'N', --ignore all standard settings
    link_type_in => 'P', --link type P for popup and R for redirect
    page_to_pass_values_to => '10', --page to pass values in the link
    request_in => 'SORT', --request to pass within link
    items_to_pass_values_to => 'P200_ITEM', --items to pass values to, comma delimited
    values_to_pass_to_items => '_category_', --values to pass to items, see XML reference
    link_pop_up_w_in => '1000', --link popup window width
    link_pop_up_h_in => '800', --link popup window height
    chart_background_color_in => '#ededd6', --region color
    unique_id_in => NULL, --unique string to identify your chart if the same chart
    --type is used on the same page
    turn_caching_on => 'N', --if your want to keep the session result per chart,
    --use collection to store the result set
    default_directory_xml => '/i/charts/', --if your want to specifiy another directory,
    -- you need to determine this parameter
    axis_val_conv_function => NULL, --if your want to convert axis values, you may include
    --any function here and it will convert your values to
    --whatever (standard functions or your own custom)
    x_axis_title => NULL, --title of the x axis
    y_axis_title => NULL --title of the y axis
    );
    END;


    I hope i have not over-read the solution in another thread.

    Thanks for any help.

    Edited by: Scubus on Aug 20, 2009 7:16 PM

    Edited by: Scubus on Aug 20, 2009 7:17 PM