How Do You Convert A Number From Negative To Positive?
Jan 18, 2008
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?
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!
I have a 2012 report builder chart that has two series (one area chart and one bar chart) combined into one chart. The problem I'm having is the bar chart has much smaller numbers than the area chart and the scaling is messed up.
Is there any way to put the bar chart on the right axis and keep the area chart on the left axis? My goal is to increase the size of the bar chart in relation to the area chart.
Also, is there any way to make the bar chart color red if the number is negative and green if it is positive?
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.
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?
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
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?
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords UUUDUUUDUUUD1230123012301175 ------------0000000000001163 UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords UUDUUUDUUUUU-----------07 UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.
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 ?
How do I count the number of positive dollar values in a query? I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money
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?
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.
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?
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 .
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.
I have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
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
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...
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
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.
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?
