cancel
Showing results for 
Search instead for 
Did you mean: 

NEED ADVICE about Inserting a custom formula column in EXPANSION memberset

Former Member
0 Kudos

Dear all:

I am asked to create a report with TIME(col), ACCOUNT(col), PRODUCT(row), where ACCOUNT stores Beginning Inventory Amt, COS, etc.

The problem I am having is that the user requests to have a place to show COS ratio column. COS ratio value is derived from ACCOUNT COS and ACCOUNT NETSALES of my ACCOUNT MEMBERSET.

I tried various ways but was unable to keep the formula in EVDRE so this COS ratio column's formula goes with the rest of the expansion.

The result I want is something like below, where NETSALES and COS are my dimension members and RATIO is Excel formula STDCOST/COS... As the report expands by different TIME hierachy (i.e. 2008.total, 2008.h2, 2008.q3), RATIO is expanded accordingly.

Is this doable in EVDRE?

2008.10 2008.11 2008.12

NETSALES COS RATIO NETSALES COS RATIO NETSALES COS RATIO

PRODUCT1

PRODUCT2

PRODUCT3

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Brian,

I think it is doable, if you time expansion is not dynamic.

You should restrict the time dimension in the following way: 2008.JAN | 2008.FEB | 2008.MAR and so on.

Then you have to make the right colkeyrange restriction. For example: =EVRNG(A25:B26,D25:E26,G25:H26). I have have not chosen the C and F columns. In these columns you should write a formula and this formula will expand in a proper way...

P.S. Why don't you want to use dim logic?

Former Member
0 Kudos

Rafael:

Thanks for the reply.

In response of your reply:

1.

I tried to make my Time Dim static and manipulated the colkeyrange. What I did was under ACCOUNT memberset, I manually typed in "NETSALES|COS", with the "bar" symbol. However, after "Expand", it expanded NETSALES across all months, then expand all the emply columns which I reserved, then expanded COS across all months. In other words, segregating members with the bar symbol will reserve some column spaces but it does not display correctly the report in its original order .

2.

I am fairly new to BPC reporting, I am sorry if I seem a bit unclear or confused...When you mentioned Dim Logic, do you mean using 'formula' column under Dimension Member List, or using script logic? Because COS ratio is a calculated value that can be easily expressed in Excel formula, I prefer not to create additional records to store COS ratio. I found out a huge difference in terms of server performance when I have fewer records in the SQL DB...

I have thought about a work-around by referring the whole expansion area to a different worksheet, or different columns of the same worksheet, and insert my customized column there. For instance, I will hide my expansion columns (L, M, N), and have O=L, P=M, Q=customized column, R=N. I will let you know how that goes.

Bottom line is, it is cleaner to write the report within BPC functionality. In fact, I found out a document under BPC Report Help file about "Including blank rows or columns" when using EvDRE, but I cannot duplicate the results described in that document...

Thanks!

Brian

Former Member
0 Kudos

1. Here is an example of expand range:

PARAMETER	EXPANSION 1	EXPANSION 2	EXPANSION 3
ExpandIn	COL	COL	ROW
Dimension	G_TIME	G_MOVEMENT	G_ACTIVITYTYPE
MemberSet	2008.TOTAL| 2009.TOTAL|2010.TOTAL	17010000,N_V_MOVEMENT	060107,060102
BeforeRange			
AfterRange			
Suppress			
Insert

It is a copy from excel from my report(it looks confusing, but I hope you understand it ))). This report works fine.

2. If you use dim logic (that is 'formula' column under Dimension Member List) the values are not saved to the DB. They are calculated on the fly.

Former Member
0 Kudos

Rafael:

Actually, the best solution I have come up with is to add COS Ratio in Dim Member List and define a formula, and everything just works beautifully!

I also noted that formula produces records not only on the fly but also in exported files.

Thank you very much for your help!

Brian

Answers (4)

Answers (4)

Former Member
0 Kudos

Please accept my apology; I wasnu2019t sure by your comment because I didnu2019t think I provided great substance. Forums, like email are so easy to misunderstand.

Unfortunately, I donu2019t see a lot of appreciation in this forum.

Thank you for correcting me and following up.

Former Member
0 Kudos

Sarcasm?

You can create a hundred different evdre templates and still not know everything about evdre and its functionality keeps growing. Nothing beats experience here. Iu2019m not able/allowed to provide examples, perhaps one of your colleagues? Or another forum participant?

1) The online help is good and the Best Practice guide is also very good. I canu2019t send the evdre guide and it is not published by SAP. You can get the BPC for Office Useru2019s Guide;

Support Portal u2013 Release & Upgrade Info u2013 Install & Upgrade Guides u2013 SAP BPC u2013 (Select your BPC release)

Office User Guide

https://websmp101.sap-ag.de/instguidescpm-bpc

Grab the BPC 7 version as well, it has some better samples.

To fully capitalize on the fullness of evdre you also need an expert level knowledge of excel, (and I donu2019t mean lots of experience). Evdre is the combination of BPC functionality and excel working in union with each other. VBA is also important in very complex situations.

2&3) This is one approach. One of the nice features in evdre is the ability to insert excel formulas within a data range. You may need to adjust your row/col key range to address your needs.

There is nothing really wrong with using dimension logic. Itu2019s important to understand you will take a performance hit somewhere and you should test to determine if the performance hit is within an acceptable range. I have one client with a few hundred accounts with formulas and there application works great and the performance is not to terrible.

Also look at the following;

How to Maximize Advanced Formatting for BPC 5.x

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70573b01-657a-2b10-a2b2-ec54bb0a...

SAP Business Planning and Consolidation 7.0 - Excel Reporting Using EVDRE

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/30d193bd-26e8-2a10-719c-cea716a1...

Best Practices for Reporting within SAP Business Planning and Consolidations 5.0

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/2006e603-ac28-2a10-e589-db05ca52...

Former Member
0 Kudos

David:

Sarcasm...? I meant with appreciation and compliment.

Your comments (along with other forum members) are really helpful, and I don't take it for granted that someone is willing to share his/her knowledge (and what's more precious) and experience.

Thank you for sharing!

Brian

Former Member
0 Kudos

One of the many great things about evdre is it's ability to easily do exactly what you are looking for. I don't mean to sound Condescending, I know evdre can be difficult to grasp at first.

Do you have the evdre reference guide? You can find it in this forum. (The online help is almost as good.) Let me know if you can't find it.

You should also find the best practice for reports. Dimension logic will impact you performance and may not the best approach for your needs.

Former Member
0 Kudos

David:

I am so glad that you are generous to give some pin-points. I just finished two e-learning classes about BPC and just practiced reporting for about 6-8 weeks. I did find it difficult to grasp a few functions and concepts within BPC mainly because of the lack of documentation, examples, and reference resources. But knowing there are a lot of references (besides the poorly documented Help file) residing on SDN, it will help me grow much faster on my learning curve.

A few things:

1. Can you show me where I can find the reference guide? I only found "Best Practice" articles...

2. When you said there were other ways to approach my task, did you mean something below:

3.9 Data dump sheet & Excel-based calculations

In some cases, for performance reasons it may be an option to set up one big

data dump sheet that is generated by one or more EvDRE functions. For

example, if you have a workbook with ten sheets and all the sheets retrieve

data from the same accounts, but for different entities per sheet. In this

situation, instead of making ten EvDREs in the whole workbook, you can dump

all your data in one hidden sheet and build your reports with links to the data

dump sheet.

Also setting up easy KPIs like u201CAccount1/Account2u201D via Excel formulas can

increase the performance of your reports as it is faster than MDX queries.

However, when building a report this way, keep in mind to choose a unified

approach throughout the whole report, because maintaining these data dump

reports is a bit harder then maintaining a single report that retrieves its own

data.

I did notice a performance issue with using formula within Dim Logic. However, to provide my Finance manager a quarterly, semi-annual, and annual inventory flow report that doesn't require frequent refreshing, I weighted the benefit of flexibility over performance. In other words, when my database records increase tremendously amount few years later down the road, I might have to make necessary adjustments.

Thank you!

Brian

Former Member
0 Kudos

Hi Brain,

I think defining after range option for account expansion of evdre will solve your problem.

After range will automatically insert an additional col and formula you defined after each and every account exp...

It will look like

-


2008.10--


|2008.11
|2008.12
--


-


NETSALES COS RATIO|NETSALES COS RATIO|NETSALES COS RATIO

Hope this may Help,

Kranthi