on 07-17-2013 11:39 AM
I build reports in different workbooks.
The problem is that if in one sheet user change the context, EPM change it also in all Workbooks/Sheets!
It's not good, because I have INPUT reports that need to be only with specific category, but if user change it in different report, he will input number on incorrect category!
Actually I fixed it with dimension overide, but in different reports the user will confused if he has a lot of opened reports.
What can i do?
I tried to use options->Context Options and Lock the dimension on context level worksheet and it's not good for me because I want the user will change dimension member but I wnat it will take effect only in it's worksheet.
Thanks for help!
My EPM Version: 13 Patch 3
BPC 10
Hi Nir,
this problem is pretty much valid for a plenty of users.
I usually solve this issue with a certain report design. It's right, that the context will change for all workbooks if a user changes the selection. But, the important thing is, you don't have to use the context. There are several ways to to avoid the template using the context. One typical way is to work with EPM commands in combination with cell references.
A possible example:
A. Put all dimensions, not used in rows or columns in the page axis and show it in the report.
B. Just imagine, you are using the category dimension in your columns and you don't want to have it changed by different users changing the context. There come several commands along with the EPM to control your selections. In your case the command EPMSelectMember() would be very useful. The advantage of this command is, it's independent from context.
Procedure:
1. In your column the formula for the category dimension would look like EPMOlapMemberO("[CATEGORY].[].[<your ID>]";"";"<your ID>";"";"000").
2. Enter the EPMSelectMember() in a certain cell, lets say H4.
3. Change your EPMOlapMemberO() for the CATEGORY in the columns to EPMOlapMemberO(H4;"[CATEGORY].[].[<your ID>]";"";"<your ID>";"";"000").
4. Create a dimension override for CATEGORY with the value of H4 (EPMDimensionOverride) in a cell of your choice.
5. Set your workbook/worksheet to be refreshed on open - you can do this in the sheet options of your EPM ribbon.
Result:
The workbook/worksheet will be refreshed on open with the category shown in the cell H4 (EPMSelectMember()-Formula), independent from context.
Another advantage of this solution is, the user can change the selection by double click on the cell H4 and select the desired member (member selector). After refresh, the report will show the desired selection.
Hope, this will help you,
Karsten
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks a lot. your reply was very helpful.
we are now facing another problem:
We used many dynamic filtering on properties in the dimension that is now being overriden. this removes all the dynamic filters. is there a way to restore the dynamic filter or maybe create it manually after the dimenssion override? (we used OFFSET MEMBER, MEMBER AND DESCENDANTS, DESCENDANTS UP TO LEVEL 2, etc.)
Hey Nir,
yes, you can do this, but not within the BPC standard.
Please remember, that EPMDimensionOverride() will override your dimension with the given values. There's no way to filter within the EPMDimensionOverride() formula. That means, you will need your values for the override already filtered.
In your case I would create a hidden report, which will show the values for dimension to be overridden and the properties you wanna filter on. You can achieve this by a simple report, showing the relevant CATEGORY members, referring to the EPMSelectMember() formula in H4, and using a local member which will show the relevant properties. Within this local member you can use the EPMMemberProperty() formula to retieve all values for a certain property. No you have all members from CATEGORY with the corresponding properties. Now, you can use Excel formulas or VBA to filter all members on the properties and write them into the value field for the dimension override.
The result should be a report filtered by properties by using the selection from EPMSelectMember().
Please consider, that the format for the override has to be MEMBER1,MEMBER2,MEMBER3,...
Hope, this will help you,
Karsten
Hey all,
We found way to deal with it perfectly.
We use EPMContextMember in the menu cells that we created for the user.
The menu for user is images with macros, and we have Refresh and SaveData.
How it's work:
For example I set EPMContextMember in cell cell(5,"H").
In VB I use the EVENT: before_refresh
Inside it in I use function setContextMember of the object EPMAddInAutomation:
obj.setContextMember connName, dimName, cell(5,"H).Value. (this is the magic)
What it does:
It's overrides the context that changed from another workbook and leaves the current one.
I used it also in saveData macro.
We also have BPF, so dimensions that came from BPF need to open correctly. We overcome it by create counter that when workbook is open its 0 and I used if the Sub:
if counter > 0 then setContextMember. end if. counter = counter +1
It's work perfectly!
Thanks for all
Hi,
another way might be to use the context lock which can be set per worksheet.
Regards,
Arnold
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nir,
First of all context is applicable for all the opened templates. You had the option of locking the context at worksheet level. However, you said that this is not acceptable. The only remaining solution would be to get the dimension in page axis.
Hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nir,
Please move the dimensions which are not in row or column axis to page axis. That will resolve the issue.
Regards,
Kalyan.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.