Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>
|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas

"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
sqlsql

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

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

Conditional Where Clause

Hi - I am writing a C# program using SQL Server. The form I have is
collecting search criteria for a database. The main 3 fields are Category,
Type, Author.
Any combination of the 3 fields can be used. That is, All 3 fields can be
used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
can't use the 3rd field in the where clause of the query.
How can I create a generic query and pass a string for the "where" clause
instead of creating 7 specific queries for each possible combination of
search criteria.
Thanks,
SarahCREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL,
@.bookType VARCHAR(10) = NULL,
@.author VARCHAR(10) = NULL
AS
SELECT category,
booktype,
author
FROM book
WHERE (category = @.category OR @.category IS NULL )
AND (booktype = @.bookType OR @.bookType IS NULL )
AND (author = @.author OR @.author IS NULL )|||Sarah,
From what you say is the following correct:
You want a Stored Procedure that takes 3 parameters and returns a recordset
based on the passed parameters.
The SELECT statement itself will be static, and both the first two params
will be used if present, and the third [arameter will only be used if neithe
r
of the first two params are present.
Does that sum it up?
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||Sarah,
Bearing in mind the mutual exclusivity between params 1,2 and param 3, the
following code will work:::
CREATE STORED PROCEDURE [dbo].[usp_GetSearchResults]
@.Category varchar(100)='',
@.Type varchar(100)='',
@.Author varchar(100)
AS
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
IF @.Category ='' AND @.Type =''
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Author ='' OR Author=' + CHAR(39) + @.Author +
CHAR(39) + ') '
END
ELSE
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Type ='' OR Type=' + CHAR(39) + @.Type +
CHAR(39) + ') '
END
EXEC (@.sSQL)
You do not necessarily need the character string to create the select
statement, it does help with debugging though.
Hope it helps,
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||KenJ - thanks - this logic will work, but I am getting a syntax error:
"Duplicated parameter names are not allowed"
when I try to do this in Query builder. Any idea what would cause this erro
r.
Thanks,
Sarah
"KenJ" wrote:

> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL,
> @.bookType VARCHAR(10) = NULL,
> @.author VARCHAR(10) = NULL
> AS
> SELECT category,
> booktype,
> author
> FROM book
> WHERE (category = @.category OR @.category IS NULL )
> AND (booktype = @.bookType OR @.bookType IS NULL )
> AND (author = @.author OR @.author IS NULL )
>|||I'm not familiar with query builder. Can you run it in query analyzer?
Here is a sample script that creates a table, loads some dummy data,
runs the procedure with several variations then drops the table and
procedure. I've run it in query analyzer to be sure it works...
USE tempdb
GO
SET nocount ON
GO
CREATE TABLE book (
bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
, category VARCHAR(10) NULL
, booktype VARCHAR(10) NULL
, author VARCHAR(10) NULL)
GO
INSERT book
VALUES('fiction'
, 'paperback'
, 'twain')
INSERT book
VALUES('fiction'
, 'hardbound'
, 'asimov')
INSERT book
VALUES('fiction'
, 'paperback'
, 'rand')
GO
CREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL
, @.bookType VARCHAR(10) = NULL
, @.author VARCHAR(10) = NULL
AS
SELECT category
, booktype
, author
FROM book
WHERE (category = @.category
OR @.category IS NULL )
AND (booktype = @.bookType
OR @.bookType IS NULL )
AND (author = @.author
OR @.author IS NULL )
GO
-- get all fiction books
EXEC getbook @.category = 'fiction'
-- all fiction books by rand
EXEC getbook @.category = 'fiction' ,
@.author = 'rand'
-- all paperbacks
EXEC getbook @.bookType = 'paperback'
-- returns all books since we don't supply any filter
EXEC getbook
GO
DROP TABLE book
GO
DROP PROCEDURE getbook
GO|||On Wed, 1 Feb 2006 16:50:27 -0800, Sarah Sarah wrote:

>Hi - I am writing a C# program using SQL Server. The form I have is
>collecting search criteria for a database. The main 3 fields are Category,
>Type, Author.
>Any combination of the 3 fields can be used. That is, All 3 fields can be
>used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
>can't use the 3rd field in the where clause of the query.
>How can I create a generic query and pass a string for the "where" clause
>instead of creating 7 specific queries for each possible combination of
>search criteria.
Hi Sarah,
Many ways to skin this cat can be found i Erland Sommarskog's article:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
It would also help if you would learn that a field and a column nothing
whatsoever alike and that names like "type", "category", etc. are too
vague to be data element names/. Try something like this:
CREATE PROCEDURE GetBook
(@.my_book_category VARCHAR(10) = NULL, -- wild guess
@.my_book_type INTEGER = NULL, -- Dewey Decimal ?
@.my_author_name VARCHAR(25) = NULL)
AS
SELECT book_category, book_type, author_name
FROM Library
WHERE book_category = COALESCE (@.my_book_category, book_category)
AND book_type = COALESCE (@.my_book_type, book_type)
AND author_name = COALESCE (@.my_author_name, author_)name) ;|||> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
That would give a tablescan.
Can you imagine how badly that will perform on a table with a few million
rows perhaps 1GB in size.
To do the tablescan everytime a user ran the query SQL Server would have to
read 1GB of data.
Now, multiply that by 10 users, thats 10GB of data SQL Server now needs to
read in order to process all 10 queries.
You are going to need one hell of a big box!
The correct way to do this is to either use IF..ELSE to make the query more
specific depending on which parameters are specified, ie. only put the
parameters specified on the WHERE clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138944895.900338.14850@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> It would also help if you would learn that a field and a column nothing
> whatsoever alike and that names like "type", "category", etc. are too
> vague to be data element names/. Try something like this:
> CREATE PROCEDURE GetBook
> (@.my_book_category VARCHAR(10) = NULL, -- wild guess
> @.my_book_type INTEGER = NULL, -- Dewey Decimal ?
> @.my_author_name VARCHAR(25) = NULL)
> AS
> SELECT book_category, book_type, author_name
> FROM Library
> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
>|||Don't forget Ken that the query below will give you a very general plan so
you'll probably end up doing a table scan.
Check the plan before you decided on the solution.
Much better to use IF ELSE or dynamic SQL and taylor your query to the
parameters passed.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"KenJ" <kenjohnson@.hotmail.com> wrote in message
news:1138899994.839100.3010@.o13g2000cwo.googlegroups.com...
> I'm not familiar with query builder. Can you run it in query analyzer?
> Here is a sample script that creates a table, loads some dummy data,
> runs the procedure with several variations then drops the table and
> procedure. I've run it in query analyzer to be sure it works...
> USE tempdb
> GO
> SET nocount ON
> GO
> CREATE TABLE book (
> bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
> , category VARCHAR(10) NULL
> , booktype VARCHAR(10) NULL
> , author VARCHAR(10) NULL)
> GO
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'twain')
> INSERT book
> VALUES('fiction'
> , 'hardbound'
> , 'asimov')
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'rand')
> GO
> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL
> , @.bookType VARCHAR(10) = NULL
> , @.author VARCHAR(10) = NULL
> AS
> SELECT category
> , booktype
> , author
> FROM book
> WHERE (category = @.category
> OR @.category IS NULL )
> AND (booktype = @.bookType
> OR @.bookType IS NULL )
> AND (author = @.author
> OR @.author IS NULL )
> GO
> -- get all fiction books
> EXEC getbook @.category = 'fiction'
> -- all fiction books by rand
> EXEC getbook @.category = 'fiction' ,
> @.author = 'rand'
> -- all paperbacks
> EXEC getbook @.bookType = 'paperback'
> -- returns all books since we don't supply any filter
> EXEC getbook
> GO
> DROP TABLE book
> GO
> DROP PROCEDURE getbook
> GO
>

Conditional Trigger

I am trying to write a trigger that will only fire when certain fields are updated. I can't seem to find any threads that relate to this issue. Below is the trigger syntax. Any info would be great. Thx

CREATE TRIGGER tr_feedback_date ON Calls
FOR UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if update (feedback) or update (analyst) or update (coordinator) or update (technical) or update (fixtime)
or update (phonewait) or update (resp)
UPDATE hold_complete
set hold_complete.feedbackdt = getdate()
from inserted with (nolock)
where hold_complete.fkey = inserted.callidThe create trigger statement can take an IF UPDATE ( column ) clause. You can read about it in Books Online.

Another option it to compare the values in the INSERTED table to the values in the DELETED table. Since updates involve both insertions and deletions, your affected record will exist in both and you can compare the differences based on the primary key.

blindman|||Also, there is very useful function COLUMNS_UPDATED() which you can use in trigger for checking what fields were updated.

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 split problem

I have a dataflow where i transfer data from textfile to oledb destination

I have a conditional split in between and check if incoming fields are empty.

in the conditional split i have

ISNULL(column1)|| ISNULL(column2) || ISNULL(column3)|| ISNULL(column4) ||ISNULL(5) || column1 == " " || column2 == ""||column3 == " " || column4== " "||column5==" "

this is what i have in my conditional split to check if they are blank.

it dosent show them as blank at all..

what am i doing wrong?

Are you looking at the wrong output from your Conditional Split?

-Jamie

|||

What is the precise definition of blank for your columns? From the expression, it looks like the answer is NULL or empty string or possible one space?

If blank can also mean "just spaces", one thing i can suggest is instead of

columnX == " "

try something like the following, which works for any number of "just spaces":

LEN(TRIM(columnX) == 0)

Mark

conditional query

Hi there!

I'm struggling to write the correct SQL to do the following task:

there are 3 fields in a table: SSize1, SSize2, SSize3.
the data type of all three is integer.
i need to do a simple calculation to determine SSize based on the following condition:
if SSize3 <>0 then
SSize = SSize1 + (SSize2/SSize3)
else SSize = SSize1
end if

can anyone please help me? thanks in advance!

regards
leeDoes your SQL dialect support the CASE operator? That would be how I'd approach solving your problem.

-PatP|||do you want to get the SSize per each row?
If so, I think you have to store these 3 fields first in a temp table. Then use a cursor, or something like that,for your calculations to compute SSize per row.

If the SSize would get the total of these 3 fields, get first the sum for each of these fields. Placed it in integer variables, then proceed to your calculations. Try this

select @.SSize1 = sum(SSize1), @.SSize2 = sum(SSize2), @.SSize3 = sum(SSize3)
from table_name

if @.SSize3 <> 0 then
SSize = @.SSize1 + (@.SSize2/@.SSize3)
else
SSize = @.SSize1
end if

hope this would work on you :)|||Or, perhaps,

SELECT DECODE(SSize3, 0, SSize1, SSize1 + (SSize2 / SSize3)) SSize
FROM table_name;

conditional logic in stored procedure

Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @.switch = "B"
SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3
end

Thanks for any help or information with this.>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@.ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@.ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@.ins3, '')
AND ikbbody LIKE COALESCE(@.ins1, ikbbody)
AND ikbbody LIKE COALESCE(@.ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@.ins3,'')|||Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis

select @.ins1=isnull(@.ins1,'%')
select @.ins2=isnull(@.ins2,'%')
select @.ins3=isnull(@.ins3,'%')

SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3

Conditional Line Breaks

There has to be a simple solution to this, but I can't find one. I have several fields dropped onto a report which display the relevant company address for that customer. But, some of the addresses don't have the full itinary, some may not have state or country where other may have. How can I implement a conditional statement which will remove remove the line break if the content is present. Or vice versa if no content is there, how can I not display that field?

Thanks in advanceIn the suppress event write this

If IsNull({filed}) then True;

Conditional Join

I want to join 2 tables conditionally. One order needs to join with one
instruction. The three potential join fields are: Country, Exchange, and
Type. These fields are required in the Orders table but only Country is
required in Instructions. The data entry requirements of the application are
such that if an instruction has a Type it must have an Exchange.
The logic of the join is that:
1-if all three fields match, Country, Exchange, and Type then join those
records.
2-if two fields match, Country, and Exchange then join those records.
3-if one field matches, Country, then join those records.
My expected results given the sample data is as follows.
SELECT OrderID,InstructionID FROM Orders
JOIN ...
--Expected Results
OrderID,InstructionID
1,1
2,5
3,5
4,7
5,8
6,9
7,13
CREATE TABLE Orders
(
OrderID int NOT NULL,
Country char (3)NOT NULL,
Exchange char (3)NOT NULL,
Type char (3)NOT NULL,
)
CREATE TABLE Instructions
(
InstructionID int NOT NULL,
Country char (3) NOT NULL,
Exchange char (3) NULL,
Type char (3) NULL,
Instructions varchar (15)NOT NULL
)
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(1,'USA','NYS','Buy','Instruction 1')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(2,'USA','NYS','Sel','Instruction 2')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(3,'USA','NYS',NULL,'Instruction 3')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(4,'USA',NULL,NULL,'Instruction 4')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(5,'CAN','TSE',NULL,'Instruction 5')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(6,'CAN','ALB',NULL,'Instruction 6')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(7,'ESP',NULL,NULL,'Instruction 7')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(8,'ESP','MAD',NULL,'Instruction 8')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(9,'IRQ','BAG','Buy','Instruction 9')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(10,'IRQ','BAG','Sel','Instruction 10 ')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(11,'DUE',NULL,NULL,'Instruction 11')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(12,'DUE','HAM',NULL,'Instruction 12')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(13,'DUE','HAM','Buy','Instruction 13')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(14,'DUE','HAM','Sel','Instruction 14')Terri (terri@.cybernets.com) writes:
> I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
Thanks a lot for table and test data. This may not be the smartest
query, but it's easy to understand:
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange )
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange
AND O.Type = I2.Type)
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
AND O.Type = I.Type
ORDER BY OrderID
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I want to join 2 tables conditionally. <<
That makes NO sense as you have to join or not join a table.
Okay.
Gee, too bad that SQL and RDBMS has columns and not fields; rows are
not records. They are nothing alike in concept or execution. Also,
"type" is too vague to be a valid column name - "type" of
what? This is basics, damn it!!
xchange.
The logic of the join is that:
1-if all three fields [sic] match, Country, Exchange, and Type [sic]
then join those records [sic].
2-if two fields [sic] match, Country, and Exchange then join those
records [sic]. <
3-if one field [sic] matches, Country, then join those records [sic].
<<
Okay, try this:
SELECT ..
FROM Foo, Bar, etc.
WHERE CASE WHEN Foo.country_code= Bar.country_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.exchange_code = Bar.exchange_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.vague_type =Bar.vague_type
THEN 1 ELSE 0 END > 1 ;|||Terri
CREATE VIEW myView
AS
SELECT
OrderID, InstructionID,
I.Country ,I.Exchange ,I.Type FROM Orders O
JOIN Instructions I ON O.Country=COALESCE(I.Country,O.Country)
AND O.Exchange=COALESCE(I.Exchange,O.Exchange) AND
O.Type=COALESCE(I.Type,O.Type)
WHERE I.Exchange IS NOT NULL
--Final Select
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE Type IS NOT NULL
UNION
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE (SELECT COUNT(*) AS dp
FROM myView V WHERE OrderID=myView.OrderID ) =1
"Terri" <terri@.cybernets.com> wrote in message
news:dr3mjc$r6g$1@.reader2.nmix.net...
>I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are
> such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
> SELECT OrderID,InstructionID FROM Orders
> JOIN ...
>
> --Expected Results
> OrderID,InstructionID
> 1,1
> 2,5
> 3,5
> 4,7
> 5,8
> 6,9
> 7,13
> CREATE TABLE Orders
> (
> OrderID int NOT NULL,
> Country char (3)NOT NULL,
> Exchange char (3)NOT NULL,
> Type char (3)NOT NULL,
> )
> CREATE TABLE Instructions
> (
> InstructionID int NOT NULL,
> Country char (3) NOT NULL,
> Exchange char (3) NULL,
> Type char (3) NULL,
> Instructions varchar (15)NOT NULL
> )
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (1,'USA','NYS','Buy','Instruction 1')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (2,'USA','NYS','Sel','Instruction 2')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (3,'USA','NYS',NULL,'Instruction 3')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (4,'USA',NULL,NULL,'Instruction 4')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (5,'CAN','TSE',NULL,'Instruction 5')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (6,'CAN','ALB',NULL,'Instruction 6')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (7,'ESP',NULL,NULL,'Instruction 7')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (8,'ESP','MAD',NULL,'Instruction 8')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (9,'IRQ','BAG','Buy','Instruction 9')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (10,'IRQ','BAG','Sel','Instruction 10 ')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (11,'DUE',NULL,NULL,'Instruction 11')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (12,'DUE','HAM',NULL,'Instruction 12')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (13,'DUE','HAM','Buy','Instruction 13')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (14,'DUE','HAM','Sel','Instruction 14')
>
>

Conditional join

Given 2 tables, Transactions and Instructions, my goal is to join a
transaction to its appropriate instruction. The 3 fields I can possibly join
on are: Exchange, SecType, and Country. All of these fields are nullable
in the instruction table. Not my design, I can't change this.
How can I join these tables so that any null values are disregarded. Given
my sample data I want to join the following records.
TransactionID,InstructionID
1,1
2,4
3,7
4,10
CREATE TABLE [dbo].[#Transactions] (
[TransactionID] [int] NOT NULL ,
[Exchange] [char] (3) NOT NULL ,
[SecType] [char] (4) NOT NULL ,
[Country] [char] (3) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(1,'NYS','COM','USA')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(2,'LSE','COM','GBR')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(3,'TSE','ADR','CAN')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(4,'NAS','COM','USA')
CREATE TABLE [dbo].[#Instructions] (
[TransactionID] [int] NOT NULL ,
[Exchange] [char] (3)NOT NULL ,
[SecType] [char] (4),
[Country] [char] (3),
[Instruction][char] (25)
) ON [PRIMARY]
GO
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(1,'NYS',NULL,NULL,'Instruction#1')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(2,'NYS','ADR',NULL,'Instruction#2')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(3,'LSE','ADR','GBR','Instruction#3')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(4,'LSE','COM',NULL,'Instruction#4')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(5,'LSE','COM','IRL','Instruction#5')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(6,'TSE','COM','CAN','Instruction#6')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(7,'TSE','ADR','CAN','Instruction#7')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(8,'TSE',NULL,NULL,'Instruction#8')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(9,'NAS',NULL,NULL,'Instruction#9')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(10,'NAS',NULL,'USA','Instruction#10')Terri wrote:
> Given 2 tables, Transactions and Instructions, my goal is to join a
> transaction to its appropriate instruction. The 3 fields I can possibly jo
in
> on are: Exchange, SecType, and Country. All of these fields are nullable
> in the instruction table. Not my design, I can't change this.
> How can I join these tables so that any null values are disregarded. Given
> my sample data I want to join the following records.
> TransactionID,InstructionID
> 1,1
> 2,4
> 3,7
> 4,10
> CREATE TABLE [dbo].[#Transactions] (
> [TransactionID] [int] NOT NULL ,
> [Exchange] [char] (3) NOT NULL ,
> [SecType] [char] (4) NOT NULL ,
> [Country] [char] (3) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (1,'NYS','COM','USA')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (2,'LSE','COM','GBR')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (3,'TSE','ADR','CAN')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (4,'NAS','COM','USA')
> CREATE TABLE [dbo].[#Instructions] (
> [TransactionID] [int] NOT NULL ,
> [Exchange] [char] (3)NOT NULL ,
> [SecType] [char] (4),
> [Country] [char] (3),
> [Instruction][char] (25)
> ) ON [PRIMARY]
> GO
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (1,'NYS',NULL,NULL,'Instruction#1')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (2,'NYS','ADR',NULL,'Instruction#2')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (3,'LSE','ADR','GBR','Instruction#3')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (4,'LSE','COM',NULL,'Instruction#4')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (5,'LSE','COM','IRL','Instruction#5')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (6,'TSE','COM','CAN','Instruction#6')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (7,'TSE','ADR','CAN','Instruction#7')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (8,'TSE',NULL,NULL,'Instruction#8')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (9,'NAS',NULL,NULL,'Instruction#9')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (10,'NAS',NULL,'USA','Instruction#10')
As I expect you realise, both tables look like they badly need a
redesign. Meantime, try this:
SELECT T.transactionid,
COALESCE(I1.instructionid, I2.instructionid,
I3.instructionid, I4.instructionid)
FROM #transactions AS T
LEFT JOIN #instructions AS I1
ON T.exchange = I1.exchange
AND T.sectype = I1.sectype
AND T.country = I1.country
LEFT JOIN #instructions AS I2
ON I2.sectype IS NULL
AND T.exchange = I2.exchange
AND T.country = I2.country
LEFT JOIN #instructions AS I3
ON I3.country IS NULL
AND T.exchange = I3.exchange
AND T.sectype = I3.sectype
LEFT JOIN #instructions AS I4
ON I4.sectype IS NULL
AND I4.country IS NULL
AND T.exchange = I4.exchange ;
David Portas
SQL Server MVP
--

Conditional Formatting?

Is there the equivalent of MS Access 'conditional formatting' for fields in report services, if so how do I access it?

Thanks

You will need to use expressions - check the following links:

* http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

* http://defdeveloper.blogspot.com/2005/10/conditional-row-formatting-in.html

* http://msdn2.microsoft.com/en-us/library/ms159238.aspx

-- Robert

Conditional fields based on export type

I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).

I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.

Any help would be appreciated.

Jerry

Hi Jerry

I do not know if it is possible for PDF. I know you can limit what fields are exported for CSV and XML.
To do this open the properties box on the field you do not wish to export and click on the data output tab.
Then change the Output radio box from 'Auto' to 'No'

Cheers
Mark
|||

What I really need is the ability to disable the creation of the hyperlink when the report is exported.

I see now that the pageload method is not even fired at the time the choice to export is made. Therefore I will need to determine what program is being run upon this selection and be able to set the parameters of that program. As I see now this just may not exist.

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 Displaying

Hi all,

I am using Crystal reports XI R1 . Here is the problem, I have an integer field (x), i need to display three more fields (a,b,c) based on that integer field (x) when it is maximum.

for example
when max(x) then i need to display the corresponding records of a, b, and c.

Any help would be greatly appreciated.

Thanks in advance
Sudharsan.What is the database?
I think you need to write the query

Select * from table
where x=(select max(x) from table)

and design the report using that query

Condition validation on Crystal report Fields

Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

ThnaksIf the field was numeric in the DB, and you want to change the value of the field in some records to an alphanumeric ('Good') you gotta problem. Create another field.|||Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

Thnaks

you can't update field of your data base and which are used in
crystal report.

you would have create a formula to it.