on 07-20-2016 3:00 AM
Hi Friends,
Env 10.1/7.4
I have a requirement to create a report which has lot of calculations. These calculations are kind of repetitive for every set of profit center nodes.
currently we have 4 top nodes of the profit centers
sample layout
Profit center node 1 (we will have 5 sets)
actual Plan
CY PY LM1 LM2 CY PY LM3 LM4
GL Account
LM - local member (calculation)
These 4 local members would repeat for 4 sets of profit center nodes and the parent node, In total I am having around 20 local members.
This report needs expansion on GL Account, so i have created local members based on position (as columns will not need any expansion)
like LM1= sum(epmposition(1),-epmposition(2)) ... etc and attached it the column axis at position(3) , 4 etc
this requires careful detailed work.
I was checking if there is an easier and elegant way to design a report.
Thanks for your time
Ed
epmposition is not bad option...
Vadim
P.S. Or you can use absolute column reference
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks All for your replies
I have attached a copy of the report.
JP-
CY and PY are not the members of the dimension. I just used it to show current year and prior year fiscal period. If i attach a local member to this time, it would disappear if we change the context want to view the report for a different time period.
Andy-
could you explain how could I use member sorting and grouping feature in my current scenario.
Is there a limitation on the number of local formula in any report? We know if would affect performance,
but we need calculations in the reports. other alternative to local members is member formulas, not sure if that is a better alternative.
Thanks again for your time
Ed.
My formula is very simple:
INDIRECT(ADDRESS(ROW(),COLUMN()-OFFSET))
Will reference the cell OFFSET cells left to the cell with local member!
It's just basic Excel
For example: If the local member is in G8
Then: INDIRECT(ADDRESS(ROW(),COLUMN()-1)) will reference F8
INDIRECT(ADDRESS(ROW(),COLUMN()-2)) will reference E8
Sorry - corrected "," instead of ";"
Thanks Vadim, I am using the Indirect formula that you have mentioned. only problem with it, the first local member is attached to time dimension, it will repeat for every set of data.. I just need to selectively hide the unwanted local members in my report, ones in purple
I have noticed, if you use pure excel formulas in the reports, when we expand it does not calculate the expansion, [local member recog not checked on]
and if we create a local member with excel calculation, it attaches itself with the dimension combination, and any change in the context will make the local member disappear.
[local member recog checked on]
Ed
"I just need to selectively hide the unwanted local members in my report, ones in purple" - first of all you have to CLEARLY explain your requirements!
I can help, but I have to know what do you want
"and if we create a local member with excel calculation, it attaches itself with the dimension combination, and any change in the context will make the local member disappear." - wrong!
Please start again and provide the logic of calculations!
Hi,
Have you tried using the option " Use Excel cell references" in the local member??? Tick this option in the local member and see whenever the local member gets repeated in the report the Excel formulas are adjusted as per the references, typically like Excel functionality!
Attached the screenshot of the option!
And to hide the unwanted local members you can select the column and right click-->Hide, Hide the columns using normal excel functionality....I will not unhide upon Refresh.
Hope this helps.....
Regards,
JP
I could understand Vadim, the column are not arranged well. But this is how the client has been publishing his data, from legacy and they want to use the same format in bpc reports too
I could not change the ordering of the columns, but simplified the "if" statements.
The values or the % they are looking for is, in comparison to the prior year.
I was able to use the indirect formula that you have mentioned, attached it to the column axis, and did some work around hiding extra calculations.
Its not an elegant solution, but cant reorder the columns.
Thanks again Vadim and Jp for your inputs. Always valuable.
Ed.
Hi Ed,
Is CY,PY members of any dimension? If Yes..then you can add the local member after the PY member instead of position.
What are the calculations done in the local member? 20 local members are too much too handle in a report....I don't think 20 members are required!
Please share the screenshot of the report structure to understand carefully.......
Regards,
JP
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ed,
You can try using the member sorting and grouping function in the member selector column axis.
Andy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.