How long BEx report in Excel can be?

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.

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.

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…

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:

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.

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.

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.