Skip to Content

Only 64k rows? I used to hear an requirement from my customers regarding BW report’s length very often.  There are several reasons why BW reports should not be that long but usually customers resist in it and simply they want to see all rows that report can produce. Therefore from this viewpoint a limitation in MS Excel 2003 (and its predecessors) of displaying in particular 65.536rows is seen as obstacle. Once new MS Excel 2007 came to the market (in 2007) it was assumable that since this limit is overcame up to 1,048,576rows it has impact on BW reporting as well. However if you see e.g. on SDNwhat people are saying regarding this limitation; there are still doubts how many rows can be displayed. Those can be eye witnessed e.g. Export 65000+ lines to excel 2007 ?, Excel 2007 or Error with Excel 2007. At the time I’m was researching what’s the matter really is I came into the conclusion that only with BW 7.x limitation of 65k rows is overcome. Unfortunately I was wrong. To be really sure I did few test which I’m going to introduced further in this blog post.

I prepared scenario with one very simple BW report without any selection screen based on demo cube ZD_SALES. I filled this cube with approximately 100k of records. I placed this cube and whole its data flow into BW based on version 7.0. I used SAP GUI version 7.2 and MS Excel 2007 together with whole Office 2007 installed on my laptop. Test query was comprised with just 4 columns. To be really sure that there is no aggregation in place Document Number was always unique in my test data loaded into cube.

image

As a first trial; I relied on transaction RSRT and I ran my report in Query Display mode = List. In this mode on MS Excel formatting is in not place so we can assume that we get all of data out of cube in our report. As seen on screenshot we have also total lines displayed. Therefore all rows from cube are displayed.

image

Later I continued with BEx Analyzer as Add-In in MS Excel 2007. From the very top; report looked good. All the columns were displayed. I started to be eager that time  just to see all 100k in my Excel…

image

 

But surprise! The report suddenly stops at (and surprise again) 65.536th row. 🙁

 image

Even I’m running BEx Analyzer based on the newest SAP GUI 7.2, even I’m using BW 7.0 as backend even my MS Excel is version of 2010 I’m still not able to get more than 65k records.

What to do next? I tried to find some information on OSS. There are notes like 1411545 – BExAnalyzer: safety belt for large result sets and 1499986 – BExAnalyzer: safety belt default value does not work which suggest to set parameter called ANALYZER_LIMIT_DEF into table RSADMIN. However after setting it my report got me only 65k records.

Hmm, ok, again no luck. What about trying to google it up? Finally there is OSS’s wiki post bringing some light on this issue. It says that limitation of 65k is due to the functionality which is exporting the data from SAP systems in general. It is called XXL Export and it cares of generic export of any list data object into the MS Excel. Also is written here that this functionality is not planned to be changed. Ok, game seemed to be over for me that time. Moreover I found interesting discussion in this SDN Problem with BEx Analyzer (SapGui640 BEx3.5) and MS Excel 2007. User posted reply from SAP OSS related to this. It is stated the same as in wiki post: “The excel file can have maximum 65536 rows. You cannot select more than this amount of rows using excel.” SAP is explaining within this that there are practical reasons for this limit. They stressing out point that BEx is not a tool of mass data extraction etc.

Game over, this time for real. But what would be the other option how to get avoid this limitation. At the moment following options came to my mind:

1.    Usage of  web reporting for export of more than 65k rows. According the note:1127156 – Safety belt: Result set is too large, in BW version 7.x it I possible to set parameters that would allow such a massive export of data.

2.    Functionality of OpenHubs. Data can be placed directly into the files (e.g. in CSV format) on application server. Be careful here OpenHubs are specially licensed and by using it there are additional fees.

3.    Coding of custom ABAO report which runs BW query and gets all its output and saves it in the flat files.

I completely realize that those options are not based on BEx Query Analyzer nor even in MS Excel but I cannot see any other option at the moment.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Guido Brune
    Hello,

    no one can analyse 65k rows. I think you have to talk about the report design with your customer.

    Furtheron there are the CRM API and Web API which allows you to execute BEx reports in batch
    and help to present a file for further analysis.

    All the best,

    Guido

    (0) 
    1. Standa Jager
      We are facing the same issue -> to get from BI many rows for offline purposes and analyze them (with Pivot Table).

      We have already experiencies with more than 450.000 rows extracted from an query (with program writequery), saved as csv and imported into excel pivot table and saved the file.

      Excel (2003) file has at about 18 MB and its faster than any BI query would ever could be (without any licence fees, BI accelerator and so on).

      (0) 
  2. Dominik Kacprzak
    old bad habits :). They want to export and work on data volume in pivot tables. For this they dont need BI, a good abap consultant, background job and you have it.
    That why I am always ask what decision do you expect to make based on this data. If you want to aggregate data, tell me, I will do it on query. You need Top N, all steps could be available using one button.
    I treat this post rather like technical challenge.
    (0) 
  3. Arvind Sundar
    I dont know whether I understood your issue completely. I just wanna share this, Can you Pls check the BEx analyzer: Global settings->under default workbook-> uncheck Use XLS File Format. Run the query now and let me know you are able to see more than 65K rows.

    Thanks,

    Anand

    (0) 
  4. Mikhail Budilov
    Yes, it terrible limit.
    Most of our concurent products can do this.

    But… if you have SAP BusinessObjects Analysis, edition for Office you can more (till 3GB in memory).

    (0) 

Leave a Reply