Conert Char to Datetime

Hello,

I have made a slight error in my SQL2000 database and have found this out with 10000 plus records under my belt.

I have a field called Char with entries 12mid and 12noon. I need to convert these to Datetime. All records convert OK except these two.

I am using DTS to transfer records form old table to new table. How do I write a procedure (or something) to enable conversion of the records 12mid and 12noon to 12am and 12pm.

Thanks

GoongYou run one batch that "REPLACE"s noon/mid with pm|||pkr,

When you say batch, what exactly is that. Is is the transformation in the DTS options.
When you say replace, how would I go about it, Is it through a query, if so, how would it possibly look.

Thanks
Goong|||Batch as in 'SQL Query'. You can run a batch in DTS but since you prob' want to do this once I'd just run it from your fav' query tool.

The clue was in my response, "REPLACE". Use it with an Update query.

Conditionally required field

How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

sqlsql

Conditionally counting detail field

Hello. I'm trying to conditionally count a field, for example:
iif(Fields!GrantCodeID.Value = 70, 1, 0)
GrantCode is a field and whenever it equals 70, I want to count it so
I can display the count after the detail.
Any help is much appreciated.
Thanks!=Sum(iif(Fields!GrantCodeID.Value = 70, 1, 0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <mike1174@.comcast.net> wrote in message
news:dfd40822.0408181042.401a6798@.posting.google.com...
> Hello. I'm trying to conditionally count a field, for example:
> iif(Fields!GrantCodeID.Value = 70, 1, 0)
> GrantCode is a field and whenever it equals 70, I want to count it so
> I can display the count after the detail.
> Any help is much appreciated.
> Thanks!

Conditionalize field values based on other field values

Here's a portion of the current statement.

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = 0,

bControlRiskIsAffirmed = 0,

bCombinedRiskIsAffirmed = 0,

bApproachTypeIsAffirmed = 0,

bCommentsIsAffirmed = 0

FROM EngagementAuditAreas WITH(NOLOCK) ...

And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),

bControlRiskIsAffirmed = (numControlRiskID IS NULL),

bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),

bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),

bCommentsIsAffirmed = (parent.txtComments IS NULL)

FROM EngagementAuditAreas WITH(NOLOCK)

Thanks.

Here is a small example of how you might accomplish your task.

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Affirmed char(4),
Num int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( NULL, 1 )
INSERT INTO @.MyTable VALUES ( NULL, 0 )
INSERT INTO @.MyTable VALUES ( NULL, NULL )

UPDATE @.MyTable
SET Affirmed = CASE Num
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE 'n/a'
END

SELECT *
FROM @.MyTable

RowID Affirmed Num
-- -- --
1 No 1
2 Yes 0
3 n/a NULL

However, it is usually NOT a good idea to have two columns that contain the same information (even if in two forms). You can easily 'transform' the values in the select queries using the same CASE structure as above.

COnditional visibility of a field in SSRS 2005

Hi,

I have a report in which there is a field called "Returned Qty" and there is a parameter called Show Qty now I want this field returned Qty to only appear if this show Qty parameter is set to yes. Can Anyone suggest me how to do this?

I guess It could be done by using the properties option and writing some expression. I appreciate the response,

Thanks,

Rashi

Hello Rashi,

Try putting this in the Hidden property of your textbox:

=IIf(Parameters!ShowQty.Value = "Yes", "False", "True")

Or, you can just replace the value directly in the textbox, using this expression:

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks for the solution, I am sure this should work but somehow it gives me error both ways. I wrote the first expression under the text properties of the box by checking the expression option but it dint work. I wrote the same expression under the hidden properties too but it too dint work. The second expression display field but the filed contains "Error".

I am sure the expresison is correct but I am not using it under correct text box may be.

My aim is not to display the field at all if showQty is set to No.

Pls let me know if I am commiting any mistake while doing this

Thanks again for your help,

Rashi

|||

Hi Rashi,

Try false and true without the quotes ""

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

Thanks,

Panna

|||

Yep, I tried this and it gives me an error " input string was not in correct format". No matter whatever expression do I write I am getting the same error message.

Any idea what it is?

|||

Sorry, yes, you will need to remove the " " from around the false and true. Right click and select Properties on your textbox that will hold the returned quantity. Select the Visibility tab, then select 'Expression:' from the 'Initial visibility:' section. Enter this as the expression here:

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

The other way to do this is to replace the value with nothing in the textbox where it actually shows the value. Right click on the textbox that will hold the returned quantity and select 'Expression:'. Enter this and hit ok.

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

But, you said this shows Error when you did it, so can you try this instead?

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, nothing)

Jarret

|||

When referencing parameters you can either look at the "Value" or the "Label".

=IIf(Parameters!ShowQty.Label = "Yes", False, True)

=IIf(Parameters!ShowQty.Value = False, False, True)

Either way should work!

GiveMeABreak. . .

|||I have been doing both ways n the error message is still the same no matter what I try it keeps giving: " input string not in correct format". If I remove all the expression it does build and shows me the returned qty firld but not with the expression values|||

Can you post the expression exactly as you have it in your textbox and in your hidden property?

Also, how do you have your parameter setup?

Jarret

|||

Thank you all, it has been fixed now.

I used the one below

IIf(Parameters!ShowQty.Value = False, False, True)

The mistake was in refrencing the parameters, otherwise its perfect!

Thanks again. :)

Rgds,

Rashi

sqlsql

Conditional totals for matrix report

I have a matrix report that has the value of one (1) in the row field when my
row header has that value.
agent air hotel cruise
-- -- -- --
bob 1 1
jim 1
jane 1 1
What I want is a total at the bottom of the report counting the numbe of
ones in the column. I am very new to SSRS as a whole, so please help!!
Thanks in advance!Carl,
Right click on the data row cell and click on "subtotals". Thats it you
have column totals.
--Venkat
Carl Henthorn wrote:
> I have a matrix report that has the value of one (1) in the row field when my
> row header has that value.
> agent air hotel cruise
> -- -- -- --
> bob 1 1
> jim 1
> jane 1 1
> What I want is a total at the bottom of the report counting the numbe of
> ones in the column. I am very new to SSRS as a whole, so please help!!
> Thanks in advance!|||Thank you for responding. I have tried the right click method, but the cell
that I need the subtotals on does not have "Subtotal" on the menu. Is there
some other way?
Thanks!
"venkat.oar@.gmail.com" wrote:
> Carl,
> Right click on the data row cell and click on "subtotals". Thats it you
> have column totals.
> --Venkat
> Carl Henthorn wrote:
> > I have a matrix report that has the value of one (1) in the row field when my
> > row header has that value.
> >
> > agent air hotel cruise
> > -- -- -- --
> > bob 1 1
> > jim 1
> > jane 1 1
> >
> > What I want is a total at the bottom of the report counting the numbe of
> > ones in the column. I am very new to SSRS as a whole, so please help!!
> > Thanks in advance!
>|||If possible pls send me the rdl file.. i will work on it and send it to
u back..
Carl Henthorn wrote:
> Thank you for responding. I have tried the right click method, but the cell
> that I need the subtotals on does not have "Subtotal" on the menu. Is there
> some other way?
> Thanks!
>
> "venkat.oar@.gmail.com" wrote:
> > Carl,
> >
> > Right click on the data row cell and click on "subtotals". Thats it you
> > have column totals.
> >
> > --Venkat
> > Carl Henthorn wrote:
> > > I have a matrix report that has the value of one (1) in the row field when my
> > > row header has that value.
> > >
> > > agent air hotel cruise
> > > -- -- -- --
> > > bob 1 1
> > > jim 1
> > > jane 1 1
> > >
> > > What I want is a total at the bottom of the report counting the numbe of
> > > ones in the column. I am very new to SSRS as a whole, so please help!!
> > > Thanks in advance!
> >
> >

Conditional Sum Statement

Hey all,
Quick question, I have a field that I need to sum only when another
field is a certan value.
For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
want to sum the sales_amt field when the first character of the
sales_code field is an 'X'. So for the set:
1, 1, 999, X100, $200.00
2, 1, 999, 200, $500.00
3, 1, 999, 898, $1000.00
4, 1, 555, X340, $2000.00
5, 1, 555, X444, $23.00
The resultant sums would be:
for dealer_id 1: 2223.00
for rep_id 999: 200.00
for rep_id 555: 2023.00
I am trying to do this in MRS as opposed to making it an additional
field in my querry.
Thanks!I think this is what you are asking...
And There is probably an easier way to do this but in the expression builder
IIF(SUBSTR(sales_code),1,1) = "x", Sum(sales_amt ),"")
Or something along those lines...
Hope that helps
Kerrie
Jimmy V wrote:
>Hey all,
>Quick question, I have a field that I need to sum only when another
>field is a certan value.
>For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
>sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
>want to sum the sales_amt field when the first character of the
>sales_code field is an 'X'. So for the set:
>1, 1, 999, X100, $200.00
>2, 1, 999, 200, $500.00
>3, 1, 999, 898, $1000.00
>4, 1, 555, X340, $2000.00
>5, 1, 555, X444, $23.00
>The resultant sums would be:
>for dealer_id 1: 2223.00
>for rep_id 999: 200.00
>for rep_id 555: 2023.00
>I am trying to do this in MRS as opposed to making it an additional
>field in my querry.
>Thanks!
--
Message posted via http://www.sqlmonster.com|||Kerrie,
I had to create a calculated field and summed it that way, i did use
your code snippit to generate my calculated field.
Thanks!!!|||Glad I could help, That is the best thing i have heard all day.
Thanks!
Jimmy V wrote:
>Kerrie,
>I had to create a calculated field and summed it that way, i did use
>your code snippit to generate my calculated field.
>Thanks!!!
--
Message posted via http://www.sqlmonster.com|||Hi Jimmy,
Easy way of doing is, if u want a sum by Sales order =x...., AND REP_ID
create a group with the sales order =x JUST "X" and u will get the value for
it
and then subtract this one with rest of value.
regards
JERROB
"Jimmy V" wrote:
> Hey all,
> Quick question, I have a field that I need to sum only when another
> field is a certan value.
> For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
> sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
> want to sum the sales_amt field when the first character of the
> sales_code field is an 'X'. So for the set:
> 1, 1, 999, X100, $200.00
> 2, 1, 999, 200, $500.00
> 3, 1, 999, 898, $1000.00
> 4, 1, 555, X340, $2000.00
> 5, 1, 555, X444, $23.00
> The resultant sums would be:
> for dealer_id 1: 2223.00
> for rep_id 999: 200.00
> for rep_id 555: 2023.00
> I am trying to do this in MRS as opposed to making it an additional
> field in my querry.
> Thanks!
>

Conditional statements in Views

Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?

> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!

conditional split on field in csv file

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

Might there be trailing spaces on the file?

Try:

Right(Trim([FieldA]),4)=="ABCD"

|||

Thanks fo reply, yes I tried that one but it doesn't have trailing spaces. I think it has something to do with fields in Input file.

When I look at the file in notepad values are

"123","somevalue"

"123ABCD","somevalue"

I have seen some posts regarding text qualifier as a problem, mine is set to " but conditional split still fails.

|||Is it throwing any type of error message?|||

No the file processes fine.

If I add another row to file and put in the suffix then the conditional split works but I think this is because the text qualifiers are removed once I had row to file eg

Original file

"123","somevalue"

"123ABCD","somevalue"

goes thru data flow task with no errors but conditional split doesn't work

Modified file will look like

123,somevalue

123ABCD,somevalue

456ABCD,somevalue

and will work for both rows with suffix.

|||

Could you put a data viewer before the conditional split and see what data is getting in?

Thanks.

|||

Have put data viewer in and first riow looks correct but rows following look like they have another delimiter shown below

123, somevalue

o “456”, “somevalue”

o “123ABCD”, “somevalue”

Row delimiter is set to {CR}. Column 1 delimiter is set to {CR} if I change this to {CR}{LF} then the process fails with error delimeter for column 1 not found.

Thanks

|||

Got it to work in the Columns tab

Row Delimeter is {LF}

Column delimiter is Comma {,}

Text qualifier is "

in advanced tab

Coliumn delimeter of 2nd column is {LF}

which ended up making data come in as

123, somevalue"

456, somvalue"

123ABCD, somevalue"

which the conditional split was happy with.

Thanks for your help

Conditional Split - Compare DATETIME with constant

Hi,

I have to compare a DATETIME Field with '1/1/1900 12:00:00 AM". Which is default DATE TIME Value in SQL Server.

I did compare like

TRADEAGREEMENTFROMDATE != (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM"))

but (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) returns "12/31/1800 12:00:00:AM"

Thanks,

Aravind

So the value I got was slightly different.

(DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) = 31/12/1899 00:00:00

Why are you using DATEPART? Do you not just want -

(DT_DBTIMESTAMP)"1/1/1900" = 01/01/1900 00:00:00

That is the same as '1/1/1900 12:00:00 AM' which you asked for above, infact the time format is just my local settings UK rather than US, the values are exactly the same.

Conditional SELECT

Dear Group

I'm having trouble with the statement below. I tried CASE and IF
without success. What I'm trying to do:
There is a field in the database called Business_TelNo. If the field
has some value, I would like to return a generated field
(LaBusinessTelNo), which is the label of Busines_TelNo, reading
'Phone:'
If Business_TelNo has no value, the label should be set to ''.

Something like this:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNo
END ELSE BEGIN SELECT '' AS LaBusinessTelNo END
FROM i2b_vw_contact

This is working:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
'Phone: ' AS LaBusinessTelNo
FROM i2b_vw_contact

PS: I know it would be much easier to add some logic in the
application but need to do this in SQL.

Thanks very much for your time and efforts!

MartinSELECT business_telno,
CASE WHEN business_telno>'' THEN 'Phone: ' ELSE '' END AS labusiness_telno
FROM i2b_vw_contact

You can find the CASE and IF syntax in Books Online but understand that CASE
is an *expression* whereas IF is a *statement* and therefore IF can't be
used as part of a query.

--
David Portas
SQL Server MVP
--|||Thanks David!
Have a nice day :-)

Conditional Image Visibility with Embedded code

I am trying to create a embedded function that will accept a dataset field
value and based on the value, make an image visibility hidden or visible in
the body of the report. I receive an error that the mathimage is not
declared. How do I reference an image in the body of the report. The field is
within a list and based on the subject an image is displayed in the bodiy of
the report.
Below is a sample of the embedded code.
Function VisibleYN(subject as string,passedyn as boolean) as double
if subject = "Math" and passedyn = true then
mathimage.visibility = visible
end if
if subject = "Science" and passedyn = true then
scienceimage.visibility = visible
end if
THe following expression is in the visibility property tab of the textbox
that holds the subject.
code.visibleyn(fields!subject.value as string, passedyn as boolean)
Any help would be appreciated!
--
MarieThe actual property is under the visibility and is named "IsHidden", so your
function should return a boolean rather than try and set a property.
Change
mathimage.visibility = visible
to
Return False
and likewise for your science one to get it to work.
"Marie" wrote:
> I am trying to create a embedded function that will accept a dataset field
> value and based on the value, make an image visibility hidden or visible in
> the body of the report. I receive an error that the mathimage is not
> declared. How do I reference an image in the body of the report. The field is
> within a list and based on the subject an image is displayed in the bodiy of
> the report.
> Below is a sample of the embedded code.
> Function VisibleYN(subject as string,passedyn as boolean) as double
> if subject = "Math" and passedyn = true then
> mathimage.visibility = visible
> end if
> if subject = "Science" and passedyn = true then
> scienceimage.visibility = visible
> end if
> THe following expression is in the visibility property tab of the textbox
> that holds the subject.
> code.visibleyn(fields!subject.value as string, passedyn as boolean)
> Any help would be appreciated!
> --
> Marie|||The reason I am trying to set the property is because the function is being
called from a textbox bound to a field. Based on the value in the field I am
setting the property of a checkmark image. If I return the boolean value how
do I use this value to set the property of another report item? I was hoping
I could do something like Reportitem.image1.ishidden = false.
Any suggestions?
Thanks for your help.
--
Marie
"David Swanson" wrote:
> The actual property is under the visibility and is named "IsHidden", so your
> function should return a boolean rather than try and set a property.
> Change
> mathimage.visibility = visible
> to
> Return False
> and likewise for your science one to get it to work.
> "Marie" wrote:
> > I am trying to create a embedded function that will accept a dataset field
> > value and based on the value, make an image visibility hidden or visible in
> > the body of the report. I receive an error that the mathimage is not
> > declared. How do I reference an image in the body of the report. The field is
> > within a list and based on the subject an image is displayed in the bodiy of
> > the report.
> >
> > Below is a sample of the embedded code.
> >
> > Function VisibleYN(subject as string,passedyn as boolean) as double
> > if subject = "Math" and passedyn = true then
> > mathimage.visibility = visible
> > end if
> > if subject = "Science" and passedyn = true then
> > scienceimage.visibility = visible
> >
> > end if
> >
> > THe following expression is in the visibility property tab of the textbox
> > that holds the subject.
> >
> > code.visibleyn(fields!subject.value as string, passedyn as boolean)
> >
> > Any help would be appreciated!
> >
> > --
> > Marie|||You can use an expression for the hidden property of the image in the report.
Right click on the image, expand Visibility, then use this in the Hidden
property
=TextBox1.Value="Math"
or
=iif(textBox1.Value="Math",true,false)
I was looking for something similar the other day, and I found the custom
code is a little quirky because I could not find a way to access control
members via code.
When I passed Me as an object, I got some interesting results. For example,
try
this:
function Test(me as object) as boolean
msgbox("String=" & Me.ToString)
return false
end sub
TextBox9.Value=Code.Test(me)
Check out the title of the msgbox itself! It looks like there is funcky
name mangling going on in the host, which prevents us from using objects
directly in custom code.
msgbox("Name=" & Me.Name) indicates a public member not available, so it
appears that unless there is a reflection trick, I have yet to find a way to
access member variables of a report control in custom code.
I'll keep an eye on this thread, and I hope this is of some help to you..
Dwayne

Conditional iif statement

Ok I have a sp that returns a number of records, each identfied as being part
of a group based on the Type field.
Type Name Amount
1 Test1 1.00
1 Test2 1.00
2 Test3 2.00
3 Test4 3.00
What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
works fine but when I use them middle records iff(Type.value = 2,
Price.value, 0) it displays 1.00 as the price for type 2, and it should
display 2.00 for type 2.
Any help is greatly apprciated. Also, due to the formatting specifics I am
using single textboxes instead of a table or list.
DigivixTry this...
=iif(Fields!Type.Value=1, Fields!Price.Value,0)
the same with Value =2
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
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> Ok I have a sp that returns a number of records, each identfied as being
> part
> of a group based on the Type field.
> Type Name Amount
> 1 Test1 1.00
> 1 Test2 1.00
> 2 Test3 2.00
> 3 Test4 3.00
> What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> works fine but when I use them middle records iff(Type.value = 2,
> Price.value, 0) it displays 1.00 as the price for type 2, and it should
> display 2.00 for type 2.
> Any help is greatly apprciated. Also, due to the formatting specifics I
> am
> using single textboxes instead of a table or list.
> Digivix|||Sorry for the typo's but that is exactly what i am using and it is giving me
the first record's price only.
"Wayne Snyder" wrote:
> Try this...
> =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> the same with Value =2
>
> --
> 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
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > Ok I have a sp that returns a number of records, each identfied as being
> > part
> > of a group based on the Type field.
> >
> > Type Name Amount
> > 1 Test1 1.00
> > 1 Test2 1.00
> > 2 Test3 2.00
> > 3 Test4 3.00
> >
> > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > works fine but when I use them middle records iff(Type.value = 2,
> > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > display 2.00 for type 2.
> >
> > Any help is greatly apprciated. Also, due to the formatting specifics I
> > am
> > using single textboxes instead of a table or list.
> >
> > Digivix
>
>|||Can't you go back to a table and simulate the look & feel? I think if you
did it in the detail row it would pull the right values.
--
"Everyone knows something you don't know"
"DigitalVixen" wrote:
> Sorry for the typo's but that is exactly what i am using and it is giving me
> the first record's price only.
> "Wayne Snyder" wrote:
> > Try this...
> >
> > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > the same with Value =2
> >
> >
> >
> > --
> > 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
> >
> > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > Ok I have a sp that returns a number of records, each identfied as being
> > > part
> > > of a group based on the Type field.
> > >
> > > Type Name Amount
> > > 1 Test1 1.00
> > > 1 Test2 1.00
> > > 2 Test3 2.00
> > > 3 Test4 3.00
> > >
> > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > works fine but when I use them middle records iff(Type.value = 2,
> > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > display 2.00 for type 2.
> > >
> > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > am
> > > using single textboxes instead of a table or list.
> > >
> > > Digivix
> >
> >
> >|||Hi David,
Thank you for the reply, however I don't understand what is meant by
"simulate the look & feel", can you please be a little more specific?
Thanks
"David Bienstock" wrote:
> Can't you go back to a table and simulate the look & feel? I think if you
> did it in the detail row it would pull the right values.
> --
> "Everyone knows something you don't know"
>
> "DigitalVixen" wrote:
> > Sorry for the typo's but that is exactly what i am using and it is giving me
> > the first record's price only.
> >
> > "Wayne Snyder" wrote:
> >
> > > Try this...
> > >
> > > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > > the same with Value =2
> > >
> > >
> > >
> > > --
> > > 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
> > >
> > > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > > Ok I have a sp that returns a number of records, each identfied as being
> > > > part
> > > > of a group based on the Type field.
> > > >
> > > > Type Name Amount
> > > > 1 Test1 1.00
> > > > 1 Test2 1.00
> > > > 2 Test3 2.00
> > > > 3 Test4 3.00
> > > >
> > > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > > works fine but when I use them middle records iff(Type.value = 2,
> > > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > > display 2.00 for type 2.
> > > >
> > > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > > am
> > > > using single textboxes instead of a table or list.
> > > >
> > > > Digivix
> > >
> > >
> > >

Conditional group footer sum

HI,
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!

Conditional Formatting on Datetime Field

I am building a report with a query that includes a field of type datetime.
I would like to change the color of the text in this column to RED if the
value contained in the field is earlier than now() - 10 minutes. I would
appreciate direction as to the appropriate function to use in this scenario.
I have tried to work with datediff, but without positive results.
Regards,
Pete Zerger, MCSE(Messaging)
Co-founder and Webmaster, MOMReourcs.org
URL:http://www.momresources.org
mailto:pete.zerger@.gmail.comWill something like this work for you?
=iif(Fields!EventDate.Value < dateadd("n", -10, Now()), "Red", "Black")
Regards,
Dan

Conditional Formatting

Hi all
Does anyone know how I can have conditional formatting in my reports?
What I want to achieve is something like. If the field text = "Green" make
the background green, etc
Thanks alot in advance
Ruse the iif function for the font color. Reporting Services accepts
expressions for most of its properties.
=iif(boolean condition, "red", "black")
HTH
Charles Kangai, MCT, MCDBA
"Rudi Groenewald" wrote:
> Hi all
> Does anyone know how I can have conditional formatting in my reports?
> What I want to achieve is something like. If the field text = "Green" make
> the background green, etc
> Thanks alot in advance
> R
>
>|||Hi charles,
Thanks that worked great, just one problem,
There is 4 possibilities. it can either be red, green, yellow or blue..
I tried this:
=iif( Fields!Shift.Value = "Green", "LawnGreen", "transparent")
else
=iif( Fields!Shift.Value = "Red", "Red", "transparent")
else
=iif( Fields!Shift.Value = "Yellow", "Yellow", "transparent")
else
=iif( Fields!Shift.Value = "Blue", "Blue", "transparent")
endif
but that don't quite help it. Any ideas?
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:4C2D7CCD-E8CB-4A5C-A7D9-9E2530CABA44@.microsoft.com...
> use the iif function for the font color. Reporting Services accepts
> expressions for most of its properties.
> =iif(boolean condition, "red", "black")
> HTH
> Charles Kangai, MCT, MCDBA
> "Rudi Groenewald" wrote:
>> Hi all
>> Does anyone know how I can have conditional formatting in my reports?
>> What I want to achieve is something like. If the field text = "Green"
>> make
>> the background green, etc
>> Thanks alot in advance
>> R
>>|||IIF is a function, not a procedural statement. Therefore you cannot use the
syntax you are trying to use. Is your logic something like this: if the value
of the Shift textbox is "green", "blue", "red" or "yellow", make the color of
this the same, otherwise make it transparent? if so, you need
=iif(Fields!Shift.Value="green" or Fields!Shift.Value = "blue" or
Fields!Shift.Value = "red" or Fields!Shift.Value = "yellow",
Fields!Shift.Value, "transparent")
or are you trying to make a distinction between green and lawngreen, in
which case you could nest a second iif function like so:
=iif(Fields!Shift.Value = "blue" or Fields!Shift.Value = "red" or
Fields!Shift.Value = "yellow", Fields!Shift.Value, iif(Fields!Shift.Value ="green", "lawngreen", "transparent"))
both of the above should work.
HTH
Charles Kangai, MCT, MCDBA
"Rudi Groenewald" wrote:
> Hi charles,
> Thanks that worked great, just one problem,
> There is 4 possibilities. it can either be red, green, yellow or blue..
> I tried this:
> =iif( Fields!Shift.Value = "Green", "LawnGreen", "transparent")
> else
> =iif( Fields!Shift.Value = "Red", "Red", "transparent")
> else
> =iif( Fields!Shift.Value = "Yellow", "Yellow", "transparent")
> else
> =iif( Fields!Shift.Value = "Blue", "Blue", "transparent")
> endif
> but that don't quite help it. Any ideas?
> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
> news:4C2D7CCD-E8CB-4A5C-A7D9-9E2530CABA44@.microsoft.com...
> > use the iif function for the font color. Reporting Services accepts
> > expressions for most of its properties.
> >
> > =iif(boolean condition, "red", "black")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Rudi Groenewald" wrote:
> >
> >> Hi all
> >>
> >> Does anyone know how I can have conditional formatting in my reports?
> >>
> >> What I want to achieve is something like. If the field text = "Green"
> >> make
> >> the background green, etc
> >>
> >> Thanks alot in advance
> >>
> >> R
> >>
> >>
> >>
>
>|||Hi Charles,
I figured it out, it was kind of simple really, just had to click. The
field "shift" only displays the words "red", "blue", "green" or "yellow".
So if that is the only words which are displayed, then obviously the
expression to be used as the background is =Fields!Shift.value
but your input made me click, thanks alot
Regards
R
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:CCA10812-58FB-43BD-B8FD-4C5B2BABC5AE@.microsoft.com...
> IIF is a function, not a procedural statement. Therefore you cannot use
> the
> syntax you are trying to use. Is your logic something like this: if the
> value
> of the Shift textbox is "green", "blue", "red" or "yellow", make the color
> of
> this the same, otherwise make it transparent? if so, you need
> =iif(Fields!Shift.Value="green" or Fields!Shift.Value = "blue" or
> Fields!Shift.Value = "red" or Fields!Shift.Value = "yellow",
> Fields!Shift.Value, "transparent")
> or are you trying to make a distinction between green and lawngreen, in
> which case you could nest a second iif function like so:
> =iif(Fields!Shift.Value = "blue" or Fields!Shift.Value = "red" or
> Fields!Shift.Value = "yellow", Fields!Shift.Value, iif(Fields!Shift.Value
> => "green", "lawngreen", "transparent"))
> both of the above should work.
> HTH
> Charles Kangai, MCT, MCDBA
> "Rudi Groenewald" wrote:
>> Hi charles,
>> Thanks that worked great, just one problem,
>> There is 4 possibilities. it can either be red, green, yellow or blue..
>> I tried this:
>> =iif( Fields!Shift.Value = "Green", "LawnGreen", "transparent")
>> else
>> =iif( Fields!Shift.Value = "Red", "Red", "transparent")
>> else
>> =iif( Fields!Shift.Value = "Yellow", "Yellow", "transparent")
>> else
>> =iif( Fields!Shift.Value = "Blue", "Blue", "transparent")
>> endif
>> but that don't quite help it. Any ideas?
>> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in
>> message
>> news:4C2D7CCD-E8CB-4A5C-A7D9-9E2530CABA44@.microsoft.com...
>> > use the iif function for the font color. Reporting Services accepts
>> > expressions for most of its properties.
>> >
>> > =iif(boolean condition, "red", "black")
>> >
>> > HTH
>> >
>> > Charles Kangai, MCT, MCDBA
>> >
>> > "Rudi Groenewald" wrote:
>> >
>> >> Hi all
>> >>
>> >> Does anyone know how I can have conditional formatting in my reports?
>> >>
>> >> What I want to achieve is something like. If the field text = "Green"
>> >> make
>> >> the background green, etc
>> >>
>> >> Thanks alot in advance
>> >>
>> >> R
>> >>
>> >>
>> >>
>>

Conditional expressions for background color

I am trying to use conditional expressions within a report field to change
the background color depending on the value of one of the fields in the
report.
Within the properties for one of the report items, I click the background
color item and then choose the expressions and that brings up an edit
expression window. I key the following code into the expression pane.
=iff(fields!libseq.value > 2, "Red", "Blue")
when I rebuild the solution or try to run the report, I get the following
message.
C:\VS Test Applications\Report Testing\Report4.rdl The background color
expression for the textbox 'textbox10' contains an error: [BC30451] Name
'iff' is not declared.
Does anyone know what needs to be declared and where?
thanks,
hughIt shoud be IIf and not iff
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:uv82bHbgGHA.4144@.TK2MSFTNGP02.phx.gbl...
>I am trying to use conditional expressions within a report field to change
>the background color depending on the value of one of the fields in the
>report.
> Within the properties for one of the report items, I click the background
> color item and then choose the expressions and that brings up an edit
> expression window. I key the following code into the expression pane.
> =iff(fields!libseq.value > 2, "Red", "Blue")
> when I rebuild the solution or try to run the report, I get the following
> message.
> C:\VS Test Applications\Report Testing\Report4.rdl The background color
> expression for the textbox 'textbox10' contains an error: [BC30451] Name
> 'iff' is not declared.
> Does anyone know what needs to be declared and where?
> thanks,
> hugh
>|||Hi Hugh,
Thank you for your post.
As Jasper mentioned, the function you use should be Iif not Iff.
Iif is a Visual Basic Fuction. Here is an article for your reference.
Expression Examples in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
If you have questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Conditional Expression

I have an expression that looks like
this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
The field returns fine, but I want negative values to be returned as 0 when
the value is <=0.
Any ideas on how to do this? Would I add a IIF to the expression? Any help
is appreciated.
Thanks!
RyanOn May 24, 1:19 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I have an expression that looks like
> this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
> The field returns fine, but I want negative values to be returned as 0 when
> the value is <=0.
> Any ideas on how to do this? Would I add a IIF to the expression? Any help
> is appreciated.
> Thanks!
> Ryan
This should work.
=iif(Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!
QTYONORD.Value <= 0, 0, Fields!Quantity.Value-Fields!
QuantityAvailable.Value-Fields!QTYONORD.Value)
Regards,
Enrique Martinez
Sr. Software Consultant