Offline Reporting with Excel? --> complete Report +hierarchy possible?

[Former Member]

Hi people!

I’m new in SAP BW and I have a question regarding BEX. My goal is to get a complete Excel report that contains all the data and has a working hierarchy structure without connection to BW!

I generated a report with Query Designer whose data are represented in several hierarchy levels (option in row-header).

=>date

===>area

=====>customer

=======>material

After the execution of the query in BEX Analyzer or in the web, only the highest level (date) is indicated. If I open the next levels, the data are reloaded of the BW each time.

But I’d like to load the data completely despite hierarchy (Transaction RSRT and then properties “load all data” is selected).

Ok to get all the data, I could change the option in Query Designer to open all the levels after the first load, but the hierarchy levels should be closed at the first time.

<u>My Question is:</u>

Is there a possibility to get all the data offline in Excel with working hierarchy levels? It’s important for me to get a complete report without connection to BW and so the people can navigate also offline, e.g. open a lower hierarchy level with all the data.

After saving my report as *.xls-file, the report contains only the part of the shown results and the hierarchy symbols have no function.

I don’t need any symbols in offline report. I think, the excel function “group by” would enough for my requirements. But I don’t know how can I use this function with my data after running the query.

In SAP R/3 the same is possible automatically, when a report will be generate.

I have tried to find responses here in forum. But the search is unfortunately a little complicated and the results were not satisfactory.

- Why I can’t get a list of the main-topic’s?

- How can I get satisfactory results for multiple words?

OK, I have read something about OLE and BSP, but I hope to find an easier solution without programming.

Maybe the tool XL Reporter would be a solution, but we have no Business One licence. Unfortunately, current links for download a free version (I don’t know if its free), which I found here in the forum are not working for me. What’s wrong with XL Reporter?

I would be glad about helpful replies.

[Former Member · Accepted solution]

T-Code: RSRT

select the query

click properties button

change readmode from H (read hierarchy at navigation) to A (read all hierarchy at once)

that should do it.

Alternatively you could tell it to open the hierarchy to the lowest level on opening. That way you would have all the data also (this you change in the properties of the query hierarchy)

[Former Member · Accepted solution]

Thanks Rob for your quick answer.

But if you read the third part of my first post, I use these options already. Unfortunately this does not help me for my requirements.

Also I don't want open all hierarchy levels at the execution of the report.

I need an offline report in excel and want open the lower levels later (with all the data) in the excel sheet. I think the solution is the "group by" function of excel, but I don't use it before.

It seems so, that nobody has really a solution, so I think its not so easy to solve the problem.

Or my requirements are standard functionality and I should it find out by myself.

I spent a lot of time to search the forum, but I can't find a helpful solution.

I search for "excel+hierarchy" and "offline report", but maybe that are the wrong terms.

Does somebody have some tips for the SDN search?

Maybe somebody can say to me how I can concern this problem.

[Former Member · Accepted solution]

Hi,

As far as I know, off-line workbooks are static. You will not be able to navigate through report or drilldown it.

Best regards,

Eugene

[Former Member · Accepted solution]

Thanks Eugene for the info. I've found the answer to my question indirectly here.

Now I will try to load the data completely and without formatting in Excel. Then I try to realize my requirements with vba code.

Maybe somebody has suitable vba examples.

Points will award.

[Former Member · Accepted solution]

Hi Jens,

So, now you want not to detouch workbook from a server and work on-line?

Could you repeat your requirements? What VBA code should do?

Best regards,

Eugene

[Former Member · Accepted solution]

Hi Eugene!

Thanks for your trouble and sorry for the late answer, but unfortunately I had to do a lot.

I think, to describe my idea is not so easy with my English. But maybe you can understand it.

First I should say, the web report and the offline Excel report are two different things now. I don't save the webreport to excel.

The web report gets a hierachy and other formatings and with access to BW the user can use the report just as one likes.

My idea for the offline Excel report is to save the complete data without hierarchy or other formattings. So every characteristic and every key figure is in a separate column and all rows contains data.

date/area/customer/material/sales volume/other kf

01.11.2005/100/34567/66000/34.000 euro/...

01.11.2005/100/34567/67000/23.000 euro/...

01.11.2005/100/34567/68000/14.000 euro/...

01.11.2005/100/34567/69000/43.000 euro/...

01.11.2005/100/40200/33000/11.000 euro/... (new customer)

01.11.2005/100/40200/44000/10.000 euro/...

01.11.2005/100/40200/68000/35.000 euro/...

01.11.2005/100/40200/69000/22.000 euro/...

...

Then the created Excelsheet should be handled by vba.

As far as I know, you can select the data in Excelsheet with normal SQL commands. If the corresponding data are selected, I try to use the group by function in vba code.

So first I select the materials from the first customer. If I found the next customer I want to group all materials for the previous customer.

The Result should look so: (+ -> to open the materials)

01.11.2005/100/34567/__/+____________/...

Sum_______/___/_____/__/114.000 euro /...

01.11.2005/100/40200/ /+____________/...

Sum_______/___/_____/__/ 78.000 euro /...

-

---

The next Step is to group all customer in one area.

01.11.2005/100/+_____/__/____________/...

Sum_______/___/______/__/192.000 euro/...

01.11.2005/200/+_____/__/____________/...

Sum_______/___/______/__/320.000 euro/...

-

---

And the last point is to group all areas per date.

01.11.2005/+___/_____/__/____________/...

Sum_______/____/_____/__/512.000 euro/...

02.11.2005/+___/_____/__/____________/...

Sum_______/____/_____/__/433.000 euro/...

-

---

Now If I have a hierarchy structure, the offline user of the report can open the single levels to see the details.

[Former Member · Accepted solution]

Jens,

It seems to me that the only way to get all of the data available off-line is to run and save the report at the lowest level.

You can then hide the details using Excel Data >> Group and Outline >> Group. To do automate this, you could use VBA code. Example is shown below.

'group and hide account aging buckets

Set myRange = Range(Cells(firstRow, openCol + 1), Cells(firstRow, lastCol)).EntireColumn

myRange.Columns.Group

myRange.Columns.Hidden = True

'undo grouping

set recWS = ActiveSheet

Do Until recWS.Columns.OutlineLevel = 1

recWS.Columns.Ungroup

Loop

recWS.Columns.Hidden = False

- Pete

[Former Member]

Hi Jens,

nice, but difficult to manage... what we are actually implementing for a big european customer is a pure offline solution that is based on a certain connectivity between BW and Excel. The requirement behind this is that BW data is required for the daily work of Sales Employees, and what they want is an offline functionality including all features of a multidimensional reporting tool (slice, dice, filter, hierarchies...).

We had chosen the following scenario:

- The offline part is based on Excel Pivot functionality. MS Excel Pivot can be based not only on other Excel worksheets, but also on ".cub"-files (Files that are originating from the MS Analysis services).

- The .cub Files are spread via sendmail or via Lotus Notes replication

- In order to convert the BW query data to the .cub format, we implemented a library in BW that extracts the data from queries or query views. The conversion to .cub must be done on a MS platform (having ADO - Active Data Objects installed), so we implemented another WebService running on MS and encapsulating the conversion process.

- To link the SAP and the MS platform, with the goal that BW is the leading process instance, a job was defined that is calling the data retrieval from a Query, making the call to the conversion WebService on MS, getting the .cub file returned and initiating the distribution to the end users.

Just an approach, somehow much work to implement it, but (what the main objective was) - it works!.

Best regards,

Steen

[Former Member]

Steen and Peter thanks for your helpful answers.

@ Peter.

Now I will gain some experience with the programming in vba and then I will try to use your code.

@ Steen

This is a very interesting scenario. But I think for my requirements and knowledges a little too much work. Maybe we can use it in future.

Can somebody say to me, if the Tool XL reporter can be used for my requirements?

Maybe there are other tools which could be used?