Check for Null in Expression

1) I need to know how to check for the null value. IsNothing and IS
System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to work.
=IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
I get a warning message for this,
The value expression for the textbox â'mmActDateâ' contains an error: Argument
'Month' is not a valid value.
2) Can someone give some examples for Switch and choose Statements to use in
Expression like IIf. thanks.
thanks in advance.Use
Fields!mmActDate.Value Is Nothing
--
Thanks,
ID
"Giya" wrote:
> 1) I need to know how to check for the null value. IsNothing and IS
> System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to work.
> =IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
> Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
> I get a warning message for this,
> The value expression for the textbox â'mmActDateâ' contains an error: Argument
> 'Month' is not a valid value.
> 2) Can someone give some examples for Switch and choose Statements to use in
> Expression like IIf. thanks.
> thanks in advance.
>|||Thanks for the response. I tried the one below, But still i get same error.
=IIf(Fields!mmActDate.Value Is Nothing,
MonthName( Fields!mmPlanDate.Value),
MonthName( Fields!mmActDate.Value ))
:(
"exkievan" wrote:
> Use
> Fields!mmActDate.Value Is Nothing
> --
> Thanks,
> ID
>
> "Giya" wrote:
> > 1) I need to know how to check for the null value. IsNothing and IS
> > System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to work.
> >
> > =IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
> > Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
> >
> > I get a warning message for this,
> >
> > The value expression for the textbox â'mmActDateâ' contains an error: Argument
> > 'Month' is not a valid value.
> >
> > 2) Can someone give some examples for Switch and choose Statements to use in
> > Expression like IIf. thanks.
> >
> > thanks in advance.
> >|||The IsNothing function can be used in an IIF statement like this:
=IIF(IsNothing(Fields!your_field.value), Action_if_null, Action_if_not_null)
So, in your case, this should work:
=IIf( IsNothing(Fields!mmActDate.Value), MonthName(
Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
Regards.
--
Please mark the correct/helpful answers!|||Can you describe the next properties? Maybe the problem is not the IsNothing
function.
Name
Value
Format|||Yes, thats right. The problem is not in the Is Nothing function. It is in the
MonthName function. thanks.
"F. Dwarf [MCP]" wrote:
> Can you describe the next properties? Maybe the problem is not the IsNothing
> function.
> Name
> Value
> Format|||Try this:
=MonthName(iif(IsNothing(Fields!mmActDate.Value), Fields!mmPlanDate.Value,
Fields!mmActDate.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Giya" <Giya@.discussions.microsoft.com> wrote in message
news:AAA118AF-E0F4-45F7-A0B6-749CB5A106E9@.microsoft.com...
> Yes, thats right. The problem is not in the Is Nothing function. It is in
> the
> MonthName function. thanks.
> "F. Dwarf [MCP]" wrote:
>> Can you describe the next properties? Maybe the problem is not the
>> IsNothing
>> function.
>> Name
>> Value
>> Format|||Try this instead:
=MonthName(iif(IsNothing(Fields!mmActDate.Value), Fields!mmPlanDate.Value,
Fields!mmActDate.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Giya" <Giya@.discussions.microsoft.com> wrote in message
news:0CB6BF4F-4FEE-49B0-83E1-35FAB145A4B8@.microsoft.com...
> Thanks for the response. I tried the one below, But still i get same
> error.
> =IIf(Fields!mmActDate.Value Is Nothing,
> MonthName( Fields!mmPlanDate.Value),
> MonthName( Fields!mmActDate.Value ))
> :(
> "exkievan" wrote:
>> Use
>> Fields!mmActDate.Value Is Nothing
>> --
>> Thanks,
>> ID
>>
>> "Giya" wrote:
>> > 1) I need to know how to check for the null value. IsNothing and IS
>> > System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to
>> > work.
>> >
>> > =IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
>> > Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
>> >
>> > I get a warning message for this,
>> >
>> > The value expression for the textbox 'mmActDate' contains an error:
>> > Argument
>> > 'Month' is not a valid value.
>> >
>> > 2) Can someone give some examples for Switch and choose Statements to
>> > use in
>> > Expression like IIf. thanks.
>> >
>> > thanks in advance.
>> >

Check Constraint Regular Expressions

Does MS SQL support full blown Regular expression features under SQL CHECK CONTRAINTS ?Full blown Regular expression features? Explain what it is!|||Yes to an extent. Remeber that a check constraint need only evaluate to True Or False. Consider the following:

create table #Tmp(f1 int, f2 varchar(10) constraint Tmp_f2 check(f2 like 'a_[0-9][0-9]-[A-Z]'))
insert into #tmp values (1,'az00-a')
insert into #tmp values (1,'a*99-a')
insert into #tmp values (1,'b*99-a')
insert into #tmp values (1,'b1')

In Perl or Python the last character of f2 wold need to be uppercase but because I have a case-insensative collation upper or lower case is accepted.

Check Constraint Expression

I'm using SQL Server 2005 and new to it.
I'm wanting to modify a Check constraint expression to say that the field (a
description) needs to be unique. I also want it to be null, but figure I ca
n
allow nulls or not within the field definition. Can I specify that in the
constraint as well?
Mainly, I need to be able to say that the field is unique, and since it's a
description.
How do I do this?I tried the following, but this didn't work. I was able to enter duplicate
descriptions.
([OrderTypeDescription] IS NOT NULL AND
[OrderTypeDescription] NOT IN (SELECT OrderTypeDescription FROM T_OrderType))
"HockeyFan" wrote:

> I'm using SQL Server 2005 and new to it.
> I'm wanting to modify a Check constraint expression to say that the field
(a
> description) needs to be unique. I also want it to be null, but figure I
can
> allow nulls or not within the field definition. Can I specify that in the
> constraint as well?
> Mainly, I need to be able to say that the field is unique, and since it's
a
> description.
> How do I do this?
>|||You don't want a CHECK constraint. Drop any existing CHECK constraint.
Make the column NOT NULL and add a UNIQUE constraint.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
news:D6DEF6E6-3B74-47F9-92DE-9688198EE8EF@.microsoft.com...
I'm using SQL Server 2005 and new to it.
I'm wanting to modify a Check constraint expression to say that the field (a
description) needs to be unique. I also want it to be null, but figure I
can
allow nulls or not within the field definition. Can I specify that in the
constraint as well?
Mainly, I need to be able to say that the field is unique, and since it's a
description.
How do I do this?|||Where do I do that from within the SQL Server Management Studio?
"Tom Moreau" wrote:

> You don't want a CHECK constraint. Drop any existing CHECK constraint.
> Make the column NOT NULL and add a UNIQUE constraint.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
> news:D6DEF6E6-3B74-47F9-92DE-9688198EE8EF@.microsoft.com...
> I'm using SQL Server 2005 and new to it.
> I'm wanting to modify a Check constraint expression to say that the field
(a
> description) needs to be unique. I also want it to be null, but figure I
> can
> allow nulls or not within the field definition. Can I specify that in the
> constraint as well?
> Mainly, I need to be able to say that the field is unique, and since it's
a
> description.
> How do I do this?
>|||I'd just do it in a query:
alter table MyTable
drop
constraint MyConstraint
go
alter table MyTable
alter column
MyCol int not null
go
alter table MyTable
add
constraint MyConstraint unique (MyCol)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
news:D3170FBC-98AB-4FC5-9D20-4B697F698781@.microsoft.com...
Where do I do that from within the SQL Server Management Studio?
"Tom Moreau" wrote:

> You don't want a CHECK constraint. Drop any existing CHECK constraint.
> Make the column NOT NULL and add a UNIQUE constraint.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
> news:D6DEF6E6-3B74-47F9-92DE-9688198EE8EF@.microsoft.com...
> I'm using SQL Server 2005 and new to it.
> I'm wanting to modify a Check constraint expression to say that the field
> (a
> description) needs to be unique. I also want it to be null, but figure I
> can
> allow nulls or not within the field definition. Can I specify that in the
> constraint as well?
> Mainly, I need to be able to say that the field is unique, and since it's
> a
> description.
> How do I do this?
>|||>I'm using SQL Server 2005 and new to it.
>I'm wanting to modify a Check constraint expression to say that the field (
a
>description) needs to be unique. I also want it to be null, but figure I c
an
>allow nulls or not within the field definition. Can I specify that in the
>constraint as well?
If I understand you, you want to allow the column to be NULL, but if
it is NOT null you want it to be UNIQUE. Is that correct?
If so, the next question is whether only one row can be NULL, or any
number of rows can be NULL. If only a single NULL row is allowed then
Tom's approach of adding a UNIQUE contraint will work fine. However,
if multiple NULL rows is allowed UNIQUE will fail on the second
occurance.
If what you need is multiple NULLs but unique non-NULL values, then we
just need a small adjustment to the code you posted.
(OrderTypeDescription IS NULL OR
OrderTypeDescription NOT IN
(SELECT OrderTypeDescription FROM T_OrderType))
Roy|||The proposed solution will allow a single null value.
I'd rather create an indexed view for that constraint (providing that the
description column allows nulls):
create view dbo.UniqueDescription
with schemabinding
as
select <column list>
,<description column>
from <table>
where (<description column> is not null)
go
create unique clustered index <index name>
on dbo.UniqueDescription
(
<clustered index candidate>
)
go
create unique nonclustered index <index name>
on dbo.UniqueDescription
(
<description column>
)
go
ML
http://milambda.blogspot.com/

Check Constraint

Hi I was wodering how to add an OR statment right in the Check Constraint expression.

This is what I am starting with in the database

([zip] like '[0-9][0-9][0-9][0-9][0-9]')

and what I want well not exact but this would answer my question

([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')

Thanks for any help

Maybe will be better if you will check if value is numeric instead of using very big LIKE for numeric part ?

where ISNUMERIC(zip)=1

or zip like '[A-Z][A-Z][A-Z][A-Z][A-Z]'

if should be all you need if size of your field is 5 char long.

|||Thanks for looking in on me but this

([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')

is just an example

What I really want to know is can I use an OR or an AND in

Check Constraint expression area

Thanks

|||

so probably you can put something like this in constrain expression

[zip] like '[0-9][0-9][0-9][0-9][0-9]'

or zip like '[A-Z][A-Z][A-Z][A-Z][A-Z]'

you can also create function and check its result

it should return bool value

dbo.checkZIPFormat(zip)

or you have to test the result

dbo.checkZIPFormat(zip) = 1 (if function returns 1 when ZIP is valid)

|||

Hi thanks again for checking. The problem lies in the database not in the code. It is in the Constraints part of the table. I can change this but no matter what I do with the code it will not let in any thing that does not match the expression right in the data base. I can even delete this out and write my own code but thats not whats required. I need to be able to have it check for [0-9][0-9][0-9][0-9][0-9] OR [A-Z][A-Z][A-Z][A-Z][A-Z]

Thank again

Mike

|||

Hi JPazgier, I have figured it out you would do somthing like

([zip] like '[0-9][0-9][0-9][0-9][0-9]' OR [zip] like '[A-Z][A-Z][A-Z][A-Z][A-Z]')

Thanks Again

Mike

Chart, how do I use format code for a label?

How do I refer to the actual value for the label inside my expression?
Say you have datetime data across the x-axis for the data. I get one row per
month in the dataset (every row is same date and time for each row/month). I
understand how I can format this, for instance "MMM" to get month name in
short format.
But if I want to do further or a bit more complex manipulation? In this
case, I want to show only first letter of the month. I fail to connect how
to put the actual data value inside my VB.NET expression (substring, left or
similar function in this case).
(I can retrieve the first letter of the month along with the data, as an
extra column, doing this in my stored procedure. If above is difficult, I'd
appreciate tips on how to refer to this column for the chart label. I.e., I
want to show some other column as the label, not the one used to derive the
actual value.)
TIA
Tibor Karaszi
SQL Server MVPExpressions for formatting labels are not directly supported in the current
release. You could do a bar chart and use an expression
(=Left(Format(Fields!OrderDate.Value, "MMM"), 1)) for the corresponding
category group. The sample report attached at the end of this post (which
runs against local Northwind database) demonstrates this.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEwX0qaiEHA.2848@.TK2MSFTNGP10.phx.gbl...
> How do I refer to the actual value for the label inside my expression?
> Say you have datetime data across the x-axis for the data. I get one row
per
> month in the dataset (every row is same date and time for each row/month).
I
> understand how I can format this, for instance "MMM" to get month name in
> short format.
> But if I want to do further or a bit more complex manipulation? In this
> case, I want to show only first letter of the month. I fail to connect how
> to put the actual data value inside my VB.NET expression (substring, left
or
> similar function in this case).
> (I can retrieve the first letter of the month along with the data, as an
> extra column, doing this in my stored procedure. If above is difficult,
I'd
> appreciate tips on how to refer to this column for the chart label. I.e.,
I
> want to show some other column as the label, not the one used to derive
the
> actual value.)
> TIA
> Tibor Karaszi
> SQL Server MVP
>
+++++++++++++ Sample report +++++++++++++
<?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>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Freight.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Title />
<Width>5.25in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Left(Format(Fields!OrderDate.Value, "MMM"), 1) & "
[" & Format(Fields!OrderDate.Value, "MMM yyyy") & "]"</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<Style>
<Format>c</Format>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 OrderDate, Freight
FROM Orders
ORDER BY ShipCity</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Chart X-axis & Y-axis Title

Does RS allow to use expression to generate titles for X- and Y-axis, either
through a field obtained from the database or a Parameter ?
Right now, even after SP1, I do not see a button that leads to <Expression>
dialog for X and Y-axis title, similar to say a <textbox> control.
Thanks.Although there is no expression builder button there, you can type any
expression you want for axes titles.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:9543D0CD-C766-4ED5-BEDA-CD43728F2728@.microsoft.com...
> Does RS allow to use expression to generate titles for X- and Y-axis,
> either
> through a field obtained from the database or a Parameter ?
> Right now, even after SP1, I do not see a button that leads to
> <Expression>
> dialog for X and Y-axis title, similar to say a <textbox> control.
> Thanks.

Chart x-axis

I am trying to create a chart using end_date (parameter) as the x-axis. I
have set up the category group expression as "=Month(Fields!End_Date.Value)"
and this is used for my x-axis. When the report is viewed, it shows January -
September, November, December, then October. The end_date is setup as
datetime datatype. The value and the labels are correct, just in the wrong
order. How can I get this x-axis in the correct order?On Aug 14, 9:08 pm, j_rad <j_...@.discussions.microsoft.com> wrote:
> I am trying to create a chart using end_date (parameter) as the x-axis. I
> have set up the category group expression as "=Month(Fields!End_Date.Value)"
> and this is used for my x-axis. When the report is viewed, it shows January -
> September, November, December, then October. The end_date is setup as
> datetime datatype. The value and the labels are correct, just in the wrong
> order. How can I get this x-axis in the correct order?
Hi,
Try to put the sorting expression of the group =Month(Fields!
End_Date.Value)
with ascending order.
V.|||That worked. Thanks for your help.
"Vinnie" wrote:
> On Aug 14, 9:08 pm, j_rad <j_...@.discussions.microsoft.com> wrote:
> > I am trying to create a chart using end_date (parameter) as the x-axis. I
> > have set up the category group expression as "=Month(Fields!End_Date.Value)"
> > and this is used for my x-axis. When the report is viewed, it shows January -
> > September, November, December, then October. The end_date is setup as
> > datetime datatype. The value and the labels are correct, just in the wrong
> > order. How can I get this x-axis in the correct order?
> Hi,
> Try to put the sorting expression of the group =Month(Fields!
> End_Date.Value)
> with ascending order.
> V.
>

Chart expression error

I have a pretty simple chart which seems to work, but when I build it, I get
a warning that "the expression for the chart "chart1" contains an error:
operation is not valid due to the current state of the object". I've
experimented with a bunch of things to get rid of the warning,
unsuccessfully. May I have some suggestions for how to figure out exactly
what the warning is referencing?
--
Bob RogersBob, can you post a minimal RDL running against e.g. Northwind which
reproduces the issue. This would help in narrowing down and finding a
solution.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> I have a pretty simple chart which seems to work, but when I build it, I
get
> a warning that "the expression for the chart "chart1" contains an error:
> operation is not valid due to the current state of the object". I've
> experimented with a bunch of things to get rid of the warning,
> unsuccessfully. May I have some suggestions for how to figure out exactly
> what the warning is referencing?
> --
> Bob Rogers|||I did finally discover the problem: an error in an expression controlling the
"data label" within "point labels" accessed through the Data tab of a chart
property. There was no misbehavior apparent, so I just had to plug through
all the specifications. That was my first chart. Next time, I will pay
close attention to the appearance of a warning, while I still have some idea
what I had just done that might have caused it. I was hoping you would say
"turn on x debug option and read a stack" or something like that. Anyway,
thanks for the attempt.
"Robert Bruckner [MSFT]" wrote:
> Bob, can you post a minimal RDL running against e.g. Northwind which
> reproduces the issue. This would help in narrowing down and finding a
> solution.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
> news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> > I have a pretty simple chart which seems to work, but when I build it, I
> get
> > a warning that "the expression for the chart "chart1" contains an error:
> > operation is not valid due to the current state of the object". I've
> > experimented with a bunch of things to get rid of the warning,
> > unsuccessfully. May I have some suggestions for how to figure out exactly
> > what the warning is referencing?
> > --
> > Bob Rogers
>
>

Chart dataset error from sql sum fields

I'm receiving an,
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?

CHARINDEX returns zero in TEXT column

I'm running into an issue where CHARINDEX on a text datatype column returns
0
if the expression I'm searching for exists at a position greater than 8000.
For example:
use pubs;
select charindex('New Moon Books',pr_info,8000)
from pub_info
returns zero, even though I know 'New Moon Books' exists past character
8000. Is this a known issue, and is there a workaround? It's causing my
search and replace procedure (using the UPDATETEXT function) to fail, i.e.
LIKE '%searchfor%'
is true but
CHARINDEX('searchfor',textColumn)
is zero.
Any help is appreciated.Did you try PATINDEX?
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||CHARINDEX will not work for strings larger than 8000. To work with TEXT
fields larger than this size, you will need to use the TEXT functions in SQL
Server 2000 like READTEXT, WRITETEXT etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||charindex won't work with text datatype. Use patindex|||Thanks Aaron, you of course are correct, PATINDEX works! (I was sure I had
tried that before, but I think I left out the wildcard character).
Anyway, much thanks (and thanks to Omnibuzz too!)
A.S.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Did you try PATINDEX?
>
> "Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
> news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
>
>

Changing the width of table column based on data returned

I would like to size the width of a table column to be big enough for the
data being returned. I don't seem to be able to use and expression for the
width parameter. How might I dynamically resize the with of table columns?THere is a property of a textbox which will allow its size to grow to fit
the text value... Just check the box...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:D3F216B4-9589-44D6-96F4-4F55DBFB34DE@.microsoft.com...
>I would like to size the width of a table column to be big enough for the
> data being returned. I don't seem to be able to use and expression for
> the
> width parameter. How might I dynamically resize the with of table
> columns?|||Greg,
The only thing I've seen so far in all my playing around is a height
increase/decrease, not a width version. Rightclick the field in question,
and choose properties. On the first tab is "Can increase to accomodate
contents" and "Can decrease to accomodate contents". I don't know why MS
doesn't have a dynamic width increase. Maybe the Dev team didn't put it in
so it wouldn't mess with the column structure.
Catadmin
"Greg Larsen" wrote:
> I would like to size the width of a table column to be big enough for the
> data being returned. I don't seem to be able to use and expression for the
> width parameter. How might I dynamically resize the with of table columns?