Decimal Not Displaying Correctly From Excel

Sep 6, 2007



I have an Excel spreadsheet with three columns of data, one of which is a "Score". The score will range between 1.0 and 0, going out to two decimal places. I am able to get this data into a global temporary table. I have a script in which a message box pops up, displaying the MIN value of the "Score" field in the temp table. The MIN value is .2. When I try to get the data from the temp table to a staging table, the Scores are all rounded to the nearest whole number. I think I've tried using every numeric data type for the staging table, and I always get the same results. In the temp table, Score is defined as such:


[Score] [decimal](18, 0) NULL


Does anybody know what I need to do to get the score to display acccurately?

Lindsay

View 5 Replies


ADVERTISEMENT

Report Not Displaying Correctly

Nov 5, 2007

I have a report in SQL Reporting Services that isn't displaying correctly. The report comprises a header and a simble table, typically consisting of 20 to 30 columns and 1-20 rows.

When I run the report IE, the table is truncated and only the first five columns are displayed. I can get the table to display correctly by doing one of the following:


Changing the report zoom - once the zoom has been changed, the report displays correctly even back at the default 100%

Moving to the next report page, then back to the first page

Refreshing the report by right-clicking and selecting 'Refresh' (not using the refresh button in the tool bar)
What seems strange is that if I copy the truncated table from IE to Excel, the full table is copied so it appears that what I'm seeing is some kind of display issue. I've trawled Google and the forums but to no avail - can anyone help?

Thanks!

View 3 Replies View Related

Getting JUST Date From Datetime Not Displaying Correctly

Jun 2, 2008

here is my select command:

SelectCommand="SELECT [JobID], [EmployeeName],

CAST(

STR(YEAR(DATEENTERED)) + '/' +
STR(MONTH(DATEENTERED)) + '/' +
STR(DAY(DATEENTERED)) AS DATETIME
) AS Date,

[From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs]"

It will show correctly in the query builder test but when you load it up in the gridview it will show the date AND 12:00:00AM for every one of the dates

I tried replacing DATETIME with VARCHAR but it would insert spaces into the date where I didn't want them: EX: (12/04/1994 => 12/ 04/ 1994)

any suggestions?

View 1 Replies View Related

Report Manager Not Displaying Correctly

Sep 10, 2007

I have managed to get reporting services running on Vista, but when I open report manager, I don't see the "Contents" or "Properties" tabs. I get the "SQL Server Reporting Services - Home" header and the links on the top right, but no tabs along the yellow or blue lines. I also have created and deployed a report but it does not show up in Report Manager.



What step have I missed?


Thanks,
Dan

View 5 Replies View Related

Report Manager Not Displaying Correctly

Sep 6, 2007

I have managed to get reporting services running on Vista, but when I open report manager, I don't see the "Contents" or "Properties" tabs. I get the "SQL Server Reporting Services - Home" header and the links on the top right, but no tabs along the yellow or blue lines. I also have created and deployed a report but it does not show up in Report Manager.



What step have I missed?


Thanks,
Dan

View 1 Replies View Related

Stored Procedure Using UNION Joins Is Not Displaying Correctly... Can Someone Help Me With My Logic?

May 16, 2007

I have a stored procedure using UNION joins on three SQL queries.
Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me.  I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :)
I need to return one result set, sorted by date... one complete result per day.  eg: 5/15/2007 |  XX | XX | XX | XX | XX | XX |5/16/2007 |  XX | XX | XX | XX | XX | XX |5/17/2007 |  XX | XX | XX | XX | XX | XX |
Currently, when I run the query, I'm getting three separate date values for each date...
eg:5/15/2007 |  XX | XX | 00 | 00 | 00 | 00 |5/15/2007 |  00 | 00 | XX | XX | 00 | 00 |5/15/2007 |  00 | 00 | 00 | 00 | XX | XX |5/16/2007 |  XX | XX | 00 | 00 | 00 | 00 |5/16/2007 |  00 | 00 | XX | XX | 00 | 00 |5/16/2007 |  00 | 00 | 00 | 00 | XX | XX |etc
How do I fix this?  I've looked through my query ad naseum and don't see anything that sets me off as "wrong".
Here is the stored procedure if you can help.  I'd really really love the help!

C R E A T E  P R O C E D U R E  sp_ApptActivityDate
(@strWHERE  as varchar(500), @strWHERECANCELED as varchar(500))
as
exec ('SELECT   [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts,  SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots,  0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM         dbo.vw_ALL_ApptActivity ' + @strWHERE + '
UNIONSELECT    [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots,  COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots,   0 AS TotalCancelAppts, 0  AS TotalCancelSlotHoursFROM         dbo.vw_Active_ApptActivity' + @strWHERE + '
UNIONSELECT    [date] as DATE,  0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts,    0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts,   SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM         dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + '
ORDER BY dbo.vw_ALL_ApptActivity.[Date] '   )GO

View 12 Replies View Related

SQL 2012 :: SSRS Report Not Displaying Time Values Correctly From Server Table

Feb 26, 2015

I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.

SQL Server table time value shown in milliseconds:
00:00:00.82

Reporting Services report value shown:
00:00:00

View 3 Replies View Related

Need Help Displaying A Fraction As A Decimal

Oct 15, 2007

 Hi, If you execute the following query in TSQL: select 1/2 you will always get back zero.  I need a simple SQL statement that will return its  decimal equivalent (i.e. .5).   Can anyone give me a hint how to do this? Thanks  

View 3 Replies View Related

Displaying Output In Decimal

Dec 22, 1999

Hi

Can anyone out there help me to solve the displaying floating point?

The problem is i couldn't get the result as 3.33333. The result i am getting is 3.00.

My coding is as below:
declare @a decimal(7,5)
select @a = 10/3

select @a
Result : a = 3.00

Thanks in advance

Rgds
Ave

View 1 Replies View Related

Reporting Services :: SSRS 2012 Report Colors Do Not Export To Excel Correctly

Jul 21, 2015

I am facing some issue in rendering my SSRS report(2012 version) to excel sheet with color codes. i am using conditional formatting to display the colors. So what is happening is ,take the below expression as an example, for the id  = 0, we are not getting white back ground, we are getting some other colors. But the same thing is working fine in SSRS 2008R2. 

=IIF(MAX(Fields!id.Value=0,
= 0, "WHITE","BLUE")

View 4 Replies View Related

Reporting Services :: SSRS 2012 Matrix Report Colors Do Not Export To Excel Correctly

Feb 12, 2013

We mark cells in  a report using color names like red, pink, and transparent in the fill expression. In the browser it works correctly. When exported to Excel the colors go to the wrong cells. This worked correctly in SSRS 2008. 

View 9 Replies View Related

Problems With The Query, ResultSet Property Not Set Correctly, Parameters Not Set Correctly, Or Connection Not Established Cor

Oct 22, 2007



I have the following query in an ExecuteSQL Task:

Insert Into Table2
Select * From Table1 Where Column1Val = '4'


As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.

Why is the above query failing with

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

View 4 Replies View Related

Issue In Displaying Footers While Exporting The Report To Excel From Ssrs

Mar 19, 2008

Hi,

I have developed a report in ssrs 2005. While exporting the report to excel 2007, i have a problem in displaying the footer values in spreadsheet. Moreover, in the page footer of the report, i have added a few condtions so as to display one of the textbox's values in the last page alone and the rest in all of the pages! But when i look in to the page layout in excel, i get in all of the pages rather than having it in the last page.

Added to that a cell doesnt hold more than 255 characters in excel.

can anyone help me out with this??

Thanks,
Kani.

View 7 Replies View Related

Reporting Services :: Exporting Decimal Value 15.00 In Excel File

Jun 18, 2015

When i am exporting into excel then 15.00 value is showing me 15 value.Is there way to show the value 15.00 in excel.

View 3 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!



I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007



I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007





I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.



It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???



Thank you for your help!

Anna





View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,




I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101




Please help!



Thanks

Mirudhu

View 4 Replies View Related

Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category

Sep 21, 2006

I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those.  Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID   Category1      Saints2      Falcons3      Bucaneers4      Chargers5      FalconsPlayer Table:ID    CategoryID   Player                 News                                Player Last Updated1            1           Reggie Bush       Poetry in motion                                9/21/20062            1           Drew Brees         What shoulder injury?                        9/18/20063            5           Michael Vick       Break a leg, seriously.                       9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this.  I just don't know how to loop through and display it on a page.  Right now I have two datareaders in the code behind but ideally something like this, I would think the code  would go on the page itself, around the html.

View 1 Replies View Related

Sql Not Working Correctly

Mar 30, 2004

and (jobs.ApplicationsClosed > getdate() OR jobs.ApplicationsClosed = getdate())


The above statement does not pick jobs.ApplicationsClosed= 3/30/2004(that is today'sdate)
If I change 3/30/2004 to 3/31/2004 it picks it up. What is wrong with my syntax?

Thanks!

View 2 Replies View Related

How To Use The Bcp Utility Correctly?

Dec 9, 2007

Hi guys,

I'm trying to use the bcp utility to transfer data from an SQL Server table (CUSTOMERS) to a text file (test.txt).

Here is what I have:
DECLARE @FileName varchar(50)
SET @FileName = 'C: est.txt'

EXEC bcp BAirwaysDB..CUSTOMERS out @FileName -S<servername> -T

only I keep getting the error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

and when running:
bcp BAirwaysDB..CUSTOMERS out C: est.txt -S<servername> -T

on the command line I get:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'RIVERAMelanie'.
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database "BAirways" requested by the login. The login failed.

Can you see where I'm going wrong with this? Any feedback would be really appreciated.

Thank you
Butterfly82

View 7 Replies View Related

Sum Not Working Correctly

Oct 12, 2007

Hi,
I have the following simple SP..

There is a field which I'm doing a sUM() on to get a total for a QtyRecieved from one of the tbls in the SP..
However, the QtyRecieved field is set to be a two decimal place numeric field.


The sp works fine without issues until on the of the qty received is not a round number.

If it's a number with a decimal in it (i.e 5.5) then this is show as a seperate result and not SUM'd with the others.

Any ideas why?

SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID,
dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
PDS_Login.Supplier_Products.UOM, PDS_Login.Supplier_Products.Vatable, dbo.Suppliers_POs_Details.ProductID,
dbo.Suppliers_POs_Details.PODetailsStatusReason, SUM(dbo.Suppliers_POs_Details_Received.Qty) AS QtyReceived
FROM PDS_Login.Supplier_Products INNER JOIN
dbo.Suppliers_POs_Details ON PDS_Login.Supplier_Products.ProductID = dbo.Suppliers_POs_Details.ProductID RIGHT OUTER JOIN
dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID
GROUP BY dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, PDS_Login.Supplier_Products.ProductName,
PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
PDS_Login.Supplier_Products.UOM, PDS_Login.Supplier_Products.Vatable, dbo.Suppliers_POs_Details.ProductID,
dbo.Suppliers_POs_Details.PODetailsStatusReason, dbo.Suppliers_POs_Details_Received.Qty
HAVING (dbo.Suppliers_POs_Details.POID = @POID)

View 10 Replies View Related

&<asp:SqlDataSource&> Need A Look Over To See If I Am Writing This Correctly.

Aug 31, 2006

This is a subset question of one of my other threads, the gist is while using forms authentication, I am trying to enter additional information into another table on the same db as the aspnet_membership tables.  I think I need an <asp:SqlDataSource> to do this because it is about the only thing left I haven't tried to get this code to work so any help is appreciated.  Environment: sql server 2000, windows server 2003, asp.net 2.0, vb, no GUI<ASP:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlServerConnection %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (@username,@email,@company,@firstname,@lastname,@phone)" runat="server" />looking over some of my books and online it says in the connectionStrings that it is supposed to be the database name that goes next to the : but when I try that I get an error that it is looking for the name of the connection string, so I enter the name of the connection string (as above) and nothing happens, which is basically the ongoing event with this whole problem, the form fields (username, email, password) that go into the aspnet_membership tables just fine, I don't have any problems with those, it is getting the additional information into another table that has been stalling me the whole time.So does this sqldatasource look right?  Any help is appreciated.

View 12 Replies View Related

Create New Sql Tables Correctly

Dec 12, 2007

Hi,

View 6 Replies View Related

CSV - Can't Import Data Correctly

May 16, 2008

Hi everyone,
I'm having some problems importing a CSV to a db in SQL Server
Consider the following csv input

"1102","Spiderman, New York City",

"1103","Superman, Metropolis",

"1104","Batman, Gotham City",


When I select comma as delimiter, the columns will automatically

be offset since the location includes commas as well. How do I correctly

set the delimiter?


I tried using the following as delimiter → ","

but that'll strip the quotes which are required in this case.
Does anyone have an idea how to resolve this?

View 8 Replies View Related

How To Install Sql Server Correctly?

May 22, 2005

Call me an idiot as I'm quite new to asp.net. I'm working through a
book called 'ASP.NET Unleashed' where the chapter on how to create a
database connection  to an sql database on MSDE called 'pubs' is
shown. Iv'e installed MSDE. When I try to run the example it says that
the login or authentication has failed. Iv'e tried different things but
I'm out of ideas. Another thing is that when I try to install sql
server 2000 on my Win XP it first says something about this being the
wrong OS or something like that. I can get past that but when I'm
presented with 3 installation options 1.client 2. client and
connectivity 3.connectivity I installed client because that's supposed
to create a stand alone installation.
However when I'm done installing the only new entry in the start->programs is
client connectivity. Where is the Enterprise services that I'm supposed
to be able to access according to the book? Is sql server 2000
compatible with XP and not just win 2000?

View 2 Replies View Related

Search Sp Not Functioning Correctly ???

Dec 13, 2005

can somebody tell me whats wrong with this sp..................when i pass lname only or fname only it does not return any data, but when i passs both lname and fname it returns the correct data.....when i rearrange the structure to where it checks the lname only it works also but fname doesn't .........any help  is greatlly appreciated
 
CREATE PROCEDURE [dbo].[temp_search] (  @lname varchar(50) = null,  @fname varchar(50) = null,  @date1 datetime = null, @date2 datetime = null)
/*set @lname = null -- 'aaa'set @fname =  null --'ddd'set @date1 = '2/25/2003'set @date2 = '9/30/2004'*/
as
set nocount on
 if not @lname is null or @lname = '' and not @fname is null or @fname = ''begin select temp_user_info.*, dateTime1 FROM temp_user_info  INNER JOIN temp_seq ON temp_user_info.temp_seq = temp_seq.temp_seq  WHERE temp_user_info.temp_seq = temp_seq.temp_seq   and temp_user_info.lname like @lname  and temp_user_info.fname like @fnameendelse  if not @lname is null or @lname = ''  begin  select temp_user_info.*, dateTime1  FROM temp_user_info   INNER JOIN temp_seq ON cir_temp_user_info.temp_seq = temp_seq.temp_seq   WHERE temp_user_info.temp_seq = temp_seq.temp_seq     and temp_user_info.lname like @lname end else   if not @fname is null or @fname = ''  begin   select temp_user_info.*, dateTime1   FROM temp_user_info    INNER JOIN temp_seq ON temp_user_info.temp_seq = temp_seq.temp_seq    WHERE temp_user_info.temp_seq = temp_seq.temp_seq      and temp_user_info.fname like @fname     end  else   if not @date1 = null  and not @date2 = null   begin    select cir_temp_user_info.*, dateTime1    FROM cir_temp_user_info     INNER JOIN temp_seq ON cir_temp_user_info.temp_seq = temp_seq.temp_seq     WHERE temp_user_info.temp_seq = temp_seq.temp_seq      and dateTime1 between @date1 and  @date2    end   else     if not @date1 = null    begin     select temp_user_info.*, dateTime1     FROM temp_user_info      INNER JOIN temp_seq ON temp_user_info.temp_seq = cir_temp_seq.temp_seq      WHERE temp_user_info.temp_seq = temp_seq.temp_seq       and dateTime1 like @date1       endGO

View 5 Replies View Related







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