Business Intelligence

Get Involved. Join the Conversation.

Topic

    Kumo70
    field cascading grouping in Report Queries - BI publisher
    Topic posted April 8, 2018 by Kumo70 Red Ribbon: 250+ Points, last edited May 7, 2018 by Arijit ChakrabortyBronze Trophy: 5,000+ Points, tagged BI Publisher 
    52 Views, 21 Comments
    Title:
    field cascading grouping in Report Queries - BI publisher
    Summary:
    field cascading grouping in Report Queries - BI publisher
    Content:

    Hi,

     

    Please note the below part#1 ive done now and works BUT my problem is part#2 for long running group records which i need to repeat the header on every page.

     

    iam relative new to Oracle BI publisher but iam trying to create a report query + template layout with grouping as below.

     

    PART#1: (DONE)

    ============================================================

    1. query like:

    =========

    though not sure if this query will give me correct output...

    select dept_name, emp_name, manager, country, location from Employees group by country.....

     

    2. report printout should look like:

    =========================

    Country: USA

    Dept: Sales

    emp_name,   location Manager

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

    ................................................

    ......             .......           ........

     

    Country: Canada

    Dept: HR

    emp_name,  location Manager

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

    ..............................................

    ......             .......           ........

     

     

    3. here is my screenshot - note there are 2 cascaded grouping outer COUNTRY main grouping then within it DEPT child grouping as below

    pastedImage_0.png

     

     

    PART#2 (i need help on this pls)

    ============================================================================

    IAM PLANNING TO repeat the header information on every page. if records for a manager group contains many (fullname, title, salary) onto the next page then the header details (department) together with column headers should show at the top of the next page

    pastedImage_0.png

    any suggestion is appreciated

     

    ...thanks.

    Comment

    • Yguttikonda

      there are some good Oracle by Example sessions avaliable in the below URL .. this is a good place to start

      http://www.oracle.com/technetwork/middleware/bi-publisher/learnmore/index.html

      Hope this helps

      --YG

    • Kumo70

      YGUTTIKONDA

      these are so useful but i still need help with repeating groups/nested as i show above - please give me a suggestion i really need this....thanks

    • Yguttikonda

      when developing the layout either in Interactive Mode or RTF Template use Group Above first by Country and then by Department

      Interactive Mode:

      RTF Template

      Below is how your template will look like ..

      Hope this helps

    • Kumo70

      YGUTTIKONDA

      i am getting very confusing error here now. After creating the inner table with groping on the left based on DEPT which works. Now when i try to add the outer/parent grouping based on country i get error that another NESTED GROUP already exist click ok to correct ..... whats this now? so groping cannot be nested here? 

      very annoying.

      any help please...thanks.

    • Yguttikonda

      if you click OK what is happening ??

    • Kumo70

      YGUTTIKONDA

      thanks again for your great help - i resolved the above issue by creating new template table then picked 2 cascaded grouping fields and it worked.

      Now ive another issue, i am trying to create same header details for those sections which are more than page size. for instance if SALES section is longer than a page size then show the header again on the next new page as below. Currently its show all continuous sections without header for the long sections.

      DEPT: SALES

      FIRST NAME

      LAST NAME

      HJJFGFGFGFG

      GFGFGFGF

      GFGFGF

      JGHJGHJG

      DFDFDFD

      GHFGFDG

      DFDFDFD

      DFDFDF

      . . . . . . . . . . . . . . . . . . . . . . . . . . ..

      . . . . .. . . . . .  . . . . . . . . . . . . . . . . .

      DEPT: SALES

      FIRST NAME

      LAST NAME

      WERWERW

      DGDSDFSD

      SDFDFDFDRR

      DFDFGDFGF

      GFHGHH

      GGGHJHHFGFDG

      SFDVD

      GHFGD

      . . . . . . . . . . . . . . . . . . . . . . . . . . ..

      . . . . .. . . . . .  . . . . . . . . . . . . . . . . .

      DEPT: HR

      FIRST NAME

      LAST NAME

      GFBGHJHMHJ

      YYUJUJKU

      HJHKJKJLJKL

      JH

      IKIKYHFG

      TDFDFGSD

      DFDFSDSD

      WEWEW

      . . . . . . . . . . . . . . . . . . . . . . . . . . ..

      . . . . .. . . . . .  . . . . . . . . . . . . . . . . .

      thanks

    • Yguttikonda

      Hello .. Pl check the table properties and make sure the Repeat as header row at the top of each page is checked..

      Also going forward please use one thread per issue. so it would help other people as well when they are searching for solutions..

    • Venkythota54

      please upload xml and layout , will send you updated , thanks

    • Yguttikonda

      please upload the template and sample xml

    • Kumo70

      YGUTTIKONDA

         ive set the table properties to check that flag but as you can in the below pdf the HEADER section didn't show up. I wonder what else is now missing?

      pastedImage_0.png

      thanks again.

    • Kumo70

      Venkat

      loading the files is not possible due to sensitivity - can you please take a look at the above details i added.

      thank you.

    • Kumo70

      YGUTTIKONDA,

      ive attached a sample xml and template files - please take a look and let me know . Maybe i need to duplicate rowset to fill the page.

      ====================================xml=======================================

      <?xml version="1.0" encoding="UTF-8"?>

      <DOCUMENT>

      <ROWSET>

         <ROW>

            <NUMBER_GROUP>354545</NUMBER_GROUP>

            <ID_VAL>8902</ID_VAL>

            <SCORE_RATE>67</SCORE_RATE>

            <TAG_NUMBER>34465656</TAG_NUMBER>

            <EMP_NAME>CALIFORNIA DEPT INSURANCE</EMP_NAME>

            <AMNT>995656</AMNT>

            <CONT_CNT>1</CONT_CNT>

         </ROW>

            <ROW>

            <NUMBER_GROUP>45001</NUMBER_GROUP>

            <ID_VAL>4511</ID_VAL>

            <SCORE_RATE>89</SCORE_RATE>

            <TAG_NUMBER>6799</TAG_NUMBER>

            <EMP_NAME>CALIFORNIA DEPT INSURANCE</EMP_NAME>

            <AMNT>995656</AMNT>

            <CONT_CNT>3</CONT_CNT>

         </ROW>

         <ROW>

            <NUMBER_GROUP>354545</NUMBER_GROUP>

            <ID_VAL>8902</ID_VAL>

            <SCORE_RATE>95</SCORE_RATE>

            <TAG_NUMBER>57001</TAG_NUMBER>

            <EMP_NAME>ALINNA HEALTH CARE</EMP_NAME>

            <AMNT>6556565</AMNT>

            <CONT_CNT>1</CONT_CNT>

         </ROW> 

          <ROW>

            <NUMBER_GROUP>354545</NUMBER_GROUP>

            <ID_VAL>8902</ID_VAL>

            <SCORE_RATE>95</SCORE_RATE>

            <TAG_NUMBER>57001</TAG_NUMBER>

            <EMP_NAME>ALINNA HEALTH CARE</EMP_NAME>

            <AMNT>6556565</AMNT>

            <CONT_CNT>1</CONT_CNT>

         </ROW> 

      </ROWSET>

      </DOCUMENT>

      ==============================================================================

      thank you.

      group ROW by TAG_NUMBER

                 

                             
        

      Employee     No: TAG_NUMBER      Emp     Name: EMP_NAME

        
        

      Created by: []

        
         

                                                                                    
        

      Number     Of Group Records

        
        

      Amount

        
        

      CONT_CNT

        
        

      AMNT

        
         

                                                                 
        

      ID

        
        

      Task Order Number

        
        

      Score %

        
        

      G V IFNUMBER_GROUP RS

        
        

      F ID_VAL    

        
        

      SCORE_RATE E E

        
         

      page breakend ROW by TAG_NUMBER

                                                                                                                                                                                                                                    

    • Rithwik

      Y dont u create a subtemplate and call that sub template in rtf which might help.

    • Venkythota54
    • Kumo70

      3591335

      i dont know why i need sub template. Pls note iam done with PART#1 above my problem is PART#2 -i.e. Repeating header details on long manager groups( Pls see above comment...).

      thanks.