5 Replies Latest reply: Sep 17, 2014 12:36 PM by Michael Novo RSS

    Dynamic Pivot Table Summary

    Michael Novo

      Good day!  Recently Frank Kulash assisted me in coding a pivot table to get a static output range of results for value totals per month using Oracle 11g

       

      My new issue I'm working towards is using a pivot table to get the values as I did before as well as the totals per year.  To get the current correct results let me show what I have...

       

      Current Pivot Query:

      --------------------------------------------

      WITH    KEYACM    AS

      (

          SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID,

          row_number () OVER ( PARTITION BY  CF_KEYWORD_ID

                                       ORDER BY      CF_PERIOD desc

                                     )   AS r_num

          FROM    AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA

      )

      SELECT    *

      FROM      KEYACM

      PIVOT     (   MIN (KEYACM.CF_VALUE)     AS VALUE

                ,   MIN (KEYACM.CF_PERIOD)  AS PERIOD

                FOR r_num  IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)

                )

      ORDER BY  CF_KEYWORD_ID ;

      -------------------------------------------------

       

      This breaks up the CF_VALUE and CF_PERIOD by CF_KEYWORD_ID and the results look like this (let's assume that JAN-14 is the current period)

       

      CF_KEYWORD_ID     1_VALUE     1_PERIOD     2_VALUE     2_PERIOD     3_VALUE     3_PERIOD etc...till 24_VALUE     24_PERIOD

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      available_reserve          -100.50         01-JAN-14     375.00        01-DEC-13      21300.74     01-NOV-13             900.50             01-FEB-12

       

      With the results above the values are correct and display perfectly within Jasper Ireports.  What I'm looking to do is insert either 2 or 3 columns that would be a total of each year SUM_1, SUM_2 and SUM_3 (no label is needed).  In the results above there should be one total for 2014 which will include just the month of January.  Then there should be a total for all of 2013 and for 2012 EXCLUDING JAN since it did not make it into the last 24 months of the selected period.  If the selected month was DECEMBER then you would only have two summaries since you would be looking at two full years.

       

      Is there a solid way of accomplishing this?

        • 1. Re: Dynamic Pivot Table Summary
          Frank Kulash

          Hi,

           

          One way is to do a GROUP BY ROLLUP (or a UNION) in the sub-query, to get the sums you need before pivoting.

          As always, if you'd like help, post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data.  Simplify the problem.  For example, only include 3 months per year, so you don't have to post so much.

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: Dynamic Pivot Table Summary
            Michael Novo

            Thanks Frank, actually the requirements just shifted as I found out and I should only show the current year (from parameter field) and Jan-Dec of previous year.

             

            $P{DateParameter} passed in from Ireports

             

            SELECT CF_KEYWORD_ID, CF_VALUE, CF_PERIOD

            FROM AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA

             

            I'll insert an Excel output of what I'm looking for...Untitled.png

            • 3. Re: Dynamic Pivot Table Summary
              Michael Novo

              I created a PIVOT which does get me the results exactly as expected.  It shows my all values for 2014 and 2013 even the null ones which is what I'm looking for in the Excel snippet I posted above.  Now the only question is how to make the dates below dynamic so that they take the YEAR of the parameter the user enters and make it into a variable so that the pivot values can be dynamic and not hardcoded.  The below query otherwise works perfectly...any suggestions?

               

              WITH    KEYACM    AS

              (

                  SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID

                  FROM    AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA

              )

              SELECT    *

              FROM      KEYACM

              PIVOT     (   MIN (KEYACM.CF_VALUE)     AS VALUE

                        ,   MIN (KEYACM.CF_PERIOD)  AS PERIOD

                        FOR CF_PERIOD IN ('01-JAN-14', '01-FEB-14', '01-MAR-14', '01-APR-14', '01-MAY-14', '01-JUN-14', '01-JUL-14', '01-AUG-14', '01-SEP-14', '01-OCT-14', '01-NOV-14', '01-DEC-14',

                                          '01-JAN-13', '01-FEB-13', '01-MAR-13', '01-APR-13', '01-MAY-13', '01-JUN-13', '01-JUL-13', '01-AUG-13', '01-SEP-13', '01-OCT-13', '01-NOV-13', '01-DEC-13')

                        ) KEYACM

              ORDER BY  CF_KEYWORD_ID

              ;

              • 4. Re: Dynamic Pivot Table Summary
                Frank Kulash

                Hi,

                 

                Column names (or aliases) must be hard-coded in the query when it is compiled.  If you want column names that depend on the data found, then you need dynamic SQL.  (If you're using SQL*Plus, this isn't too hard, using substitution variables.)

                See:

                Help for a query to add columns

                 

                One alternative is to include the dynamic column headings in the result set itself.  You can format it so people reading the output will think the first line (or two) is a heading, not part of the result set.  for an example, see:

                Re: Please Help in query

                • 5. Re: Dynamic Pivot Table Summary
                  Michael Novo

                  In case anyone is interested, it might not be the ideal way but here's snippets of how I coded the final solution.

                   

                  SELECT

                           (CASE WHEN KEYACM.CF_PERIOD = TO_DATE(CONCAT('01-JAN-', TO_CHAR(TO_DATE($P{PERIOD}, 'MON-YY'), 'YY'))) THEN KEYACM.CF_VALUE END) AS JAN

                    ,      (CASE WHEN KEYACM.CF_PERIOD = TO_DATE(CONCAT('01-FEB-', TO_CHAR(TO_DATE($P{PERIOD}, 'MON-YY'), 'YY'))) THEN KEYACM.CF_VALUE END) AS FEB

                    ,      (CASE WHEN KEYACM.CF_PERIOD = TO_DATE(CONCAT('01-MAR-', TO_CHAR(TO_DATE($P{PERIOD}, 'MON-YY'), 'YY'))) THEN KEYACM.CF_VALUE END) AS MAR

                    ,      CONCAT('JAN-', TO_CHAR(CF_PERIOD, 'yy')) AS JAN_PER

                    ,      CONCAT('FEB-', TO_CHAR(CF_PERIOD, 'yy')) AS FEB_PER

                    ,      CONCAT('MAR-', TO_CHAR(CF_PERIOD, 'yy')) AS MAR_PER

                   

                  The first three in the CASE WHEN statement will give me the value for whatever the year is that's input.  The date value is always the first of the month which is why I'm looking at year only and always CONCAT 01-MON

                  The last 3 statements of the select will give me the correct column heading to display above the column.  Below is a final copy of what the report looks like.  They do want the blank columns to show...basically display it like a calendar even if there's no data.

                   

                  Untitled.png