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

Anusha Dupuguntla

Summary

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

Content

Hi Team,

Currently, I am creating template in excel format. When I am viewing the report I found that the excel is taking 65,000 records only but we have more than 1 lakh records.

How can we split the output into another sheet if the first sheet reaches the maximum limit of the excel.

Can any body please help with the above issue. Currently we are using BI publisher 10g  version 10.1.3.4

Regards,

Anusha

Brajesh Shukla-95078

XDO_SHEET_? (you can specify the condition to move the data to new sheet)

and XDO_SHEET_NAME_? are the tags name using that you can specify your condition and can rename the workbook name. Refer the below document for more details

BI Publisher: How to create Multisheet in Single Excel file using Excel Template (Doc ID 1352000.1)

Brajesh Shukla-95078

DO you have rtf or excel template?

Wim vd Veer

Hello Brajesh,

Thanks a lot for your fast response.

I saw the document 1352000.1 before, but I never managed to add a calculation for the condition to split the data by 65000 rows. I only managed to do it as described in the document.

Can you please guide me, on how I have to specify the condition for the split XDO_SHEET_?

Thanks for your help in advance.

Best regards,

Wim

Wim vd Veer

Interesting question, because I faced the same issue, and so far never managed to solve it. Looking forward to hear a solution..!!

Anusha Dupuguntla

Hi,

We have excel template.

Regards,

Anusha

Brajesh Shukla-95078

Sorry somehow missed this thread.

You can follow the below steps

1. Add column  CEIL(ROWNUM/65000) COUNTROW  as new column in  the query which is generating the line details.

2. in the group create new group G_COUNTROW and place element as  COUNTROW on and under that your remaining line level detail will be there in same line group so XML data will look like as below

    <G_COUNTROW>

                  <G_COUNTROW>1</G_COUNTROW>

                 <GROUPLINE1>

                   <LINE_DETAIL1>ABC1</LINE_DETAIL1>

                   <LINE_DETAIL2>ABC2</LINE_DETAIL2>

                   .

                   .

                </GROUPLINE1>

    </G_COUNTROW>

    <G_COUNTROW> #This Group will create only if row are more than 65000#

                  <G_COUNTROW>2</G_COUNTROW>

                 <GROUPLINE1>

                   <LINE_DETAIL1>ABC1</LINE_DETAIL1>

                   <LINE_DETAIL2>ABC2</LINE_DETAIL2>

                   .

                   .

                </GROUPLINE1>

    </G_COUNTROW>

3. In the XDO_SHEET_? specify group name as <?.//G_COUNTROW?>

Regards,

Brajesh

Wim vd Veer

Hello Brajesh,

Thanks a lot for you explanation..!!

Best regards,

Wim

Anusha Dupuguntla

Hi Rajesh,

Thanks for your response!

I have seen the document previously which you have mentioned. But it doesn't work in my case. I want to know how do we split the report into another sheet if the first sheet reaches maximum of 65K records by using single data model only.

Can we mention any logic in the SQL code in order to get the output as mentioned above. If yes, can you please help me with the syntax how to do that.

Regards,

Anusha

Brajesh Shukla-95078

Hi,

Please define your data template as below.

And put the below XML Code

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

<dataTemplate name="XXXX" version="1.0">

<properties>

<property name="debug_mode" value="on" />

</properties>

        <dataQuery>

        <sqlStatement name="Q_ITEM_LOC">

            <![CDATA[select item

              ,stock_on_hand

  ,loc

  ,ceil(rownum/65000) countrow

    from item_loc_soh where stock_on_hand > 0]]>

        </sqlStatement>

    </dataQuery>

    <dataStructure>

        <group name="G_RECORD_GROUP" source="Q_ITEM_LOC">

            <element name="RECORD_GROUP" value="COUNTROW"/>    

            <group name="ROW" source="Q_ITEM_LOC">

                <element name="ITEM" value="ITEM"/>

                <element name="STOCK_ON_HAND" value="STOCK_ON_HAND"/>

                <element name="LOC" value="LOC"/>

            </group>   

        </group>

    </dataStructure>

</dataTemplate>

The above will generate the XML in below way

<XXXX>

<G_RECORD_GROUP>

<RECORD_GROUP>1</RECORD_GROUP>

<ROW>

<ITEM>11233</ITEM>

<STOCK_ON_HAND>1121212</STOCK_ON_HAND>

<LOC>121212121</LOC>

</ROW>

<ROW>

<ITEM>4333</ITEM>

<STOCK_ON_HAND>55433</STOCK_ON_HAND>

<LOC>76543</LOC>

</ROW>

.

.

.

65000 times ROW group will appear RECORD_GROUP 1

and for the 65001 records it will create new group G_RECORD_GROUP

</G_RECORD_GROUP>

<G_RECORD_GROUP>

<RECORD_GROUP>2</RECORD_GROUP>

<ROW>

<ITEM>11233</ITEM>

<STOCK_ON_HAND>1121212</STOCK_ON_HAND>

<LOC>121212121</LOC>

</ROW>

<ROW>

<ITEM>4333</ITEM>

<STOCK_ON_HAND>55433</STOCK_ON_HAND>

<LOC>76543</LOC>

</ROW>

.

.

.

</G_RECORD_GROUP>

</XXXX>

XDO_SHEET_? specify group name as <?.//G_RECORD_GROUP?>

Brajesh Shukla-95078

Can you give me Screen shot of your Group and Data Set?