TM1 Forum

To set the scene, I'm not an expert user or administrator!

I'm setting up a new cube and trying to maintain the attributes in my dimensions using formulae in the xdi file where I update the elements of the dimension. All attributes load ok except for number formats. (The attribute pre-exists - not sure if this is something to do with it?)

I'm using the DBSA fuction, written as follows:

=DBSA("cell reference for Number format","Cell reference for Server & Dimension name","Cell reference for element","Cell reference for attribute")

In writing the above, the cell references are as follows:

Number format - shown as #,##0;(#,##0);-
Cell reference for attribute: The word "Format" - as this is how it appears when you go through Server explorer > right click on the dimension and then go into the attributes editor...

If I change this to load into a different attribute (eg long descoption) it works ok, so I can only assume it's something to do with the reference I'm using for format... Any ideas?

For info, I use TM1 9.5.1

Thanks

Hi mifingy,

I've not tried to do what you are doing but I suspect that it's not possible to update the format attribute using a DBSA. This would be because it's a system attribute that pre-exists in the dimension.
Alternative possabilities.
TM1 doesn't like the format string you are using, try putting it directly into the attribute editor, if TM1's happy with that then it's not the problem.
Maybe you can only use DBSA if other attributes already exist in the dimension (which it sounds like you have) or it only works if you have populated one element manually first.

If you want some form of bulk update then TIs probably the right route for you, except you might struggle to write it given your experience level.

Please let the forum know if you find anything out!

Cheers,

Steve

Steve Rowe wrote:I've not tried to do what you are doing but I suspect that it's not possible to update the format attribute using a DBSA. This would be because it's a system attribute that pre-exists in the dimension.
Alternative possabilities.

Just for kicks I tried writing to the format attribute of a dimension using DBSA and it worked fine. I even used the exact same format code that the OP used. Problem would have to be that one of the cell references he is referring to does not actually contain what he says it does.

Just for kicks I tried writing to the format attribute of a dimension using DBSA and it worked fine. I even used the exact same format code that the OP used. Problem would have to be that one of the cell references he is referring to does not actually contain what he says it does.

A little excel tip, given the OP said they were not an expert user. If you go into the formula bar in excel and highlight a single cell reference and hit F9 it will replace the reference in the cell with the value. For example a formula like =DBR(a1,a2,a3,a4) will look like =DBR("myserver",a2,a3,a4) if you highlight the a1 reference and hit f9. You can easily highlight them by double clicking in the formula bar. Once you've finished hit {esc} and the formula will revert back to its original state.

Steve

stex2727 wrote:

Just for kicks I tried writing to the format attribute of a dimension using DBSA and it worked fine. I even used the exact same format code that the OP used. Problem would have to be that one of the cell references he is referring to does not actually contain what he says it does.

A little excel tip, given the OP said they were not an expert user. If you go into the formula bar in excel and highlight a single cell reference and hit F9 it will replace the reference in the cell with the value. For example a formula like =DBR(a1,a2,a3,a4) will look like =DBR("myserver",a2,a3,a4) if you highlight the a1 reference and hit f9. You can easily highlight them by double clicking in the formula bar. Once you've finished hit {esc} and the formula will revert back to its original state.

Good tip, but even easier... use the Formula Tracer in TM1 Tools which will not only show what the arguments are, but highlight invalid ones.

Thanks for all the replies. I had a bit more of a play with this and appear to have cracked it

It appears that TM1 didn't like the format I was trying to load. In case anybody else wants to do this, see below:

I tried to load "#,##0;(#,##0);-"

In actual fact, TM1 wanted me to load "c:#,##0;(#,##0);-" - it appears that when you copy the Excel syntax into the custom number field, by the time you click OK TM1 adds the c: to the beginning of the format. There are different pre-fixes depending on what you're trying to do - I've just copied in the typical number formats I like to use and then added these to a list that can be selected using Excel validation fuction to make it easy to select and maintain in the dimension xdi field. Hope this is of use to somebody else.

Cheers