This discussion is archived
8 Replies Latest reply: Apr 9, 2013 5:16 AM by Vaishalini RSS

How to create a Cross tab report dynamically in APEX

Vaishalini Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >

    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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