Forum Stats

  • 3,851,702 Users
  • 2,264,013 Discussions
  • 7,904,820 Comments

Discussions

Generic columns classic report - refresh column list

Scott Wesley
Scott Wesley Member Posts: 6,259 Gold Crown
edited Jul 20, 2015 4:10AM in APEX Discussions

Consider a dynamic pivot statement like the following (as described by AMIS) which

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno,job' ) )



I've used this as source SQL for classic report, selecting "Use generic column names"

The report looks like

dp_full.png

I can parameterise the data shown with a select list, and including a where clause

where job =v(''P39_JOB'') or v(''P39_JOB'') is null



The select list does PPR on the region, demonstrated here:

https://apex.oracle.com/pls/apex/f?p=73000:39::::::

Note: , and all pivoted jobs are still visible as columns, only the data is restricted

- non-related jobs are blank

- any depts with no clerks aren't shown

dp_clerk.png

Let's say you have another select list that groups jobs, say the "Pres" and "Presidents".

How could you refresh the region to exclude certain jobs?

Reloading the page with the P39_JOB value already set will not affect the outcome, while including a literal string does.

It seems perhaps ODCI function parses the query on null binds, then populates with actual results.

Does anyone have any suggestions?

update: it seems one workaround is to inject the value as a substitution string, eg:

and job like '&P39_CEO.'

where P39_CEO defaulted in session state to %

I only got this concept working in my client app (4.2, 11gr2), not my demo (5.0, 12c), even attempting to cater for relevant quotes and when null. I kept getting

report error: ORA-1403: no data found

I also couldn't work out how to properly apply sys.dbms_assert.enquote_literal()

Scott

Tagged:
Kiran Pawar

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    edited Jul 15, 2015 5:16AM Answer ✓

    Hi Scott

    What about the approach I described in this post: ?

    It eliminates the need for any dynamic SQL and I think the filtering options could be implemented using SQL/XML features.

    Kiran PawarScott Wesley

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    edited Jul 15, 2015 5:16AM Answer ✓

    Hi Scott

    What about the approach I described in this post: ?

    It eliminates the need for any dynamic SQL and I think the filtering options could be implemented using SQL/XML features.

    Kiran PawarScott Wesley
  • Scott Wesley
    Scott Wesley Member Posts: 6,259 Gold Crown
    edited Jul 16, 2015 11:25PM

    Ahh, that chestnut.

    Removing need for dynamic SQL would be handy, and filtering with bind variables.

    Do you know how the report responds when varying columns over PPR?

    I'm having trouble adapting the SQL/XML translation to my scenario - XML queries are still quite foreign to me. It may just be that time of the day, but Connor always taught me they were poison ;p

  • Scott Wesley
    Scott Wesley Member Posts: 6,259 Gold Crown
    edited Jul 17, 2015 2:24AM

    I persisted and updated my demo with the XML solution, and it behaves as I would have liked.

    That's pretty cool. And faster.

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    edited Jul 17, 2015 7:09AM
    scott.wesley wrote:
    
    I persisted and updated my demo with the XML solution, and it behaves as I would have liked.
    
    That's pretty cool. And faster.
    

    Happy to help. (Happier still if you give credit to the correct Paul: @fac586)

    Could you show your query? I was going to to take a look at your scenario today or over the weekend.

  • Scott Wesley
    Scott Wesley Member Posts: 6,259 Gold Crown
    edited Jul 19, 2015 9:07PM

    There's a glitch somewhere, I first marked it as helpful, then as correct - now the post is marked as answered, but obviously not recognising which response.

    This is the code I used in the sample page

    with order_pivot as ( 
          select 
              deptno 
            , job_xml 
          from 
              (select 
                  deptno, job
                ,empno
              from 
                  emp o 
              where (job like :P39_JOB or :P39_JOB  is null)
              and job like :P39_CEO||'%'
                  ) 
          pivot xml 
              (count(empno) n_orders for job in
                (select job from emp
                 where (job like :P39_JOB or :P39_JOB  is null)
                 and job like :P39_CEO||'%')
               )
    ) 
    select 
        deptno 
      , xmlserialize( 
          content 
          xmltransform( 
              job_xml 
            , xmltype(q'{ 
                <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
                  <xsl:output method="html" /> 
                    <xsl:template match="/PivotSet"> 
                      <xsl:for-each select="item"> 
                        <td> 
                          <xsl:attribute name="headers"> 
                            <xsl:value-of select="column[@name='JOB']" /> 
                          </xsl:attribute> 
                          <xsl:value-of select="column[@name='N_ORDERS']" /> 
                        </td> 
                      </xsl:for-each> 
                    </xsl:template> 
                </xsl:stylesheet>}'))) matrix 
    from order_pivot 
    order by deptno
    
  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    edited Jul 20, 2015 4:01AM
    scott.wesley wrote:
    
    There's a glitch somewhere, I first marked it as helpful, then as correct - now the post is marked as answered, but obviously not recognising which response.
    with order_pivot as ( 
          select 
              deptno 
            , job_xml 
          from 
              (select 
                  deptno, job
                ,empno
              from 
                  emp o 
              where (job like :P39_JOB or :P39_JOB  is null)
              and job like :P39_CEO||'%'
                  ) 
          pivot xml 
              (count(empno) n_orders for job in
                (select job from emp
                 where (job like :P39_JOB or :P39_JOB  is null)
                 and job like :P39_CEO||'%')
               )
    ) 
    select 
        deptno 
      , xmlserialize( 
          content 
          xmltransform( 
              job_xml 
            , xmltype(q'{ 
                <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
                  <xsl:output method="html" /> 
                    <xsl:template match="/PivotSet"> 
                      <xsl:for-each select="item"> 
                        <td> 
                          <xsl:attribute name="headers"> 
                            <xsl:value-of select="column[@name='JOB']" /> 
                          </xsl:attribute> 
                          <xsl:value-of select="column[@name='N_ORDERS']" /> 
                        </td> 
                      </xsl:for-each> 
                    </xsl:template> 
                </xsl:stylesheet>}'))) matrix 
    from order_pivot 
    order by deptno
    

    I was actually referring to the attribution here to "Paul Broughton", for I am not he. A basic link to the thread will do. If/when Oracle/Jive sort out the profile and privacy setting bugs then further details may be found there.

  • Scott Wesley
    Scott Wesley Member Posts: 6,259 Gold Crown
    edited Jul 20, 2015 4:10AM

    Ahh, der - recent memory mixed you two up - I know so many Pauls.

This discussion has been closed.