how to convert in the layout to numeric

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

Development Tools

Reporting Services Development

how to convert in the layout to numeric

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2009-02-25 : 08:26:48

HI

IN THE SUBQUERY FOR ONE OF THE COL AM DOING THE CALUCULATION

CONVERT(varchar(10), FLOOR((SUM(FLOOR(PLT.PLT_HRS) * 60 + FLOOR(PLT.PLT_HRS % 1 * 100))) / 60)) + '.' + RIGHT('0' +CONVERT(varchar(10),(SUM(FLOOR(PLT.PLT_HRS) * 60 + FLOOR(PLT.PLT_HRS% 1 * 100)))% 60),2)

AND THEN IN THE OUTER QUERY AM CONVERTING THIS INTO

ISNULL( CONVERT(NUMERIC(32,2),a.minu),0) actual_hours

NOW IN THE REPORT AM TAKING THIS COL ONLY BUT IF I EXPORT THIS INTO EXCEL SHEET AND DO SUM IT WILL SHOW WRONG RESULT

SO I WANT IN THE REPORT THAT
FOR EX :ACUTAL_HOURS IS 3.15
IT SHOULD DISPLAY AS 3.25 i.e IT SHOULD CONVERT IT INTO NUMERIC

AS IT SHOULD TAKE ONLY 15 MIN FIRST AND THEN 15/60 MIN THEN THIS IL GIVE 0.25 AND THIS SHOULD UPADTE WITH 3 THEN IT SHOULD COME 3.25 LIKE THIS..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 08:29:28

Please post some sample data...

E 12°55'05.63"
N 56°04'39.26"

shm
Yak Posting Veteran

86 Posts

Posted - 2009-02-27 : 00:04:56

hi

ok..in the query am getting but i want that in the report expr

ex:if the col1 = 3.15 this is varchar datatype (actually it is hrs and mins)

i should get in the number datatype as after the decimal point 15 this should divide by 60 and then before the decimal point that is 3 this should concatenate with 15/60 i.e it il become 3.25

15/60 = 0.25
3+0.25 =3.25

like this the result should come col2=3.25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 13:16:58

you need to do this manually, like

Floor(Val(Fields!col1.value)) + ((Val(Fields!col1.value)-Floor(Val(Fields!col1.value)))/60)

shm
Yak Posting Veteran

86 Posts

Posted - 2009-03-06 : 02:15:03

hi

i tried Floor(Val(Fields!col1.value)) + ((Val(Fields!col1.value)-Floor(Val(Fields!col1.value)))/60)
this is coming wrong data

for 0.30 it is coming 0.005 it should come 0.50

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 13:13:05

seems like you've applied formatting for converting to percentage. just right click on cell and check format tab in cell properties and select number as format

shm
Yak Posting Veteran

86 Posts

Posted - 2009-03-12 : 02:51:07

hi

no i have not changed to percentage format..

i tried by number format as u mentioned but again it is not working..

shm
Yak Posting Veteran

86 Posts

Posted - 2009-03-12 : 03:37:30

hi

it is working i changed that expr to

= formatnumber(floor(val(Fields!col1.Value)) + formatnumber(right(formatnumber(val(Fields!col1.Value)-floor(Val(Fields!col1.Value)),2),2)/60,2),2)

=Floor(Val(Fields!col1.value)) + ((Val(Fields!col1.value)-Floor(Val(Fields!col1.value)))/60)

in the above expr ((Val(Fields!col1.value)-Floor(Val(Fields!col1.value)))

if it is 18.05 the value coming in this is 0.05 it is taking 0.05/60 but i want 05/60
for that i used right function

now it is coming correct

thank u

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources