3 Replies Latest reply: Jan 26, 2011 5:25 PM by Tyson Jouglet RSS

    Oracle APEX Classic Report Break Formatting

    814474
      I created a classic report. Under report attributes I defined Break Formatting as "First, Second and Third Column".

      The data columns for this report are Domain, Discipline, TechnologyArea and Products.
      Domain is the parent. Discipline is a child of domain. TechnologyArea is the child of Discipline. Products are children of TechnologyAreas.

      Domain prints in the first column, Discipline in the second, TechnologyArea in the third and Product in the fourth. There are one to many products for any TechnologyArea - and one to many TechnologyAreas for any Discipline - and one to many Disciplines for any Domain. The report is sorted on Domain, Discipline, TechnolgyArea and Product. Therefore domain prints over and over and over. Discipline prints over and over until it changes. TechnologyArea prints several times before it changes. I don't like that.

      I want the report to print Domain and not print it again until it changes. I want it to do the save for Discipline and TechnologyArea.

      I set break formatting to "First, Second and Thrid Column". Now Domain prints once and does not print again until it changes. So far so good! Now Discipline prints once and does not print on subsequent rows until the discipline changes. So far so good! The TechnologyArea name prints on every row. That's not good.

      Given this pattern, if there were an option for First, Second, Third and Fourth columns I think that would give me the report I want - but there's not. Can anyone tell me how to stop TechnologyArea from repeating on every row?
        • 1. Re: Oracle APEX Classic Report Break Formatting
          VANJ
          Try playing around with the row_number analytic function
          case when 1=row_number() over (partition by c1,c2,c3 order by c4) then c3 end
          • 2. Re: Oracle APEX Classic Report Break Formatting
            Tyson Jouglet
            This look about right?

            notice the last two records in the with statement are duplicates.
            with test_data as(
                select 'MAMMAL' domain, 'DOG' discepline, 'BEAGLE' technologyArea, 'SQUEEKY TOY' product from dual union all
                select 'MAMMAL',  'DOG' ,   'RETRIEVER', 'SQUEEKY TOY' from dual union all 
                select 'MAMMAL',  'CAT' ,   'LION',      'SQUEEKY TOY'  from dual union all 
                select 'MAMMAL',  'CAT' ,   'CHEETAH',   'SQUEEKY TOY'  from dual union all
                select 'MAMMAL',  'DOG' ,   'DINGO',     'SQUEEKY TOY'  from dual union all
                select 'REPTILE', 'LIZARD', 'IGUANA',    'LARGE ROCK' from dual union all
                select 'REPTILE', 'LIZARD', 'GUILLA MONSTER', 'LARGE ROCK' from dual union all
                select 'REPTILE', 'SNAKE',  'CORAL',     'LARGE ROCK' from dual union all
                select 'REPTILE', 'SNAKE',  'PYTHON',    'LARGE ROCK' from dual union all
                select 'REPTILE', 'SNAKE',  'KING',      'LARGE ROCK' from dual union all
                select 'MAMMAL',  'DOG' ,   'BEAGLE',    'BONE' from dual union all
                select 'MAMMAL',  'DOG' ,   'RETRIEVER', 'BONE' from dual union all 
                select 'MAMMAL',  'CAT' ,   'LION',      'BONE' from dual union all 
                select 'MAMMAL',  'DOG' ,   'DINGO',     'BONE'  from dual union all
                select 'REPTILE', 'LIZARD', 'IGUANA',    'HEAT LAMP' from dual union all
                select 'REPTILE', 'LIZARD', 'GUILLA MONSTER', 'HEAT LAMP' from dual union all
                select 'REPTILE', 'SNAKE',  'CORAL',     'HEAT LAMP' from dual union all
                select 'REPTILE', 'SNAKE',  'PYTHON',    'HEAT LAMP' from dual union all
                select 'REPTILE', 'SNAKE',  'KING',      'HEAT LAMP' from dual union all
                select 'MAMMAL',  'CAT' ,   'CHEETAH',   'BONE' from dual union all
                select 'MAMMAL',  'CAT' ,   'CHEETAH',   'BONE' from dual
            )
            select * from(
            select 
                case when domain         != nvl(domain_lag,        ':START:') then domain         else null end as domain,
                case when discepline     != nvl(discepline_lag,    ':START:') then discepline     else null end as discepline,
                case when technologyarea != nvl(technologyarea_lag,':START:') then technologyarea else null end as technologyarea,
                case when product        != nvl(product_lag,       ':START:') then product        else null end as product
            from(
                select domain, discepline, technologyarea, product,
                       lag(domain,1)         over(order by domain, discepline, technologyarea, product) domain_lag,
                       lag(discepline,1)     over(order by domain, discepline, technologyarea, product) discepline_lag,
                       lag(technologyarea,1) over(order by domain, discepline, technologyarea, product) technologyarea_lag,
                       lag(product,1)        over(order by domain, discepline, technologyarea, product) product_lag
                  from test_data
                 order by domain, discepline, technologyarea, product)
            )
            where coalesce(product, technologyarea, discepline, domain) is not null
            edit
            I did not understand the question properly before submitting this. Here it is anyway.


            Cheers,

            Tyson Jouglet

            Edited by: Tyson Jouglet on Jan 26, 2011 2:42 PM
            • 3. Re: Oracle APEX Classic Report Break Formatting
              Tyson Jouglet
              also tried using row_number() and that way works too.

              Saves on some nvl's. Also I realised I did not have to use coalesce, I could just check for non null products because if product is null, everything else up the chain will be null as well.
              with test_data as(
                  select 'MAMMAL' domain, 'DOG' discepline, 'BEAGLE' technologyArea, 'SQUEEKY TOY' product from dual union all
                  select 'MAMMAL',  'DOG' ,   'RETRIEVER', 'SQUEEKY TOY' from dual union all 
                  select 'MAMMAL',  'CAT' ,   'LION',      'SQUEEKY TOY'  from dual union all 
                  select 'MAMMAL',  'CAT' ,   'CHEETAH',   'SQUEEKY TOY'  from dual union all
                  select 'MAMMAL',  'DOG' ,   'DINGO',     'SQUEEKY TOY'  from dual union all
                  select 'REPTILE', 'LIZARD', 'IGUANA',    'LARGE ROCK' from dual union all
                  select 'REPTILE', 'LIZARD', 'GUILLA MONSTER', 'LARGE ROCK' from dual union all
                  select 'REPTILE', 'SNAKE',  'CORAL',     'LARGE ROCK' from dual union all
                  select 'REPTILE', 'SNAKE',  'PYTHON',    'LARGE ROCK' from dual union all
                  select 'REPTILE', 'SNAKE',  'KING',      'LARGE ROCK' from dual union all
                  select 'MAMMAL',  'DOG' ,   'BEAGLE',    'BONE' from dual union all
                  select 'MAMMAL',  'DOG' ,   'RETRIEVER', 'BONE' from dual union all 
                  select 'MAMMAL',  'CAT' ,   'LION',      'BONE' from dual union all 
                  select 'MAMMAL',  'DOG' ,   'DINGO',     'BONE'  from dual union all
                  select 'REPTILE', 'LIZARD', 'IGUANA',    'HEAT LAMP' from dual union all
                  select 'REPTILE', 'LIZARD', 'GUILLA MONSTER', 'HEAT LAMP' from dual union all
                  select 'REPTILE', 'SNAKE',  'CORAL',     'HEAT LAMP' from dual union all
                  select 'REPTILE', 'SNAKE',  'PYTHON',    'HEAT LAMP' from dual union all
                  select 'REPTILE', 'SNAKE',  'KING',      'HEAT LAMP' from dual union all
                  select 'MAMMAL',  'CAT' ,   'CHEETAH',   'BONE' from dual union all
                  select 'MAMMAL',  'CAT' ,   'CHEETAH',   'BONE' from dual
              )
              select * from(
              select 
                  case when domain_new         = 1 then domain         else null end as domain,
                  case when discepline_new     = 1 then discepline     else null end as discepline,
                  case when technologyarea_new = 1 then technologyarea else null end as technologyarea,
                  case when product_new        = 1 then product        else null end as product
              from(
                  select domain, discepline, technologyarea, product,
                         row_number() over(partition by domain order by discepline, technologyarea, product) domain_new,
                         row_number() over(partition by domain, discepline order by  technologyarea, product) discepline_new,
                         row_number() over(partition by domain, discepline, technologyarea  order by  product) technologyarea_new,
                         row_number() over(partition by domain, discepline, technologyarea, product  order by domain, discepline, technologyarea, product ) product_new
                    from test_data
                   order by domain, discepline, technologyarea, product
                   )
              )
              where product is not null
              Cheers,

              Tyson Jouglet