Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How do we split the report into another sheet if the first sheet reaches maximum limit of 65000

Received Response
1608
Views
28
Comments
2

Answers

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    I mean you need group by. Follow the below link to create two group within same dataset. Our aim is to group the data in the set of 65000 in each group.

    https://docs.oracle.com/middleware/12211/bip/BIPDM/GUID-E8BD22BB-9CC7-462C-99F6-D3FEF55BB7BE.htm#BIPDM259

    So your group from current group by from  data set will be

    <GROUP1>

      <countrow >1<countrow >

      <GROUP2>

        <item>  XXXX </item>

         <stock_on_hand> XXXX   </stock_on_hand>

         <loc> XXXX   </loc>

    </GROUP2>

    </GROUP1>

    Hope I am clear?

    Regards,

    Brajesh

  • Anusha Dupuguntla
    Anusha Dupuguntla Rank 4 - Community Specialist

    Hi,

    Please find the attached data model and excel template which I have designed.

    The query is giving above 1k records. I wan to split the report in to multiple sheets based on the row count.

    Please help me with the changes which I have to change in my SQL code as well as in the template to obtain the result in multiple sheets.

    Thank you so much for your help.

    Regards,

    Anusha

  • Anusha Dupuguntla
    Anusha Dupuguntla Rank 4 - Community Specialist

    Hi Rajesh,

    Thanks. The link you have provided is of version 12.2.1.1.0 but we are using 10.1.3.4. We  don't have as many options as in 12.2.1.1.0 .

    Can You please help me with that?

    Thanks once again for your inputs.

    Regards,

    Anusha

  • Anusha Dupuguntla
    Anusha Dupuguntla Rank 4 - Community Specialist

    Hi Rajesh,

    Thank you so much for your inputs.

    1. I added CEIL(ROWNUM/65000) COUNTROW in the query. Please find the below query:

    select item,stock_on_hand,loc,ceil(rownum/65000) countrow from item_loc_soh where stock_on_hand>0;

    2. My doubt was where to create new group G_COUNTROW and how to create that. Can you please explain me in detail so that it will help me a lot at the starting stage.

    Thanks a lot in advance.

    Regards,

    Anusha

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    I took your XML and create two group and added  XDO_SHEET_NAME_? as well in XDO METADATA. It worked for me , created two worksheet

    XDO_SHEET_NAME_?Brajesh

    image

    image

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    Share your sample XML....just with 10-15 rows

  • Anusha Dupuguntla
    Anusha Dupuguntla Rank 4 - Community Specialist

    Hi,

    Thank you so much !!! It worked.

    Regards,

    Anusha

  • Anusha Dupuguntla
    Anusha Dupuguntla Rank 4 - Community Specialist

    Hi,

    Please find the below xml data:

    <LIST_G_RECORD_GROUP>

    <G_RECORD_GROUP>

    <RECORD_GROUP>1</RECORD_GROUP>

    <LIST_ROW>

    <ROW>

    <ITEM>1000000532098</ITEM>

    <STOCK_ON_HAND>24</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000308556</ITEM>

    <STOCK_ON_HAND>92</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000308570</ITEM>

    <STOCK_ON_HAND>86</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000243864</ITEM>

    <STOCK_ON_HAND>13</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000254402</ITEM>

    <STOCK_ON_HAND>6</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000369571</ITEM>

    <STOCK_ON_HAND>18</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000371406</ITEM>

    <STOCK_ON_HAND>1</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000471311</ITEM>

    <STOCK_ON_HAND>1</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000473223</ITEM>

    <STOCK_ON_HAND>3</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000515411</ITEM>

    <STOCK_ON_HAND>50</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000419818</ITEM>

    <STOCK_ON_HAND>1</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000478846</ITEM>

    <STOCK_ON_HAND>2</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000497717</ITEM>

    <STOCK_ON_HAND>1</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000515671</ITEM>

    <STOCK_ON_HAND>36</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000296617</ITEM>

    <STOCK_ON_HAND>39</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ITEM>1000000369571</ITEM>

    <STOCK_ON_HAND>18</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000371406</ITEM>

    <STOCK_ON_HAND>1</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    <ROW>

    <ITEM>1000000471311</ITEM>

    <STOCK_ON_HAND>1</STOCK_ON_HAND>

    <LOC>568</LOC>

    </ROW>

    Regards,

    Anusha

  • Anusha Dupuguntla
    Anusha Dupuguntla Rank 4 - Community Specialist

    Hi Again,

    Thanks a lot for sharing the example.

    I am getting the XML data like as you have mentioned in the example. I also added  XDO_SHEET_? by specifying the group name as <?.//G_RECORD_GROUP?>  in XDO_METADATA sheet. My problem is when I am viewing the template in excel I am getting 65000 records in sheet1 and another sheet is created but it is empty.

    Do I need to add anything else to get the data the records in the next sheet ? I have attached my design template for your reference. Please help me with this.

    Regards,

    Anusha

  • Wim vd Veer
    Wim vd Veer Rank 3 - Community Apprentice

    Great and Thanks a lot for sharing this nice example with us...!!!