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

Conditional Matrix Subtotals

Let's say I have raw data that looks something like this

Fruit_name status count
apples Fresh 5
apples rotten 3
pears Fresh 3
pears rotten 2

and I was to matrix it and group on fruit_name and add a subtotal to count. In that subtotal, without changing what was displayed in the details number, could I conditionaly only show a sum of fresh fruit? Example below

Apples 5
Apples 3
-
Total 5

Pears 3
Pears 2
-
Total 3

in a nonmatrix situation I'd use something like

sum(iif(status="Fresh",count,0))

Thanks for the help.Anyone have any ideas? Or is it not possible to have have a conditional summation with components that aren't displayed in the matrix,|||

Hello,

Instead of doing this subtotal at the report level, can you try using the "RollUp" operator in the SQL statement..

Example :

select fruit_name, status, sum(Qty)
from tables........
group by fruit_name, status with rollup

DataResult:

Apples Fresh 5

Apples Rotten 3

Apples NULL 8

Pears Fresh 3

Pears Rotten 2

Pears NULL 5

NULL NULL 13

(you can replace NULL with ALL using the Grouping function )

I may not be answering to your question.. but maybe this will help you..

Thanks!

|||I considered making the summation another row of the dataset instead of calculating it in the report but I was hoping to have the matrix do it.

I'll probably use the sql pivot function to pivot the data before it hits the report and use a table instead of a matrix.

Reporting Services is a decent product but it's missing some very basic features.

Conditional Count for Row Group together with Column Group

Division =Fields!Status.Value
Total
=Fields!Division.Value
=Count(iif("Status"=Fields!Status.Value,1,0),"Division")
Total:
the above is the matrix that i had done to count some values. The
"=Fields!Status.Value" is a column group call Status and the
"=Fields!Division.Value" is a row group call Division. My column goup
contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
group contais all my division fields. My database had a record of all the
submissions that had been submitted by the different divisions and what is
their submission current status which is either Accepted, Rejected or Pending.
Now what i want to achieved is that i want to display the Number of
Accepted, Rejected and Pending submissions for each Division by using the
Count function. But the expression that i had did does not give me the actual
result. i need some help on what correct expression should i use.
Thansk in advance for all the help and time consumed.ok, managed to solve it with a simple expression =Count(Fields!Status.Value)
Thanks anyway. =)
"JiaN" wrote:
> Division =Fields!Status.Value
> Total
> =Fields!Division.Value
> =Count(iif("Status"=Fields!Status.Value,1,0),"Division")
> Total:
> the above is the matrix that i had done to count some values. The
> "=Fields!Status.Value" is a column group call Status and the
> "=Fields!Division.Value" is a row group call Division. My column goup
> contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
> group contais all my division fields. My database had a record of all the
> submissions that had been submitted by the different divisions and what is
> their submission current status which is either Accepted, Rejected or Pending.
> Now what i want to achieved is that i want to display the Number of
> Accepted, Rejected and Pending submissions for each Division by using the
> Count function. But the expression that i had did does not give me the actual
> result. i need some help on what correct expression should i use.
> Thansk in advance for all the help and time consumed.