Replace negative values

hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maartenYou can modify select statement to something like this-

select item, qty, value=
case
when value < 0 then '0' else value
end
from test

Roshmi Choudhurysql

Replace first 3 digit of phone numbers

Hi All,

I am trying to replace only first 3-digit of phone number starting with 011 to 000.

CREATETABLE #temp(stringRep VARCHAR(60))

INSERTINTO #temp(stringRep)VALUES('011-203-0011')

INSERTINTO #temp(stringRep)VALUES('011-203-2333')

DECLARE @.startPos INT

SET @.startPos = 1

UPDATE #temp SET stringRep =REPLACE(stringRep,SUBSTRING(stringRep, 1,

3),'000')

SELECT*FROM #temp

DROPTABLE #temp

stringRep

000-203-0000
000-203-2333

it also replaces the last three digit with 000. the result i would like to get is

stringRep

000-203-0011
000-203-2333

can anybody advise me on this? thanks!

Try:

update #temp

set stringRep = stuff(stringRep, 1, 3, '000')

where stringRep like '011%'

AMB

sql

Replace

Hi there,

We have a field with '.' and '-' and we need that the command replace response our only numbers without spaces.

Examples:
Field: '13.880-7' -> What we need: '138807'
Field: '13880-7' -> What we need: '138807'
Field: '1.307' -> What we need: '1307'

But the server configuration return spaces using the command:
SELECT REPLACE (REPLACE ('13.880-7', '.', ''), '-', '')

RETURN:
13 880 7 (replace for spaces)

How can I solution this problem with the SET clause without change the server configuration ?!?!

Thanks

Angela & David.SELECT REPLACE (REPLACE ('13.880-7', '.',space(0)), '-',space(0))|||Originally posted by tecman
SELECT REPLACE (REPLACE ('13.880-7', '.',space(0)), '-',space(0))

Still not working... this sintax returning 'Null'.

But Thanks.

Angela & David.|||What in the server configuration causes these added spaces ?|||Originally posted by rnealejr
What in the server configuration causes these added spaces ?

I dont know... How do I to discovery this ?|||When you had referenced 'server configuration' in your 1st post - I was under the impression you knew - I am unaware of anything that would be causing this problem. Which sql server version including service pack is installed ?|||Originally posted by rnealejr
When you had referenced 'server configuration' in your 1st post - I was under the impression you knew - I am unaware of anything that would be causing this problem. Which sql server version including service pack is installed ?

Its very strange...
Because one Database is Ok, the Replace function works... cutting the string.
And in another Database (in same server) dont works....

Im going crazy.|||OK - now we have some baseline. What are the field(s), including data types, you are running replace against ?|||What is your server configuration, may U send a sp_configure? And post a sp_dboption db_name too.

Is the sql server a 2K or 7.0??

if the version is 2k, this database is slq7.0 compatible ?|||Originally posted by Asmodan
What is your server configuration, may U send a sp_configure? And post a sp_dboption db_name too.

Is the sql server a 2K or 7.0??

if the version is 2k, this database is slq7.0 compatible ?

Version: 7.0 (2k compatible)

sp_configure:

name minimum maximum config_value run_value
----------- ---- ---- ---- ----
affinity mask 0 2147483647 0 0
allow updates 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 44 44
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 64 64
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 767 767
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 512 512
min server memory (MB) 0 2147483647 639 639
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196611 196611
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0

sp_dboption:

Settable database options:
-----------
ANSI null default
ANSI nulls
ANSI warnings
auto create statistics
auto update statistics
autoclose
autoshrink
concat null yields null
cursor close on commit
dbo use only
default to local cursor
merge publish
offline
pending upgrade
published
quoted identifier
read only
recursive triggers
select into/bulkcopy
single user
subscribed
torn page detection
trunc. log on chkpt.|||Generate sp_dboption for both tables - the 1 that removes the space and the one that doesn't.|||Yes, U must generate the sp_dboption for the databases

exec sp_dboption 'name of the db'

both, ok...|||Originally posted by rnealejr
Generate sp_dboption for both tables - the 1 that removes the space and the one that doesn't.

Look this:

Doesnt Remove: (SP_DBOPTION 'YF_CLIENT')
The following options are set:
-----------
trunc. log on chkpt.
auto create statistics
auto update statistics

Removes: (SP_DBOPTION 'SAG')
The following options are set:
-----------
auto create statistics
auto update statistics|||Is that the only difference between the 2 ? Which service pack are you running ?|||The diference dont solve the problem, may U can execute this comand, please...

SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO

just copy to your query analizer and run it.|||Originally posted by Asmodan
The diference dont solve the problem, may U can execute this comand, please...

SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO

just copy to your query analizer and run it.

Not yet:
13 880 7

In other server:
138807

H E L P !!!|||You are running this in query analyzer under the same sql server connection - but if you select 1 database within qa you have 1 output - select another database you receive a different output using 'SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')'|||Originally posted by rnealejr
You are running this in query analyzer under the same sql server connection - but if you select 1 database within qa you have 1 output - select another database you receive a different output using 'SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')'

This conversion its a example. Because this conversion is in a stored procedure, inside a query who bring a varchar field who need to be converted.

If I change the database, this sintax work out.

Thanks again.
(Sorry... my English is horrible)|||U are change just the database where the proc is , not the server, verify the collation, compare the option, is everything equal??

My english is bad too, where are U from??|||Originally posted by Asmodan
U are change just the database where the proc is , not the server, verify the collation, compare the option, is everything equal??

My english is bad too, where are U from??

Brazil, and U ?!?

Well... In the db_option, and sp_configure is everything equal. Where do I to see another configuration in database ?

In another server and another database is ok.|||Brasil tbem, ufa... vai ficar mais fcil... trabalha aonde?|||Originally posted by Asmodan
Brasil tbem, ufa... vai ficar mais fcil... trabalha aonde?

Heheheh !! Que engraado.
Trabalho na Sul Amrica, So Paulo... e vc ?!?!?

Ento cara, so dois DBs, um se comporta de uma forma e o outro de outra forma. Verifiquei a sp_option e sp_configure e ambas no coincidentes. Vc tem alguma sugesto ?!?|||Trabalho no lloyds, aqui na Berrini...

Trabalho mais com sybase, isto sim banco de dados....

Estamos migrando para sql 2k, e temos dois servers 7.0.

Bem, a proc que faz este convert est compilada num banco A, se compila ela num banco B, no mesmo server, ela passa a funcionar de moda diferente, vc j recompilou a proc nos dois bancos?? Pode ser algum bugzinho.
Tentei reproduzir o result errado que vc tem a, mas nem com reza brava ele coloca os espaos.
Vc pode me passar a proc?|||Originally posted by Asmodan
Trabalho no lloyds, aqui na Berrini...

Trabalho mais com sybase, isto sim banco de dados....

Estamos migrando para sql 2k, e temos dois servers 7.0.

Bem, a proc que faz este convert est compilada num banco A, se compila ela num banco B, no mesmo server, ela passa a funcionar de moda diferente, vc j recompilou a proc nos dois bancos?? Pode ser algum bugzinho.
Tentei reproduzir o result errado que vc tem a, mas nem com reza brava ele coloca os espaos.
Vc pode me passar a proc?

Mas o problema principal nem na procedure. Num simples SELECT utilizando o Replace, num Database ele corta (que o que quero) e no outro ele coloca espaos... no sei vc entendeu.

Usando:
SELECT REPLACE ('13.665-7', '.', '')

Em um ele resulta:
13665-7

No outro:
13 665-7

Eu gostaria de saber se existe algum SET XXXX {ON|OFF} que eu possa desabilitar ou habilitar algo para q funcione... E t difcil.|||Olha eu no consegui reproduzir aqui, ou seja, meio chute, mas o nico set que pode te ajudar seria

set ANSI_PADDING {on;off}|||Originally posted by Asmodan
Olha eu no consegui reproduzir aqui, ou seja, meio chute, mas o nico set que pode te ajudar seria

set ANSI_PADDING {on;off}

... ainda no funcionou.|||I can translate a little - but are you still having the problem ?|||Originally posted by rnealejr
I can translate a little - but are you still having the problem ?

Yes... i cant resolve the problem. Probably is a easy problem, but still not working.

Can U help me ?!|||Post the queries you are running under both databases ( as simple as possible ) with the replace function. At the moment include definitions of the field you are using for both databases in these replace functions.|||This may not be feasible but have you tried copying the database that is giving you the problem to see if the problem persists in the copied database ?|||RE:
Not yet:
13 880 7

In other server:
138807

...

H E L P !!!The diference dont solve the problem, may U can execute this comand, please...
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
just copy to your query analizer and run it.

This thread is complex to follow, however the issue is most probably quite simply the current DB compatibility setting.
For example run the following:

exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '65'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '80'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO|||Compatibility was already mentioned which triggered me to test it - I tried all version combinations on both sql server 7/2000 databases and no luck - the replace worked fine.

SAMI - Did this make a difference for your database ?|||RE: Compatibility was already mentioned which triggered me to test it - I tried all version combinations on both sql server 7/2000 databases and no luck - the replace worked fine.

(Please see / test the attachment to verify that it does / does not reproduce the described symptom on your systems.)

Normally, the DB cmptlevel certainly can produce the behavior described (this is fairly well known behavior, and it works as documented on all of my dev systems). When the DB cmptlevel = 60 or 65, an empty string literal ('') is interpreted as a blank (an ascii 32).

If you are saying you tried setting DB cmptlevel = 60 or 65 with all version combinations on both sql server 7/2000 databases, and then could NOT produce the following in the 65 cmptlevel DB:

SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '') As '@.new_cmptlevel = ''65'''

-- Result Set:

@.new_cmptlevel = '65'
----------
13 880 7

Then your systems are interesting (what sp, hotfixes, etc. are you running)?|||We have a field with '.' and '-' and we need that the command replace response our only numbers without spaces.
Examples:
Field: '13.880-7' -> What we need: '138807'
Field: '13880-7' -> What we need: '138807'
Field: '1.307' -> What we need: '1307'
But the server configuration return spaces using the command:
SELECT REPLACE (REPLACE ('13.880-7', '.', ''), '-', '')
RETURN:
13 880 7 (replace for spaces)
Q1 How can I solution this problem with the SET clause without change the server configuration ?!?! Thanks Angela & David.

Note: As I mentioned, the issue is probably simply the current DB compatibility setting, DB cmptlevel = 60 or 65; (and not the result of a server wide setting pe se).

{I probably would have guessed as much earlier, if I had just specifically analyzed your original post's sql and result sets more fastidiously.}

A1 i
If I am in fact correct: To address this issue without changes to the DB configuration (specifically a 60 or 65 DB cmptlevel); you may create a stored procedure that takes the string input and returns a parsed result. For an example see: dba_ParseOutNonNumerics.sql

A1 ii
I do not know how you may " [solve] this problem with the SET clause ", but if there is a way to do so, I would appreciate understanding it.|||DBA - Nice recovery - I made the same mistake you did - leaving off the @.new_cmptlevel in the select statement. The pre-7 documentation reflected the issue of empty strings being interpreted as a space but the tests conflicted with the documentation.

SAMI - This was neglected to be mentioned in DBA's follow-up but you have to use both - the exec sp_dbcmptlevel and the @.new_cmptlevel in the select statement.|||I'm not sure I follow. RE:
DBA - Nice recovery - I made the same mistake you did - leaving off the @.new_cmptlevel in the select statement. The pre-7 documentation reflected the issue of empty strings being interpreted as a space but the tests conflicted with the documentation.

SAMI - This was neglected to be mentioned in DBA's follow-up but you have to use both - the exec sp_dbcmptlevel and the @.new_cmptlevel in the select statement.

Each time I execute my original post to this thread in Pubs it reproduces the symptom: (it explicitly demonstrates the use of the @.new_cmptlevel parameter of the sp_dbcmptlevel stored procedure.)

Post Subject: Much ado about the DB compatibility setting

This thread is complex to follow, however the issue is most probably quite simply the current DB compatibility setting.
For example run the following:

exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '65'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '80'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO

Repeating data regions on every page

Hi all, I want to create an invoice that will repeat numbers dates and
shipping info on every page that the list of items grows on to. You know,
to simulate a standard preprinted invoice page but fully generated by
reporting services.
Any assistance is appreciated.
Raul RubioThe sample report, at the end of this posting, should point in the direction
you need to go.
Since you will have a fairly regular structure I suggest that you use a
table instead of lists.
I think you will find them much easier to work with than a List which
contains a number of textboxes.
Note the use of the table group on OrderID.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raul R" <roller8@.hotmail.com> wrote in message
news:e%23qgdx$nEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Hi all, I want to create an invoice that will repeat numbers dates and
> shipping info on every page that the list of items grows on to. You know,
> to simulate a standard preprinted invoice page but fully generated by
> reporting services.
> Any assistance is appreciated.
> Raul Rubio
>
SampleInvoice.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.5in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Quantity.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderDate">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>OrderDate</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!OrderDate.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CompanyName.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Country.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="OrderIDGroup">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Quantity_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>ca33ccc8-f580-468b-ae00-bdebbfd7a5f0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CompanyName, Customers.City,
Customers.Country, Orders.OrderID, Orders.OrderDate, [Order
Details].UnitPrice, [Order Details].Quantity,
Products.ProductName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4d305298-f219-4b15-ab25-32b10f38c465</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Rendering to Excel: numbers stored as text

Some of my reports are rendering to Excel with numbers stored as text (the
little green triangle in the upper corner of each cell alerts the viewer of
the problem).
While this seems innocuous, it means that a user can't do typical Excel
operations, like highlighting cells and creating a graph, or highlighing and
viewing the totals on the status bar. Although there's a number in the cell,
it acts like text.
Not all of my reports are doing this, but I'm having major trouble figuring
out the differences between those which are, and those which are not. Also,
I can't find any documentation about how the rendering makes this
determination.
Thank you!It appears I've finally found the solution. Since I knew I wasn't *really*
aggregating any values, I was using First() as my aggregation formula. When
I switched to Sum() as my aggregation, the result was forced to be numeric,
even through the Excel rendering.
How was anyone supposed to figure this out without documentation!? I mean,
if the fundamental data is numeric, then why should First( ) change it to
text? That seems SO arbitrary...!
I hope this helps someone to NOT pull their hair out like I did.
"mr_bert" wrote:
> Some of my reports are rendering to Excel with numbers stored as text (the
> little green triangle in the upper corner of each cell alerts the viewer of
> the problem).
> While this seems innocuous, it means that a user can't do typical Excel
> operations, like highlighting cells and creating a graph, or highlighing and
> viewing the totals on the status bar. Although there's a number in the cell,
> it acts like text.
> Not all of my reports are doing this, but I'm having major trouble figuring
> out the differences between those which are, and those which are not. Also,
> I can't find any documentation about how the rendering makes this
> determination.
> Thank you!|||Thanks
This just saved me a day of pulling my hair out