Alert User of Invalid Parameter Entry

Does anyone know if there is a way to alert the user that the parameter
value is invalid prior to the report rendering?
One of the parameters is an account number '########'. The user is
accustomed to seeing it as '###-####-#' and may enter it in that
format. I've already put the usual '(With no dashes)' next to the
prompt. However, if they do put dashes, (or some other invalid format),
the report runs as normal and spits out a blank report.
I would like the user to be aware of invalid parameter entries, similar
to a web app, or windows form.
TIA.On Dec 21, 11:06 am, Michael <Mich...@.discussions.microsoft.com>
wrote:
> Does anyone know if there is a way to alert the user that the parameter
> value is invalid prior to the report rendering?
> One of the parameters is an account number '########'. The user is
> accustomed to seeing it as '###-####-#' and may enter it in that
> format. I've already put the usual '(With no dashes)' next to the
> prompt. However, if they do put dashes, (or some other invalid format),
> the report runs as normal and spits out a blank report.
> I would like the user to be aware of invalid parameter entries, similar
> to a web app, or windows form.
> TIA.
There are a couple different things you can do. Either you can add a
textbox control to the report and set it to an expression like:
=iif(InStr(Parameters!AcctNumber.Value, "-") > 0, "Invalid Account
Number", Nothing)
-or-
You can do a replace on the Acct Number parameter entered by the user
via an expression in your dataset. An expression like this might help.
=Replace(Parameters!AcctNumber.Value, "-", "")
and you can embed them in each other to catch other stray characters
(i.e., remove all hyphens and asterisks)
=Replace(Replace(Parameters!AcctNumber.Value, "-", ""), "*", "")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Aggregate problem

I am getting the following error in my SQL.
Column 'dbo.ClientWorkerStatus.WorkerTypeID' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
My code is below. What I want to get is up to 1, 2 or 3 counts for one
ClientID as there are multiples in the ClientWorkerStatus table.
SELECT dbo.ClientWorkerStatus.ClientID,
Preferred = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 0 THEN 1
ELSE 0
END,
Family = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 1 THEN 1
ELSE 0
END,
Pool = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 2 THEN 1
ELSE 0
END
FROM dbo.ClientWorkerStatus INNER JOIN
dbo.ClientWorkerLink ON dbo.ClientWorkerStatus.ClientID =
dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID = dbo.ClientWorkerLink.WorkerID
WHERE (dbo.ClientWorkerStatus.StatusEnd IS NULL OR
dbo.ClientWorkerStatus.StatusEnd > GETDATE()) AND
(dbo.ClientWorkerLink.Active = 1)
GROUP BY dbo.ClientWorkerStatus.ClientID
Thanks, DavidWhat aggregate?
Have you tried something like:
SELECT s.ClientID,
Preferred = CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
Family = CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
Pool = CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
FROM
dbo.ClientWorkerStatus s
INNER JOIN dbo.ClientWorkerLink l
ON s.ClientID = l.ClientID
AND s.WorkerID = l.WorkerID
WHERE
COALESCE(s.StatusEnd, '20300101') > GETDATE()
AND l.Active = 1
GROUP BY
s.ClientID,
CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
Without DDL, sample data and desired results, this is only a guess. See
http://www.aspfaq.com/5006 for info on providing details that will yield a
full solution.|||Hello, David
Is the ClientI=ADD column the primary key (or a unique key) in the
ClientWorkerStatus table ? If yes, you can safely add the
WorkerT=ADypeID column to the GROUP BY list, i.e:
SELECT [...]
GROUP BY dbo.ClientWorkerStatus.ClientI=ADD,
dbo.ClientWorkerStatus.WorkerT=ADypeID=20
Razvan|||Erase your group by at the end of your query.
You don't have any sum, avg, .... in your query. The group by clause is not
required.
Jonathan
"Razvan Socol" wrote:

> Hello, David
> Is the ClientI_D column the primary key (or a unique key) in the
> ClientWorkerStatus table ? If yes, you can safely add the
> WorkerT_ypeID column to the GROUP BY list, i.e:
> SELECT [...]
> GROUP BY dbo.ClientWorkerStatus.ClientI_D,
> dbo.ClientWorkerStatus.WorkerT_ypeID
> Razvan
>|||Yes, Aaron...your's worked. However I was also able to get it to work
by surrounding the CASE statements in the SELECT clause by SUM(...).
For example:
Preferred = SUM(CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END), ...etc
I do want to group the results by ClientID because any one ClientID can
have more than 1 WorkerTypeID so I need the count of each.
Thanks.
David
*** Sent via Developersdex http://www.examnotes.net ***|||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.
You have absolutely no idea how to design a schema, do you?
You have columns named "-type_id" which make no sense. Look at the
ISO-11179 Standards.
You have table names that end with "-link", as if you were writing a
1970's navigation database.
You have table of status codes when status is a kind of attribute, not
an entity.
Then after all those FUNDAMENTAL errors, you do not have any aggregates
in the useless crap you posted.
dbo.ClientWorkerStatus.StatusE=ADnd > GETDATE()) <<
You might want to get enough basic SQL programming skills to write this
as:
COALESCE (foo_status_end_date, CURRENT_TIMESTAMP) >=3D CURRENT_TIMESTAMP
There is no such thing as a magical, universal "status": -- it is the
status of **something**. That getdate() is a proprietary syntax which
good SQL programmers avoid like lice. Learn the options in the
language so you can write them in SQL and not in some 3GL-style syntax.