How to make SSAS Dimension attribute data type as Numeric in Excel

11
votes

I need to your help to change the dimension attribute data type in Excel sheet when connecting cube via excel and I have defined a column as "FLOAT" in Data warehouse and "DOUBLE" in Cube,but that attribute is always displaying as string in Excel sheet when I connect the cube via Excel sheet.

and I didn't define the name column property as well because name column will always support string datatype.

Please find the below example from Adventure works cube to understand my scenario.

I have taken "Weight" attribute from Product dimension and the key column and Value column of this attribute has "Double" datatype and Name column has "Wchar" datatype in the adventure works.

Because of Name column property , Weight attribute is showing as string in Excel sheet.

So I have removed the Name column definition to "Weight" attribute and I kept the Value column and Key column property as "Double" , but still in excel "Weight" attribute is displaying as "String".

Can anyone please help to make this dimension attribute as "double" in excel sheet as well?

Please find the below screenshots for reference.

Weight- Dimension attribute property setting screenshot

Weight Datatype in Excel sheet

Thank you in advance for you help. Bhavani R

2
sql-serverexcelssas
Try creating a new member property which is a property of Weight add it as a Show Properties in Report. – GregGalloway
Thanks GregGalloway for your answer. I am able to see the "Weight" attribute as Double value in excel after creating new member property using Weight attribute. Can you please confirm other than this no other option or property settings to see the "Weight" attribute as Double? – Bhavani Rajakumar
other than a measure I don’t know of other options. Well, I suppose you could use CUBEVALUE formulas and have the formula convert the string to a number – GregGalloway
Thanks GregGalloway for your answer. – Bhavani Rajakumar

2 Answers

0
votes

You can use MemberValue to get the value of weight as numeric.

CREATE MEMBER your_new_member AS
your_old_member.currentmember.MemberValue
0
votes

Expanding @GregGalloway comments as an answer:

Create a Named Calculation, for our sample, Weight Numeric

enter image description here

Then edit the Dimension and add an Attribute Relationship between Weight Numeric and Weight

enter image description here

Now, this column is available as a property in Excel Pivot table

enter image description here

Voila, this new column in Excel is now really a Numeric column. The formatting options in Excel will really format this column.

Related SO Query: SSAS, dimension numeric value filtering