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


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?

View Replies View Related

Creating A Blank Row In A Table Between Positive && 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?


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


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


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:
1 thru 2147483647 = -
-2147483648 thru -1 = -
x/2^24 . (x/2^24)/2^16 . etc . etc
(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),
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
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),
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?

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?


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?



View Replies View Related

Meaning ??? Negative Number And Out Of Lock


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:

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


How can I make this alignment like


where the parenthesis will be outside the number space keeping in mind the alignment is in right .

any help will be apriciated.


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.


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.



I don't even know where to start to convert this. Any help is appreciated!


View Replies View Related

Row Number Of Group Footer

How would I get the rownumber() for a Group Footer in report designer not in my query?

I put the syntax rownumber(nothing), but it is giving me the row numbers for the detail section.

Any help would be appreciated.

View Replies View Related

How To Get Number Of Pages For A Group?

Like many others, I have a large report that groups on invoices, and does a page break between each invoice. From reading other posts in this forum, I was able to reset the page number on the grouping, however, there are some things that I need to display only on the last page of each invoice, but I don't see how I will be able to determine when I'm on the last page for a particular invoice. Can I somehow determine up front the number of pages for a group so I know when I'm at the last page?

Any help is greatly appreciated!

View Replies View Related

How To Insert Sequence Number With Group

123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket


123_____1_______I am joe
123_____2_______I am programmer
124_____1_______I am wang
124_____2_______I am programmer
124_____3_______I like cricket
can somebody please advise the easiest way to do this in sql 2000?

View Replies View Related

How To Select Top N Number Per Date Group?

I have 1 table named BookMe,

with fields

$Date, $Div, $Name

I want to select the top N number of Div's (ie highest Div value) per distinct Date.

i know how to get the distinct date like this:


How do I combine a TOP keyword to get the TOP n rows per distinct date???


View Replies View Related

Any Way To Show A Group Detail Header Row Once For Each Group In A Table?

I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.

View Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time


I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View Replies View Related

Maximum Number Of Group-by Expressions Exceeded

Is there any way of configuring this parameter?

A work around?

Or have I reached the limits of scalablilty on this issue?

Please assist me


View Replies View Related

How To Extract A Sequence Number From The Group By Count?


I have a situation where I have to write a SQL to generate a sequence number from the count of the group by.


I have src data :


SUBSCRID col1 col2 col 3 col4

1001 val1 val2 val3 val4

1002 vala valb valc valc

1001 ................

1002 ..........

1001 ...........

1003 ................


So my result should be :


SUBSCRID SUBSCRCNT col1 col2 col3 col4

1001 1 val1 val2 val3 val4

1001 2 ...................

1001 3 .....................

1002 1 vala valb valc valc

1002 2 ................

1003 1 .................


Any help on how to write a query will be appreciated?



View Replies View Related

Reset Total Page Number In A Group

I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX. Code for page of total pages

="Page " & Globals.PageNumber & " of " & Globals.TotalPages

EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function


What I need is code for a combination of the display code for page of total pages that resets within a group.

Any help is greatly appreciated.

View Replies View Related

Resetting Page Number On Group Break

I have a report where i do a page break for each supplier. I want to reset my page number to '1' when there is a group break.When i googled for this functionality i found the following chris Hay's blog which provides the solution.
But if i follow the same, I get #Error instead of page number during the run time.How do i resolve the error?
Also, I want to display the "page 1 of totalpages in group" like that. Is that possible?

Thanks in advance

View Replies View Related

SELECT TOP With Additional Requirement: (limited Number Per Group)

This is a little bit tricky:

2 tables:

1) tblAlpha:
Name Alpha
ABC 0.111
DEF 0.222

2) tblSector:
Name Sector
ABC Industry
DEF Technology

I want to select top X Names from tblAlpha, ordered by Alpha, but I want to
limit the number of names for any Sector (Q).
E.g. X = 10, but Q = 10*50%= 5, that means I want the top 10 Alpha from
tblAlpha, BUT any Sector can not have more than 5 to be selected. So if all
Top 10 Alphas are Technology, I can only select top 5 of them and search
down the list further.

This should involve JOIN and TOP, maybe create some temp table, What should
I do to make it work?

Thanks a lot.

View Replies View Related

How Can I Show A Row Inside A Table Group Just Once After The Group Row?

I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.

View Replies View Related

Conditional Number Format On Specific Matrix Column Group

I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.

For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125

By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75

But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75

I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:

=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")

But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:

=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")

The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?

How else can I do this? I must be missing something here. Thanks.

View Replies View Related

Positive Value

I have the following query


ChildName BillPayerName Cost
Mary Fred 50

Total Balance Blank

The problem is when I check for a value > 00.1 in the having clause the total balance column disappears.
But its ok if I use any other comparison operator.
I.e < 00.1.

Any ideas.

create procedure Rpt_pos1 --(@cmb1 as varchar(50)) with ENCRYPTION as

--declare @startdate smalldatetime


set dateformat ymd

set datefirst 7

--loop through weeks

create table #getTable (thesite integer null,thechild integer null,cost money null)

insert #getTable select s.siteid,c.childid,0

from site s,child c

where --s.siteName=@cmb1



and c.potential=0

insert #getTable

select s.siteid,c.childid,isnull(sum(round(,2,2)),0)*-1

from site s,child c,billingTotal bt

where --s.siteName=@cmb1


and c.siteID=s.siteID

and bt.siteID=s.siteID

and bt.childid=c.childid


and c.potential=0

group by s.siteID,c.childid

insert #getTable select s.siteid,c.childid,isnull(sum(round(p.amount,2,2)),0)

from site s,child c,payment p

where --s.siteName=@cmb1


and c.siteID=s.siteID

and p.childID=c.childID


and c.potential=0

group by s.siteID,c.childid

create table #results (ChildName varchar(200) null,BillPayerName varchar(200) null, Cost varchar(20) null)



select c.forename + ' ' + c.surname

,case b.telhome when '' then b.Forename + ' ' + b.Surname

else b.Forename + ' ' + b.Surname + ' (' + b.telhome + ')' end

,case when sum(round(g.cost,2,2)) < 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) > 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) = 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15)) end


#gettable g

,child c

,billpayer b

,currency cu

,site s



and s.siteID=c.siteID

and c.siteID=g.thesite

and c.childID=g.thechild

and b.billpayerID=c.billPayerID

group by








having sum(round(g.cost,2,2)) > 00.1

order by

c.surname asc

insert #results

select '','',''

insert #results


'Total Balance'


,c.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))


#gettable g

,site s

,currency c



and c.currencyID=s.currency

group by


having sum(round(g.cost,2,2)) > 00.1

drop table #gettable

select * from #results

drop table #results


View Replies View Related

Copyrights 2005-15, All rights reserved