14 Replies Latest reply: Aug 20, 2009 12:22 PM by 581175 RSS

    XML Charts - Unwrapped

    Denes Kubicek
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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