Maintain datatype for reporting services from OLAP

I'm trying to extract data from my OLAP and present it using reporting services. The problem is that all the values extracted from the OLAP seems converted into string instead of keeping their original type. Am I doing something wrong in the setting? Please let me know how can I check the data type in reporting services. Thanks!

I haven't seen this conversion. The datatype that is set in the properties for the dimension attribute is what I see in my report. Check in Analysis Services for the attribute properties datatype that is set there and verify that something that you expect as a number is defined as a number.

There isn't a property in Reporting Services that tells you the data type, but you can check to see if something is numeric by using the ISNUMERIC(Field) to determine if the field returned in the MDX is seen by RS is a number.

You can also convert the field to what ever datatype you need in Reporting Services.

David

|||Dear David,

I would like to try checking up the attribute properties datatype. However, I can't find anywhere that I can do this in the SQL Server Management Studio. Could you please give me a guide for that?

Regards,
Alex|||

Hi Alex,

Unfortunately you can't see them in SS Management Studio, but you can via BI Studio, you will have to run BI studio and if you don't have a project choose File/Open and then Analysis Services Database. Click the dimension, and then look at the properties for the Attribute Heirarchy, go to the Source/Key Column and click the ... This will show you the data type that is listed for the source column.

This should match the column that is in your DSV that is created for the project.

David

|||Hi David,

Thanks for your speedy response first. Actually, I have done couples of time of what you've mentioned. I am certainly sure that BI studio show me the datatype is what I expected. However, once I get into the reporting services. All of them becomes string datatype. I need to use cint and other function to convert them back to their supposed datatype. Is there anything wrong with my MDX statement? I just use the drap & drag approach to create the DataSet for reporting services.

Regards,
Alex|||Hi David,

I've just done a few more tests. I found that the datatype are maintained when it comes to the reporting services. I use the IsNumeric and IsDate functions to check for this. However, the problem is even they're in the correct type. It seems that the sorting and formating function are not handling them well. First, I would like to order by an integer value field and I got the list like this. 1, 10, 2, 20, 3, 30, 4 and so on. Second, if I change the format code of a date field, it doesn't change at all. I do this by right click on the textbox containing the date and choose properties -> format tab and input d into the format code field. Do you have any idea about this? Thanks!

Regards,
Alex