Problems with EVDRE()

[Former Member]

Hi,

I would like to create an input schedule which shows 14 columns: First the total forecast of year X, then the total budget of year X, and finally all the basemembers of the selection period in the CV (for example 12 periods of budget for year X+1).

-> I can realise this if I switch of the column expansion and just code my desired time and category dimensions in the 14 columns. However, we do not want to hard-code this in the report and want the system to dynamically determine the 12 periods (for example if only 2009.Q1 is selected in the CV than it should only display 3 columns instead of 12. The first two columns however always have to be displayed as one column (so no expansion), and with year X, meaning also dependent on what is selected in the CV.

Is this possible?

D

[Former Member · Accepted solution]

Yes, I thin kit is possible. It may take multiple evdre's and some creativity, but it sounds do-able. Perhaps the evdre's would be, an expansion of rows only for row ranges, an evdre with fixed columns and the expanded row region for forecast, one for budget, and last one that expands the columns based on teh CV.

It seems like a lot of expansions and evdre's, but it will be an efficient process since you are norrowing the scope of each evdre.

Hope this helps.

[Former Member · Accepted solution]

I actually was thinking to create one EVDRE end start with 12 periods of year X+1 and then include 2 new colums to calculate the forecast and budget of year X and simply get them with EVGTS function. The first two columns do not have to be ready for input, solely for reporting purposes ...

shouldn't that be more simple rather creating multiple EVDRE and try to report as one with hiding columns, ...

D

[Former Member · Accepted solution]

I would NOT suggest blending EVGTS and EVDRE. The ECGTS is a single call by cell, where as the EVDRE is a block call. I would stick to multiple evdre's; remember, therer are multiple ways to split a single evdre for pulling data. I was trying to make it easy for the month to QTR to year expansion. You may also lock areas and use the dynamic EVTIM function in columns as well.

There are so many different approaches that it is to advise without seeing the template.(at least for me)

Thanks

[Former Member · Accepted solution]

Hi Peter,

What do you mean with 'use a lock area' ... I'm not familiar with that, I guess. Does it allow limiting the expansion settings within a column or row dimension to for example only 12 columns instead of 14, and thus fixing the first 2 columns ... because that is what I'm looking for. To give you a concrete example

I have a report 2 row dimensions (customer and account) and one column dimension (time). the time columns should be as following:

BUD 2009.TOTAL / FQ 2009.TOTAL / BUD 2010.JAN / BUD 2010.FEB/ ... /BUD 2010.DEC

I would like to apply expansion on time dimension but only for the last 12 columns (representing the budget for next year) using memberset BAS. For the first two, there should be no expansion.

So If I select 2011.TOTAL in the CV my 12 budget periods will have 2011.JAN ... 2011.DEC and my first two columns have BUD 2010.TOTAL and FQ 2010.TOTAL. If I can limit the expansion settings to only the last 12 columns, my problem is solved ...

is this possible with lock area's?

[Former Member · Accepted solution]

You could in a separate cell build the pieces of you time memberset, ie: if D20 is the cell in the PageKeyRange for the currently selected time, in another cell, F20 write something like:

=LEFT($D$20,4)&".TOTAL"&"|"&LEFT($D$20,4)&".TOTAL"&","&"BAS("&LEFT($D$20,4)+1&MID($D$20,5,LEN($D$20)-4)&")"

Which result in the following in cell F20:

2007.TOTAL|2007.TOTAL,BAS(2008.TOTAL)

Then refer use =F20 in your Time memberset cell. In the memberset for your Category use:

FORECAST|BUDGET

You need to make sure you have 2 sets of column keys:

=EVRNG(J24:K25,L24:X25) which appears as Sheet1!$J$24:$K$25,Sheet1!$L$24:$X$25

As you change the selected time, your time memberset will change so you need to set the workbook options to expand after current view change.

At least that's one way, hopefully you will find something that works for you.

[Former Member · Accepted solution]

Sorry, didn't see your last respond while typing in mine. You've combined Category/Time together in the Time members instead of keeping them separate?

Then you only need to find the right excel string parsing commands to build your time memberset as

BUD 2009.TOTAL,FQ 2009.TOTAL,BAS($D$20)

The expansion will put BUD 2009.TOTAL in the first column, FQ 2009.TOTAL in the second column and the BAS level members of the currently selected time member in the following columns be it 12 or 3.

You only need 1 column key then. It is still dynamic, does not mix old evgets with evdre (though I've successfully done that to meet some odd situations), and only 1 evdre.

[Former Member · Accepted solution]

Thanks Sara!

you solved my issue ... A new world of opportunities came open