sum -text datatype? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

sum -text datatype?

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-14 : 11:26:17

I have to create Sum() in SSRS for my report. My field data type is TEXT and i can not create sum function in ssrs. I can not make change to data type since it is linked table in access database. Is there any way to sum text data type???.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-14 : 11:30:15

You can create a view where you can convert the field in question to numeric and in the SSRS refer to this view instead of linked table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-14 : 11:44:10

thanks for ur reply.

can u give me some hint how to create view and how to use that convert function?

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-14 : 11:48:06

actually, i have some data which are number. however, in access 97 thousands numbers are defined as text data type. SSRS was not converting text datatype to number in sum function. So, i converted text data type to int. and then Sum() thousands numbers.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-14 : 11:48:14

Create View SomeView
as
Select col1, col2, cast(ProblemColumn as Numeric(15,6)) as ProblemColumn
from SomeTable
GO

Note: Numeric is an assumed data type. You can change it as per your needs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-14 : 11:55:00

thanks harsh.

let me try and will let u know.

thanks a lot! for ur help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-15 : 02:20:20

Always use proper datatype to store data

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 02:05:28

You can even cast it in SSRS using Cint(),CDbl(),... functions