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?
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?
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.
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 = 22.214.171.124 - 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
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 ?
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?
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'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.
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?
SOURCE TABLE ID________COMMENT 123_______I am joe 123_______I am programmer 124_______I am Wang 124_______I am programmer 124_______I like cricket
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?
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
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?
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?
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:
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.