Cell1 = Cell2

I'm pulling my hair out now... What am I doing wrong
OK..
Header1 | Header
--+--
cell1 | cell
Very basic table layout..
If Cell1 is just a field value I can do cell2 = reportitems!cell1 an
it works find. However, if Cell1 is a calculation that's slightl
complex and do cell2 = reportitems:cell1 I get
> p:\formulacard\formulacard1\Formula Card SG.rdl The value expressio
for the textbox ?QtyReqdCell? refers to the report item ?kl?. Repor
item expressions can only refer to other report items within the sam
grouping scope or a containing grouping scope
Now this annoys the hell out of me because the cell1 on it's ow
renders fine and returns a values. What am I doing wrongRob,
Is this actually a table or a matrix?
What is the expression in cell 1, both when it succeeds and when it
fails.
The ReportItems option can only deal with items within the same scope,
i.e. on the same level. One of its uses was for exporting to Excel as
formulas instead of values, but there were some bugs with it so
Microsoft curtailed it's functionality a bit. I now avoid using
ReportItems if I can. I tend to make sure the query provides all the
data I need to populate cells, including calculations.
Chris
RobFarley wrote:
> I'm pulling my hair out now... What am I doing wrong?
> OK...
> Header1 | Header 2
> --+--
> cell1 | cell 2
> Very basic table layout...
> If Cell1 is just a field value I can do cell2 = reportitems!cell1 and
> it works find. However, if Cell1 is a calculation that's slightly
> complex and do cell2 = reportitems:cell1 I get
> > p:\formulacard\formulacard1\Formula Card SG.rdl The value expression
> for the textbox ?QtyReqdCell? refers to the report item ?kl?. Report
> item expressions can only refer to other report items within the same
> grouping scope or a containing grouping scope.
> Now this annoys the hell out of me because the cell1 on it's own
> renders fine and returns a values. What am I doing wrong?|||I've gone for that option now, everything is calculated in the quer

Casting Help

Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.

Thanks
Sam "O"

cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)
intUpdateCount = cmdInsert.ExecuteNonQuery()

What are the values for sun, etc.? Could they be null? Could they be non-numeric, like 'fred'? If they are numeric, why are you using a varchar field?|||Thank you for your quick reply. Here are the answers to your questions.

yes values could be null
They will never be non-numeric
I am using an exisiting table and I am not able to modify the datatypes (yet)

Thanks
Sam "O"|||I am not certain the NULL is the issue, but I would try using IsNull(), like so:

CAST(IsNull(@.v,'0') as Numeric(4,2))

Even if it does not throw the exception, if the variable is null, null+anything will return null.

I would also check and see that all values are null rather than a single space. For instance, this will give you the casting error:

DECLARE @.v as varchar(20)
SET @.v=''
SELECT CAST(IsNull(@.v,'0') as Numeric(4,2))

If the values that do not appear obviously to be numbers are not NULL, but are rather empty strings or ' ', then perhaps you will want to run a query to change the "missing" values to null, or create a view with case statements to fix up the values. This is why using one type when you want another is a bad idea.|||I did try the IsNull Prior to posting and still was thrown the exception so it makes sense to me now what you are saying about the empty string possibilities.

Thank you for your input!

Cast varchar to decimal

I am losing my hair...and my mind...
Is there any reason why I wouldn't be able to cast a varchar value of say
7.8 to decimal?
I have a whole bunch of lab results that come with a bunch of garbage in the
result column. I have stripped it away so that it is only a format
[1-x].[0-9]. I want to make it a number so I can identify High and low value
s
for each patient.
Am I missing something...besides my mind?
Thanks in advance,Don't see any problem in achieving what you want.
Can you post a sample to exactly understand what your issue is?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>I am losing my hair...and my mind...
> Is there any reason why I wouldn't be able to cast a varchar value of say
> 7.8 to decimal?
> I have a whole bunch of lab results that come with a bunch of garbage in
> the
> result column. I have stripped it away so that it is only a format
> [1-x].[0-9]. I want to make it a number so I can identify High and low
> values
> for each patient.
> Am I missing something...besides my mind?
> Thanks in advance,
>
>|||Here is the code... pretty straight forward....
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
Any thoughts?
Thanks in advance,
"SriSamp" wrote:

> Don't see any problem in achieving what you want.
> Can you post a sample to exactly understand what your issue is?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>
>|||168 7.5 7.5 5
Perhaps those aren't spaces - in fact I think they are one carriage return
and one line feed character. How are these values inserted? You should
prevent illegal values from being entered at all.
In the mean time - remove char(13) and char(10) from the string.
ML
http://milambda.blogspot.com/