Page 1 of 1
Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 4:36 am
by bunchukokoy
Hi!
Does anyone know how I am gonna dispalay a specific element attribute to a cell in excel?
It's like I have an attribute for Dimension Stores which is EStablishedDate. This attribute is a string type. And I want to display on a cell the attribute of Store#1.
Does anyone know how?
Thanks!
bunchukokoy
Re: Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 4:46 am
by Alan Kirk
bunchukokoy wrote:
Does anyone know how I am gonna dispalay a specific element attribute to a cell in excel?
It's like I have an attribute for Dimension Stores which is EStablishedDate. This attribute is a string type. And I want to display on a cell the attribute of Store#1.
Check the TM1 help files for Excel worksheet functions. The function that you want is DBRA()
Re: Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 5:36 am
by bunchukokoy
Hi! Allan,
I have used the function so I have this sample =DBRA("server:store",$A$1,"EST DATE"), but it returns blank. But when I hard-code the Store into
=DBRA("server:site","106","EST DATE"), it returns the right one... Do you know what's wrong Sir?
I did reference the control objects, the cube for the attributes of this dimension and it also returned the right one, But I kinda like the one you mentioned.
Thanks!
Re: Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 5:39 am
by Alan Kirk
bunchukokoy wrote:Hi! Allan,
I have used the function so I have this sample =DBRA("server:store",$A$1,"EST DATE"), but it returns blank. But when I hard-code the Store into
=DBRA("server:site","106","EST DATE"), it returns the right one... Do you know what's wrong Sir?
My bet is that the value in cell A1 is stored as a numeric one. To force the DBRA formula to read it as a string, insert a single quote in front of it; so '106 (the quote won't be visible in Excel) rather than just 106.
Re: Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 5:54 am
by bunchukokoy
Wow!
You're right Allan.
Thanks very much Allan... You're indeed a help. Thanks!
Re: Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 6:10 am
by bskalli
You can also use DBRA("server:store",Text($A$1,"@"),"EST DATE")
Re: Display Element Attribute in Worksheet
Posted: Thu Jul 15, 2010 6:23 am
by Alan Kirk
bskalli wrote:You can also use DBRA("server:store",Text($A$1,"@"),"EST DATE")
That's correct, though I probably wouldn't; as far as possible I try to strip out any unnecessary formulas in an Excel report workbook using TM1. By themselves the calculation time can be insignificant but as more and more functions go in it can start to have a performance impact. If you have say 30 stores, that's an extra 30 function calls that Excel has to make in the process of calculating the sheet. But certainly that expression should still return the correct result.