8 Replies Latest reply: Apr 9, 2013 7:16 AM by Vaishalini RSS

    How to create a Cross tab report dynamically in APEX

    Vaishalini
      Hi All,

      I need help regarding matrix reports to create dynamically in APEX .
      I have created a dynamic matrix report using pl/sql procedure.

      Example :-
      declare
      l_sql varchar2(1000) ;
        l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
      BEGIN
      l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(replace(''||upper(:P2_X)||'',':',''','''),':');
             FOR z IN 1..l_vc_arr2.count LOOP
      l_sql := 'SELECT * FROM (SELECT Ename, Deptno,job,sal FROM emp) 
      PIVOT
      (
      avg(sal) as sal,count(*) as count
      FOR job IN
      ('''||l_vc_arr2(z)||''') )';
       END LOOP;
      dbms_output.put_line(l_sql);
      END;
      Where :P2_X bind variable for multiselect item.

      Except performance issue, it is working fine with this method. But I need a better method to overcome performance issue and to have ease method to use.
      Is there any other method to create matrix reports dynamically in APEX like plugins or any method rather than using PL/SQL code?
      Any Suggestions..


      Thanks in Advance,
      Shalini

      Edited by: 967916 on Apr 5, 2013 1:16 PM
        • 1. Re: How to create a Cross tab report dynamically in APEX
          fac586
          >

          Please update your profile with a real handle instead of "967916".

          You'll get a faster, more effective response to your questions by including as much relevant information as possible upfront. This should include:

          <li>Full APEX version
          <li>Full DB/version/edition/host OS
          <li>Web server architecture (EPG, OHS or APEX listener/host OS)
          <li>Browser(s) and version(s) used
          <li>Theme
          <li>Template(s)
          <li>Region/item type(s)

          With APEX we're fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
          I need help regarding matrix reports to create dynamically in APEX .
          I have created a dynamic matrix report using pl/sql procedure.

          Example :-
          declare
          l_sql varchar2(1000) ;
          l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
          BEGIN
          l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(replace(''||upper(:P2_X)||'',':',''','''),':');
          FOR z IN 1..l_vc_arr2.count LOOP
          l_sql := 'SELECT * FROM (SELECT Ename, Deptno,job,sal FROM emp) 
          PIVOT
          (
          avg(sal) as sal,count(*) as count
          FOR job IN
          ('''||l_vc_arr2(z)||''') )';
          END LOOP;
          dbms_output.put_line(l_sql);
          END;
          Where :P2_X bind variable for multiselect item.

          Except performance issue, it is working fine with this method.
          I don't see how it can be. That is neither a valid report region source nor suitable PL/SQL to generate a crosstab report in a dynamic PL/SQL region. You meed to explain exactly what you are actually doing, or better still create an example on apex.oracle.com.

          What "performance issue"? You need to explain and quantify this. Use Debug to determine what is impacting performance.
          • 2. Re: How to create a Cross tab report dynamically in APEX
            fac586
            Moreover, I suspect that this code is not working as you expect/intend:
            declare
            l_sql varchar2(1000) ;
            l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
            BEGIN
            l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(replace(''||upper(:P2_X)||'',':',''','''),':');
            FOR z IN 1..l_vc_arr2.count LOOP
            l_sql := 'SELECT * FROM (SELECT Ename, Deptno,job,sal FROM emp) 
            PIVOT
            (
            avg(sal) as sal,count(*) as count
            FOR job IN
            ('''||l_vc_arr2(z)||''') )';
            END LOOP;
            dbms_output.put_line(l_sql);
            END;
            As the ':' delimiters are replaced before invoking <tt>apex_util.string_to_table</tt>, the resulting array contains only a single element, and the loop is only executed once. The pivot list is actually generated entirely by string manipulation. The above code can be simplified to:
            begin
            
              return
            'select
                *
            from
                (select ename, deptno, job, sal from emp) 
            pivot
                (avg(sal) as sal, count(*) as count
                  for job in (' || replace('''' || upper(:p2_x) || '''', ':', ''',''') || '))';
            
            end;
            • 3. Re: How to create a Cross tab report dynamically in APEX
              Vaishalini
              Thank You for your reply.

              I have updated my profile.

              Details :-

              Full APEX version - *4.1.1.00.23*
              Full DB/version/edition/host OS - *11g Release 11.2.0.1.0 - 64bit Production*
              Web server architecture (EPG, OHS or APEX listener/host OS) - APEX_LISTENER_VERSION     1.1.3.243.11.40
              Browser(s) and version(s) used - Mozilla Firefox, Chrome and internet Explorer
              Theme - *24.Cloudy*
              Template(s) - Two Level Tabs
              Region/item type(s) Report region

              Please let me know if you need more details.

              The apex link for above example as follows.
              http://apex.oracle.com/pls/apex/f?p=12269:1

              Username : shalini
              Password : password64

              Here when we select multiple job names and submit, the report will generate dynamically with selected job names as columns.

              I want to get the same report without using pl/sql procedure.

              Thanks

              Edited by: Vaishalini on Apr 5, 2013 4:17 PM
              • 4. Re: How to create a Cross tab report dynamically in APEX
                fac586
                Vaishalini wrote:
                Thank You for your reply.

                I have updated my profile.

                Details :-

                Full APEX version - *4.1.1.00.23*
                Full DB/version/edition/host OS - *11g Release 11.2.0.1.0 - 64bit Production*
                Web server architecture (EPG, OHS or APEX listener/host OS) - APEX_LISTENER_VERSION     1.1.3.243.11.40
                Browser(s) and version(s) used - Mozilla Firefox, Chrome and internet Explorer
                Theme - *24.Cloudy*
                Template(s) - Two Level Tabs
                Region/item type(s) Report region

                Please let me know if you need more details.

                The apex link for above example as follows.
                http://apex.oracle.com/pls/apex/f?p=12269:1
                Please provide workspace and login information.
                Here when we select multiple job names and submit, the report will generate dynamically with selected job names as columns.

                I want to get the same report without using pl/sql procedure.
                To get a dynamic pivot you have to generate a dynamic query string, and for a standard APEX report the mechanism to do this the PL/SQL function body returning SQL query report source type. As shown above, the PL/SQL required to do this is trivial, and can be reduced to a single return statement. What is the problem with this?
                • 5. Re: How to create a Cross tab report dynamically in APEX
                  Vaishalini
                  Login Information :

                  Workspace : SWS1
                  Username : shalini
                  Password : password64

                  Single return statement is not returning report and it is displaying no data found error message.


                  My requirements will be like
                  1) Use of multiple aggregate columns dynamically (which will be selected by user) in pivot function
                  2) Use of column that contains the muliple values (which are dynamically selected ) that will become column headers

                  For this type of requirements, it requires complex pl/sql code. And not only that, if the data is more in tables/views then it is taking more time to get and return data.

                  So, I want to know if there is any other better method or plugin to reduce complexity and to create these type of reports.


                  Thanks.
                  • 6. Re: How to create a Cross tab report dynamically in APEX
                    fac586
                    Vaishalini wrote:
                    Login Information :

                    Workspace : SWS1
                    Username : shalini
                    Password : password64

                    Single return statement is not returning report and it is displaying no data found error message.
                    You had the source property for the region set to Use Query-Specific Column Names and Validate Query instead of Use Generic Column Names (parse query at runtime only), which is required in this case as the number of columns is unknown until runtime.

                    A few other points:

                    <li>We can get a slight performance boost and better user experience by refreshing the report using a Dynamic Action rather than submitting and re-showing.
                    <li>Using regular expression replacement when generating the report query allows for dynamic column aliases to be included, which removes the quotes from the basic column headings:
                    begin
                    
                      return
                    'select
                        *
                    from
                        (select ename, deptno, job, sal from emp) 
                    pivot
                        (avg(sal) as sal, count(*) as count
                          for job in (' || rtrim(regexp_replace(upper(:p2_job) || ':', '([^:]+):', '''\1'' as \1,'), ':,') || '))';
                    
                    end;
                    <li>Similar regexp replacement can also be used to easily generate complex column headings very simply:
                    return 'Name:Dept:' || rtrim(regexp_replace(initcap(:p2_job) || ':', '([^:]+):', '\1 Salary:\1 Count:'), ':')
                    My requirements will be like
                    1) Use of multiple aggregate columns dynamically (which will be selected by user) in pivot function
                    2) Use of column that contains the muliple values (which are dynamically selected ) that will become column headers

                    For this type of requirements, it requires complex pl/sql code.
                    "Complex" may be subjective. What you regard as complex may not be to someone else. We've already significantly reduced the complexity (in terms of lines of code at any rate) of both the query and header generation. What kind of PL/SQL experience do you have?
                    And not only that, if the data is more in tables/views then it is taking more time to get and return data.
                    That's completely unavoidable. The more data we have to access the longer it will take to process. The goal is to design the database and application so that the minimum quantity of data is accessed so the time it takes to process remains acceptable as the data volume increases.
                    So, I want to know if there is any other better method or plugin to reduce complexity and to create these type of reports.
                    From what you've presented so far it's difficult to see how another method could be made less complex. Have you searched for such a plug-in? I've no idea if such a thing exists (I'd far rather have some fun building it myself...)
                    • 7. Re: How to create a Cross tab report dynamically in APEX
                      Vaishalini
                      I have searched for plugins but i dint find any plugin till now.

                      Edited by: Vaishalini on Apr 8, 2013 4:34 PM
                      • 8. Re: How to create a Cross tab report dynamically in APEX
                        Vaishalini
                        Hi fac586,

                        Thank you very much for your help.

                        My PL/SQL experience is just a 1yr.

                        Till now I have done crosstab reports using dynamical pl/sql.
                        With your help the lines of code has been reduced .


                        Still I am looking for a plugin. If you find any, please suggest me.