Skip to Content

Problems with Distribution functionality since we upgraded to EPM SP29.2

Oct 17, 2017 at 06:57 PM


avatar image

We have a few distributions PDBooks and Books which we have a problem since we upgraded from EPM SP22 to SP29.2. (we are on BPC 10 NW).

In our Books we have a "Home" sheet that contain EPMContextMember formulas in some cells which have a Range attached to them.

The "Home" sheet is then hidden because we don't want to show that sheet in the distributed reports created by the distribution. In the other sheets which contain reports there are some formulas that link to the Range of the Home sheet, to show the Costcenter as a title row for example.

Since we have upgraded to EPM SP29.2 the hidden Home sheet is not created in the distributed reports, so all of our formulas in the other sheets that are link to the Range of the Home sheet show #REF.

Is there a reason why we have this issue with SP29.2 ???

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Vadim Kalinin Oct 18, 2017 at 12:22 AM

Can you create a simple test case (2 sheets, one hidden etc)?

And post detailed screenshots of the issue?

Show 10 Share
10 |10000 characters needed characters left characters exceeded

Hi, here are screen shots of my BDBook and my Book, and files created by the Distribution.

Here's the Book (Reporting sheet with the formula which link to the Range 'YR')


Here's the Hidden 'Home' sheet in the Book which contains the Range 'YR'


Here's the PDBook (Look my second comment with the picture, Don't know why but I can't add more picture here)


Here's the file created by the Distribution, 'Home' sheet is not there anymore, so the Range 'YR' doesn't exist and that's why we see #REF. ..................(Look my second comment with the picture)


I then Unhide the 'Home' sheet in the Book and re-run the Distribution.

Now everything is fine, Range is ok.

..................(Look my second comment with the picture)


But the client will see the 'Home' sheet which contain all our formulas and configurations which is not nice.

With EPM SP22 or even with SP25 we didn't have to unhide the sheet 'Home', do you know what happening here with EPM SP29.2 ?



OK, it's definitely NOT a simple test case :)

File contain macros etc...! Too many things preventing correct analysis...

Asking again:

Create template with 2 sheets - with very simple reports and post tests!



Here's the file created by the Distribution

...Look my third comment for more picture.....


Sorry, but do you understand what I am talking about? Simple test case!

Without any macros!


Then re-run the Distribution, Here's the file created by the Distribution:

Now everything is OK.

I hope that it is not to much confusing, I was not able to attached all pictures in the same comment.



Have to repeat again - it's a waste of time to analyze real report. Can be number of reasons for the mentioned behavior!

Test simple case to investigate the issue - effect of hidden sheet!


Hi Vadim, I've built a new Book with a simple EPM-query including a hidden sheet with Context-member formula. I've also built a new PDBook from scratch and run the distribution....unfortunately for me it works.

So the conclusion is, there is something in my Book on the hidden sheet that make that sheet unavailable for the Distribution process. I can't find out !!!


That was my initial idea! You have macros in your code. Looks like something wrong with VBA code....


I'm trying different things to find out the problem, and I already tried to copy the VBA in the new Book, but the new Book still working with Distribution. I also tried to remove some formulas in my broken Book without any success. If you have other idea, let me know...thanks.


Sorry, but without detailed investigation of your workbook it's not possible to help!

Try to recreate everything step by step...