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

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

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

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

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

Wim vd Veer

Hello Brajesh,

Thanks a lot for you explanation..!!

Best regards,

Wim

Anusha Dupuguntla

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

Anusha Dupuguntla

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