SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 sum -text datatype?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ri16
Yak Posting Veteran

64 Posts

Posted - 02/14/2008 :  11:26:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5555 Posts

Posted - 02/14/2008 :  11:30:15  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

ri16
Yak Posting Veteran

64 Posts

Posted - 02/14/2008 :  11:44:10  Show Profile  Reply with Quote
thanks for ur reply.

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

ri16
Yak Posting Veteran

64 Posts

Posted - 02/14/2008 :  11:48:06  Show Profile  Reply with Quote
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.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5555 Posts

Posted - 02/14/2008 :  11:48:14  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

ri16
Yak Posting Veteran

64 Posts

Posted - 02/14/2008 :  11:55:00  Show Profile  Reply with Quote
thanks harsh.

let me try and will let u know.

thanks a lot! for ur help
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22756 Posts

Posted - 02/15/2008 :  02:20:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Always use proper datatype to store data

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/16/2008 :  02:05:28  Show Profile  Reply with Quote
You can even cast it in SSRS using Cint(),CDbl(),... functions
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000