Dynamically adding formua to column

[Former Member]

Hi

I have 2 expansions in ROWS - Account, Project

1 Expansion in COLUMN - TIME

and a third column that has a forumula in first row of second column

Time F1 (Foruml Column)

Account Project

I want the formula to be copied as after i execute the EXPAND ALL. at the moment i get 100 rows but formula is only in the first row.

Please tell me how to copy formula so that if there are 100 rows after execution, 100 formulas are filled correspondingly.

Regards

[Former Member · Accepted solution]

Hi Merc

To get a formula to copy down it needs to be "inside" your evdre report.

So I with your example you currently have a straight forward expansion with the last thing being a formula. If you adjust your retrieval so that there is a second retrieve section and you can have the formula in the blank in the middle.

For instance your columns will now be "2010.TOTAL,2012.TOTAL|" which might be in the range "I10:J10,L10:M10"

When you run this you have a blank column of K were your formula goes and column L and M will all hide as there is no data for them

Regards

Jason

[Former Member]

Hi,

If you have a content in the first row, it should have been copied till the last row; since you have a row expansion. However, this will happen only if the column is included in the colkeyrange.

I understand that this is just a formula column, and doest not have any member as the key, but still you can include this in the colkeyrange.

Hope this helps.

[Former Member]

Hi Guys,

Many thanks, but I am confused. See below what I have in

I tried to enter the Formula column (Column AR) in ColKeyRange, it did not work, no copy.

I also tried NOEXPAND memberset inrows, it did not work. even in Col, did not work.

AM             AN                      AO                    AP                           AQ

                                      2011.TOTAL         2011.JAN                      FORMULA

Account   Project 

Dont know how will it copy formula.

EVDRE:OK	APPM_NIBR
PageKeyRange	    $E$4:$E$23
ColKeyRange	           !$AD$43:$AP$43
RowKeyRange	   $K$45:$L$49
CellKeyRange	
GetOnlyRange	
FormatRange	          $B$26:$G$37
OptionRange	        HideColKeys,HideRowKeys
						

PARAMETER	                              EXPANSION 1	               EXPANSION 2	                   EXPANSION 3
ExpandIn                                        	ROW	                               ROW                             	COL
Dimension	                                   D_SITE	                             D_ACCT	                         TIME
MemberSet	                                  DEP	                               DEP,SELF	                    BAS,SELF
BeforeRange			
AfterRange			
Suppress	                                                 Y	                                       Y	
Insert			

Edited by: MercAMG on Sep 7, 2011 1:46 PM

[Former Member]

Hi,

Why does your colkeyrange look like !$AD$43:$AP$43. I mean there is a ! in the beginning.

Are you using EVRNG function?

Can you include AQ in the colkeyrange?

[Former Member]

Issue is resolved to what Jason has said.

Looks good now. Thanks Nillanjan.

[Former Member]

Hi,

You select the cell where the formula is working and drag it down manually till the 100th row .

It will copy the formula to all the cells till the end.And then when you refresh your formula should work along with

the entries in the report .Hope this helps.

Sanjeev