Dynamic reporting in Analysis for office

[tim_atkins]

Hi All,

Has anyone had experience in creating a set of annual financial statements using Analysis for Office. We are using BPC (10.1 embedded) as the consolidation engine, but A40 fro reporting.

We'll have a potential 42 notes to report, but if it just a small dormant company, we'll only need 3-4 notes. Ideally we'd like to have one workbook with all the notes and then dynamically build the report depending on the company requirements.

There'll be a few paragraphs of commentary per note we'd also need to pull in.

Initially, we could keep all the queries as data sources in the workbook, but only to refresh and insert into the workbook if actually needed. I am hoping there are some more undocumented VBA api's methods we can use to insert a datasources as a crosstab into a sheet.

Otherwise, we'd need to populate an array and use that as the cross tab in the report.

Any ideas welcome, I may almost be re-building SAP Disclosure Management in BW using A40.

Many thanks

Tim

[reyemsaibot]

Hi Tim,

i read your text twice and I still don't understand what you need or what you want to do. Maybe you can explain it a little bit more.

Can't you select your data via a variable? You can also use VBA to set a variable.

BR,

Tobias

[tim_atkins]

Hi Tobias,

We need to be able to report a Statutory financial statement set. So

1) Balance Sheet

2) Income Statement

3) Stat of Changes in Equity

4) Cash Flow

This will be constant across companies. However, for the Group results (after consolidation), we need the above statements, but also include notes:

PPE Movements

Intangible movements

Financial instruments breakdown.

There are 42 different notes needing various queries to populate the data.

The end result of all the reports will be in one workbook to be sent to printers to format and produce annual financial statements.

The challenge is there are about 30 companies and 18 dormant companies. These have different requirements with notes. So making 1 change for standard reporting for the 30 companies, could result in having to change 30 worksbooks if they are all hard coded.

If I am amble to have all the data as data sources in the workbook (not cross tabs, yet). Then dynamically populate the workbook with queries as needed by inserting relevant cross tabs, that may go a long way to making this dynamic.

I am not sure Analysis for Office can be used for complex reports with multiple queries.

I hope that has not made this more confusing. I  do have a few ideas, but would like to know if anyone else has done something similar.

Tim

[TammyPowlas]

Tim:

I understand what you are trying to do

We have our balance sheet/profit & loss in Crystal Reports, using the desired formats

I just don't know / understand the source of your notes?  I understand the notes to the financial statements, but for us, that is done outside of SAP.

I am interested to hear/learn if you achieve this.

[tim_atkins]

Unfortunately we only have access to A40 at the moment for the reporting.

Most of the notes can be sources from the detail data in SAP

Other notes will be input into a DSO for reporting.

We are using BPC 10.1 embedded consolidations, so this is going to be an interesting exercise all round.

I suppose I am looking for a good sold approach for reporting in A40 - (i.e. beyond one cross tab per workbook )

[reyemsaibot]

But couldn't you insert all your datasource on single sheets and with VBA you set for example

If report = "abc" then

Sheet1.Visible = True

Sheet2.Visible = False

Sheet3.Visible = False

Sheet4.Visible = True

Print or what ever you want

End if

And then you have a "master sheet" where you can select your report you want. And a button execute the VBA code. There you are also able to fill your variables of the DataSources via VBA.

I hope the explanation is clear

[michael_tocik5]

Hi Tim,

We use AO for all of your listed reporting types. We found the crosstabs limited to the display our users would like and therefore we make extensive use of the SAPGetData formulas to present the data in the format they require.

Some things to consider if you do go down this route is;

• Performance - Depending on the number of SAPGetadata that are used in a workbook this can have a negative impact
• Support - Adds an overhead on supporting the workbooks if things change on your queries this also depends on the level of reporting you have.
• Scheduling - This is supported on the BO Platform but with a number of limitations that make this not an option for us.