7 Replies Latest reply on Jul 20, 2015 8:10 AM by Scott Wesley

    Generic columns classic report - refresh column list

    Scott Wesley

      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

        • 1. Re: Generic columns classic report - refresh column list
          fac586

          Hi Scott

           

          What about the approach I described in this post: Matrix report?

           

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

          1 person found this helpful
          • 2. Re: Generic columns classic report - refresh column list
            Scott Wesley

            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

            • 3. Re: Generic columns classic report - refresh column list
              Scott Wesley

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

               

              That's pretty cool. And faster.

              • 4. Re: Generic columns classic report - refresh column list
                fac586

                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.

                • 5. Re: Re: Generic columns classic report - refresh column list
                  Scott Wesley

                  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
                  
                  • 6. Re: Generic columns classic report - refresh column list
                    fac586

                    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.

                    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.

                    • 7. Re: Generic columns classic report - refresh column list
                      Scott Wesley

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