How Do You Convert A Number From Negative To Positive?

 I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number?  One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive.  Does anyone know how this can be achieved?



ADVERTISEMENT

Group By Positive Or Negative Number In Table

Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.

I have a report I am creating where I need to group on whether a number is positive or negative.  The number will never be zero.  This number is a decimal (currency), if that makes a difference.

I have tried creating a formula field with the following expression:





Code Snippet

IIf(Fields!Num.Value > 0, 1, 0)

I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.

If you need any more information, please let me know and I will provide what I can.  Thanks for your answers!

View Replies View Related

Convert Negative Number Stored As Nvarchar

I'm working with a horrible database!  The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format.  I need to convert the field to a decimal (9,2) but I can't because of the negative sign.
 
Example:
 
Balance
00000000
0000-413
0000-913
00-10913
00009526
 
I don't even know where to start to convert this.  Any help is appreciated!
 
Thanks.
 

View Replies View Related

QA Returning Negative #s As Positive

Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.

View Replies View Related

Positive Negative Values In SUM

 



Hi,


I have table with a column which has both negative and positive values.

I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

Plase help me.


Ex: My  Input Values are
 
   column1
-186.5499257
-141.8985222
-95.3846883
-71.1359768
-69.39071172
30.44750844
40.33666032
53.81527872
98.2967252
112.948667
 
OutPut goes like This
 
  Column1                    Column2
-186.5499257               null
-141.8985222               null
-95.3846883                 null
-71.1359768                 null
-69.39071172            -564.36  
30.44750844               null
40.33666032               null
53.81527872               null
98.2967252                 null
112.948667               335.84
 
 

View Replies View Related

Negative Numbers Become Positive When Transferring From 400

Help! I have a DTS package that does a data transfer between an AS400 and SQL 7. There is a field in the 400 table that contains negative numbers. After the transfer, the same field in the SQL table has lost the negative sign and is now positive. Also, the data in the original table has two decimal places. After the transfer, there are fifteen decimal places and it appears that it has been "unrounded", i.e. instead of 98.80, it's now 98.799999999999997.

Prior to upgrading to 7.0, we were doing the same thing with 6.5 using HDR to do the transfer. It worked perfectly then.

Anyone have any ideas??? Your help is MUCH appreciated.

Chris W.

View Replies View Related

How Do I Delibrately Make A Positive Value A Negative Value

 

 I want to convert a positive value to a negative value.

How do I do it...

View Replies View Related

Treat Data Differently For Positive Vs Negative #

Ok, I have a table with IP addresses stored in decimal format using both positive and negative numbers.
The way that they are stored is:
Positve
1 thru 2147483647 = 0.0.0.1 - 127.255.255.255
Negative
-2147483648 thru -1 = 128.0.0.0 - 255.255.255.255
Conversion
positive
x/2^24 . (x/2^24)/2^16 . etc . etc
negative
(x+2^32)/2^24 . ((x+2^32)/2^24)/2^16 . etc . etc

I have a script which works by using UNION and the WHERE statements are x>0 x<0

My problem is I need to use a 3rd party app to run the script (McAfee ePO). McAfee does not recognize the UNION. My question is, can I acheive the same results as the script below, without using UNION.

SELECT ReportFullPathNode.FullPathName,
cast(cast(IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
UNION ALL
SELECT ReportFullPathNode.FullPathName,
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start+4294967296
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start<0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID

View Replies View Related

Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero

 

I've a stacked column chart (vertical) both with positive and negative value.
 
I want an horizontal line only for the value = 0.
 
I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.
 
Can I show the gridlines only for the value 0?
Simona

View Replies View Related

Creating A Blank Row In A Table Between Positive &&amp; Negative Values

I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?

Thanks,
Steven

View Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View Replies View Related

Inserting Negative Number?

Hi

I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?

TIA

JDJ
jdj@gvd.dk

View Replies View Related

Can't Convert Negative Numbers From String Format

Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
 
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
 
Any ideas?
 
Thank you for your help!
 
cdun2

View Replies View Related

Meaning ??? Negative Number And Out Of Lock

Help

Why is the unsed space is negative number? Whould that affect the database? How do you fix the negative number?

If 'out of lock' happen, how do you check at what level the object was/is locked and how many locks is needed to configure?

Thank you ahead of time

View Replies View Related

DATEDIFF Returns Negative Number

I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number.

Here is my SQL statement:

SELECT
dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime)
AS TimeDiff
FROM dbo.FDC_Trips

For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.

How can I get this query to work with times before and after midnight?

View Replies View Related

Negative Number Parenthesis Alignment

I am  representing negative number within parenthesis , but as I am working on reporting service, it is possible to make the exact alignment of the number and parenthesis.
 
here is one example ....
when i format number with #,##,0;(#,##,0) i am getting output in a format  in subsequent rows like
 

  123,345
(145,567)
 
How can I make this alignment like
 
 123,345
(145,567)
 
where the parenthesis will be outside the number space keeping in mind the alignment is in right .
 
any help will be apriciated.
 
thanks
Aru

View Replies View Related

Sp_spaceused Report Negative Number For Unused

Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.

Thanks.

View Replies View Related

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.

Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

View Replies View Related

Convert A Number To Hex

Is there a existing stored proc to convert a integer is SQL Server to
a hex number?

View Replies View Related

How To Convert A Number To String In SQL?

I search the help for T-sql, still don't know how to convert a number, for example 12345 to a string with formating like 12,345
Thanks for your help.

View Replies View Related

Convert Varchar To Number

Using SQL 2005. I have a field with dollar amounts but field type is VarChar. Need to convert to number to sum. What's the best way to do this or how can I sum a field type Varchar that has dollar amounts. Thank you, David

View Replies View Related

Convert Number To Time

In my database times are stored as integers. Therefore if someone arrives a 1am the value is 60, 2am 120 etc. Is there a way to convert these integer values to 1:00, 2:00. I've tried converting to a decimal so if somene arrives at 1:30 it returns 1.5, but I still only get the integer value...

CONVERT(decimal(10,2), (90/60)) AS iStart

Thanks in advance
Roy

View Replies View Related

Convert Varchar To Number

Hi

In my MSSSQL database I have a table that contains "AGE" and its datatype is varchar.

I want to calculate the min(age) and max(age).

But the AGE field's datatype is varchar and I canot do any mathematical functions.



In Oracle There is a code for doing that.I think this is the code


select max(tonumber(age));


what is it's MSSQL equivalent.


thanks

sabu

View Replies View Related

How To Convert 6.49146E+11 To NUMBER ?

Flat file has a data like 6.49146E+11.
 
How to convert this to 649146029310.
 
Whats the syntax for derived column component ?
 
Thanks.

View Replies View Related

How To Convert A Bit String To 0x Number?

hi,

I have a bit string like '000111010101101110000111' , how to convert it to varbinary format like 0x1D5B83?

Thanks.

 

View Replies View Related

How Can I Convert Datetime To Number Of Minutes

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

View Replies View Related

Convert Month Into Its Number Equivalent...(Ex. JAN=1,FEB=2...)

Good Day to Everyone,

Need help!

I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month.

Now my questions are:
1.) How can I select these values sorted according to date. Since this is a character data type. Sorting is alphabetical. I need to sort it according to date where JAN05-1 is the first record and the last record is DEC06-2 (assuming that the oldest year is 05 and 06 is the latest).
2.) How can I convert the 3 char month into its number equivalent. Example, JAN is equal to 1, FEB is 2, MAR is 3....
3.) How can I count the no. of days in a month? Is there a function that would result to the no. of days in a given month?

Thank you so much in advance. :)

View Replies View Related

Convert Number (seconds To Datetime)

Hi
I have a field (int) thats a value in seconds.

I need to add it to a datetime value to get a finish time ..

how can i convert it to datetime (baring in mind its seconds)
so far any conversion i do converts it to days...

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved