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!



ADVERTISEMENT

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?

Thanks,
Steven

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

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

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

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

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

SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket

DESTINATION TABLE

ID_____SEQ______COMMENT
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:

SELECT DISTINCT $Date FROM BookMe ORDER BY $Date

 

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

 

Thx!

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

Hi

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

Thanks

View Replies View Related

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

Hi,

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

Example:

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?

Thanks,

Venkat

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
 
=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)
 
What I need is code for a combination of the two...to display code for page of total pages that resets within a group.
 
Any help is greatly appreciated.
Thanks!

 

View Replies View Related

Resetting Page Number On Group Break

 

Hi,
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.
http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
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
 
OutPut:
 
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

AS

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

c.siteID=s.siteID


and c.active=1

and c.potential=0
 

insert #getTable

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

from site s,child c,billingTotal bt

where --s.siteName=@cmb1

s.siteID=bt.siteid


and c.siteID=s.siteID

and bt.siteID=s.siteID

and bt.childid=c.childid

and c.active=1

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

p.siteID=s.siteid


and c.siteID=s.siteID

and p.childID=c.childID

and c.active=1

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)

insert

#results

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

from

#gettable g

,child c

,billpayer b

,currency cu

,site s

where

cu.currencyid=s.currency


and s.siteID=c.siteID

and c.siteID=g.thesite

and c.childID=g.thechild

and b.billpayerID=c.billPayerID

group by

c.forename,

c.surname,

b.telhome,

b.surname,

b.forename,

c.siteID,

cu.symbol


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

order by

c.surname asc

insert #results

select '','',''

insert #results

select

'Total Balance'

,''

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

from

#gettable g

,site s

,currency c

where

g.thesite=s.siteid

and c.currencyID=s.currency



group by

c.symbol


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

drop table #gettable

select * from #results

drop table #results

go

 

View Replies View Related







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