can i use IF statment on DBRW excel formula

[erin]

Hi All
i when created dynamic report for column and report made has 2 cube.
on excel cell i writhe the formula as below:
= IF(M$14<>N$14,DBRW($J$9,$M$11,$O$11,$I21,$H21,N$15),DBRW($M$9,$M$11,$O$11,$I21,$H21,N$16,N$17))
the formula is work on view report. but when i change value on report and save ,
the data can't be save.
how can i solve the problem.

[bplaia]

are you trying to save the data change back to TM1 or simply read the value from TM1 and put it in the excel sheet?

[Martin Ryan]

You can use IF's on DBRWs only if you want read only information. If you want to type the value over the DBRW formula and send it back to TM1 then it will not work. This is because of how the Cellupdate event is handled in VBA.

The precedent cells (e.g. cell $M$9) can have IF formulas in them, which could be a way around the problem - though probably not in your case because I see the two cubes have a different number of dimensions in them.

Martin

[erin]

hi Martin,
many thanks.
yes, in my case the two cubes have a different number of dimensions.
so ... you meaning , the way is not work ?

You can use IF's on DBRWs only if you want read only information. If you want to type the value over the DBRW formula and send it back to TM1 then it will not work. This is because of how the Cellupdate event is handled in VBA.

The precedent cells (e.g. cell $M$9) can have IF formulas in them, which could be a way around the problem - though probably not in your case because I see the two cubes have a different number of dimensions in them.

Martin

[erin]

yes,
i try to save data on excel hseet also change the data.
simple read is okay... but store is not work...

are you trying to save the data change back to TM1 or simply read the value from TM1 and put it in the excel sheet?

[bplaia]

yes,
i try to save data on excel hseet also change the data.
simple read is okay... but store is not work...

are you trying to save the data change back to TM1 or simply read the value from TM1 and put it in the excel sheet?

DBRW won't write back to TM1, you have to use a DBS or DBSW formula in order to save data on TM1

Here is the IBM documentation
http://pic.dhe.ibm.com/infocenter/ctm1/ ... k_dbs.html

[tomok]

DBRW won't write back to TM1, you have to use a DBS or DBSW formula in order to save data on TM1

Incorrect. You can write back to TM1 over a DBRW formula. This has been a key feature umpteen years. What you CANNOT do is write back to TM1 in a DBRW when the cell has more than just a plain vanilla DBRW formula (like an IF statement or another DBR lookup or DBRA).

[Martin Ryan]

yes, in my case the two cubes have a different number of dimensions.
so ... you meaning , the way is not work ?

Yep, that's my meaning. You cannot do what you want to do this way. I think you'll have to create two separate reports, one for cube A and one for cube B.

i try to save data on excel hseet also change the data.

I think you mean that you tried hitting Save in Excel with the intention of saving the data in TM1? If that's what you mean, saving in Excel does nothing in TM1. Saving an Excel workbook will save all the Excel formulas (DBRWs etc) but it does not save the data in TM1. When you type a value into TM1 - regardless of whether that's directly in the cube or via a DBRW formula in Excl - the value is automatically saved in TM1. You don't need to do anything else for it to be retained (to be pedantic, the exception is if the server crashes and logging is turned off, but that's not relevant to your case).

Martin

[bplaia]

DBRW won't write back to TM1, you have to use a DBS or DBSW formula in order to save data on TM1

Incorrect. You can write back to TM1 over a DBRW formula. This has been a key feature umpteen years. What you CANNOT do is write back to TM1 in a DBRW when the cell has more than just a plain vanilla DBRW formula (like an IF statement or another DBR lookup or DBRA).

my apologies for the incorrect information and thanks tomok for letting me in on that secret

[Darkhorse]

turn off calc before save