Inserting titles between multiple dynamic EPM reports

[Former Member]

Hi,

I am currently working on some templates where there are multiple dynamic EPM reports with shared column dimensions and multiple dimensions in the rows.  I am using EPM Add-in SP21 patch 1.

We would like to have a section title appearing between the reports, I have tried a number of methods but have not found a solution that produces quality results.

Methods tried:

1. A local member on the row inserted at the start of the second report.  The local member text appears in the right most row dimension column which is not the right place for a title.  Also the titles for the dimension rows are not copied.

2. Insert a dummy member and override the member name.  There are a couple of problems with this method, subsequent dimension ID's are populated and local members are also applied to this member.

3. EPMInsertCellsBeforeAfterBlock.  I only managed to get this to copy a single cell into the data columns, not the row dimension columns.

4. Use VBA to add a title.  The API GetDataTopLeftCell will return the starting position of data in a specific report.  This can then be used to insert text into the previous line as a title.  The disadvantage here is that VBA is being used but it can be used to produce the required results though you need to handle formatting and any password protection within the VBA.

I imagine that this is quite a common requirement.  Ideally I would like to solve this without resorting to VBA or some exotic workaround.

Any suggestions appreciated.

Cheers,

Mark

[rishi4892]

Hi Mark,

You can pass formula for local member Header from EPMFormatting sheet.

1. Use two formatting sheet for two reports

2. Pass formula in local member Header in EPMFormattingSheet to refer to Actual Column Header.

Thanks,

Rishi

[former_member186338]

Hi Rishi,

Interesting idea! Not able to test it, but do you mean something like:

=IF(COLUMN()=1,"Report Name","")

Vadim

P.S. The issue is with multidimension row axis!

[rishi4892]

Hi Vadim,

I have not looked at "=IF(COLUMN()=1,"Report Name","") "

I am simply refering to cell which contains header in the epmformattingsheet inside local member header.

it will work perfectly for multidimensional row axis, i have tested it.

see screenshot attached.

Thanks,

Rishi

[former_member186338]

Ok, I can see duplicated headers, but the topic author requires a second local member for the subreport title:

=IF(COLUMN()=2,"Report Name","")

To put sting "Report Name" in the column B

Vadim

[rishi4892]

Yes, Vadim

Thanks,

Rishi

[Former Member]

Dear Rishi,

Using the tips that you and Vadim have put together, I have managed to get a header on the second report, though it is not working exactly as you describes.

1.  I did not need to add a formatting sheet, I just gave the local members unique names.

2. The local members were disabled after refreshing the report.  This makes making changes to the formulas a little tricky.

Other than this, it seems to work well and avoids any use of VBA.

Thanks and Regards,

Mark

[former_member186338]

Sorry Mark,

But I am unable to understand what is the idea behind:

"1.  I did not need to add a formatting sheet, I just gave the local members unique names.

2. The local members were disabled after refreshing the report.  This makes making changes to the formulas a little tricky."

Can you illustrate it?

Vadim

[Former Member]

Dear Vadim,

Rishi said that you needed to use two separate formatting sheets.  I was able to use a single formatting sheet for both reports.

When the use selection on the formatting sheet for the local member includes "content" the linked local member is disabled following the refresh.

The are two points to note

A side effect of the disabling of the local members is that the titles are repeated when ever the local members are reactivated.  The original title are not replaced, you have to manually delete the rows.

A second issue I found is that if you have text wrapping enabled on the formatting sheet for one of the local members, the row height is not automatically changed following the refresh, meaning that only a single row is shown.

I suspect that the disabling of the local member is a bug in the EPM add-in.

I hope that this is now clearer to you.

Regards,

Mark

[former_member186338]

"Rishi said that you needed to use two separate formatting sheets.  I was able to use a single formatting sheet for both reports." - Ok, at least single formatting sheet is used

"When the use selection on the formatting sheet for the local member includes "content" the linked local member is disabled following the refresh" - not clear, why do you think it's disabled?

Vadim

[Former Member]

Dear Vadim,

I have tested this feature several times and have consistently seen this behavior.

Once the "content" is added to the "Use" in the formatting sheet, the local member is disabled at the next refresh.  This can be seen by editing the EPM report and looking at the local member, additionally subsequent changes made to the formatting sheet are only applied once the local members are reactivated.

I have seen this using both EPM Add-in SP21 patch 1 and SP20 patch 2.

I would be interested to know if you find a different behavior.

Regards,

Mark

[rishi4892]

Hi Mark,

Have you tried using two EPM Formatting Sheets,

might be the formatting sheet looking for local members in both reports but since it does not found the local member in other report so it might have disabled it.

Regards,

Rishi

[Former Member]

Hi Rishi,

I tried using two EPM Formatting Sheets, but still got the same behavior.  It is only once the content is added that it is disabled. 

I take it that you do not see this? 

What version of the EPM Add-in are you using?

Regards,

Mark

[rishi4892]

Hi Mark,

I am also using SP20 Patch 2.

Regards,

Rishi

[former_member186338]

Yes, I can confirm the issue with local member disabled by formatting content override.

Vadim

[Former Member]

Dear Vadim,

Can you please check if the expansion criteria in the second EPM report are also broken.

I have expansion criteria for 3 dimensions which are linked to properties of the main driving report dimension.  I have noticed that after applying the content override the expansion criteria are replaced by a number of member only settings.

I have tried starting afresh with the report but have run into the same problem a couple of times.

To me it looks like this solution with the formatting sheet is currently a dead end.

It looks like there are a couple of bugs within the EPM add-in that cause problems with this method.  Which is a shame because it looked to be quite an elegant solution.

Regards,

Mark

[former_member186338]

Yes, the same story with row axis members - "replaced by a number of member only settings".

Same feeling: "solution with the formatting sheet is currently a dead end"!

And may be not only currently - header content override is not an easy to support feature - when report is refreshed the local member line has to be deleted, but without specific local member formula it's a question what to delete...

Hope it's clear.

B.R. Vadim

[former_member186338]

Hi Mark,

First, with local member is the best. To get the right text with the right placement - use Excel formulas.

Please, specify your requirements.

Vadim

[former_member186338]

P.S. By the way, do you have a fixed number of columns in your report?

[Former Member]

Dear Vadim,

This is a general requirement that we have for multiple similar files the number of columns will vary.

I am not happy with results of using the local member because the title is in the wrong column.  See screen shot in the original post.

Regards,

Mark

[former_member186338]

"This is a general requirement that we have for multiple similar files the number of columns will vary." - I am asking about each particular report - do you think the number of columns will be dynamic?

"I am not happy with results of using the local member because the title is in the wrong column.  See screen shot in the original post." - you are doing something incorrect And on the original screenshot I can't even understand what header you are talking about and where is the "wrong column".

Please, specify your requirements.

Vadim

P.S. Just start with very simple report....

[Former Member]

Dear Vadim

I want results similar to what I have achieved using VBA.  Where I have one row for column titles, which I am copying using Excel formulas, these should appear on every column including the dimension columns.  On the row above this should be a subsection title, in this example this is "Bezüger"

The screen shots contain two reports, one below the other with shared column axis.  The second report has been shifted down by a couple of rows to introduce a break between the reports.

Using VBA I inserted titles onto two rows, with a section title above a repeat of the original column header.

The best that I have achieved using local members resulted in a title being on the right most column, and there being no way to put title column header above the dimension columns.

I have played around with all the options for the local member but find no way to make the title appear in the left most column.

In this screen shot, the local member is disabled, because I have replaced it with the VBA solution.

I hope that the updated screen shots make this clearer to you.

Regards,

Mark

[former_member186338]

Can you also answer: "This is a general requirement that we have for multiple similar files the number of columns will vary." - I am asking about each particular report - do you think the number of columns will be dynamic?

Vadim

[Former Member]

Dear Vadim,

On the report I have been working on the columns are fixed.  But I imagine that there will be reports with dynamic column expansions which will also require subsection titles.

You said that I was doing something wrong with the local member, from the screen shot in my last post, can you see what my mistake is?

How would you expect to use local members to solve the requirement of adding titles.

Regards,

Mark

[former_member186338]

Just some simple solution for the fixed column report:

1. Don't share column axis.

2. Use static column axis

3. Use formulas in the second report to get column axis members from the first report:

Vadim