How to validate Report Parameters

Hi,

I am designing a report using SRS 2000. I want to validate parameters before executing the query. Suppose if user gives a random number for parameter (say Emp ID), i want to display a error message in a textbox "There are no records for the given employee ID" and stop executing query to display employee details in the table.

I tried using Visible expression for table IIF(Fields!EmpID = "", False, True). But when I run the report with a random number, an error populated saying "You cannot query a record for no data..."

Please suggest me a work around.

Regards,

One workaround may be to use a stored procedure and perform validation within it. Return a result set that provides either a data result or error values within your dataset. Use visibility expressions on the reporting services to show-hide either the table that returns the data or the table that returns the error message.

eg.

if @.myparam = 'bad'

select 'bad' as column1

else

select column1 from mytable

then you do a check for the first value of column1. If it's bad show the error table, otherwise show the regular table.

cheers,

Andrew

|||

Hi Chiro,

Here's how it works.

In your table properties, locate the NoRows properties of the table. Place your message.

Note: If you use this you cannot change the font color because reporting services does have a color property for this.

or

Try this

Add a Rectangle into the body of your report

In the Visibility property of the Rectangle, locate the Hidden and click expression.

Place this in your Rectangle

=iif( Sum(Fields!EmpID.Value, "Your Dataset")>0, False, True)

Then try running the report.

|||

Thanks for the reply. I managed it by using 2 datasets, 1 for validation parameters and 1 for getting data. I dont know why the above method which u have suggested is throwing error "You cannot fetch records for no rows or some thing..." when i try entering some junk characters like "23423daedfsrewAFSD".

Any way. Thanks for the reply.

Regards

How to Validate Parameters

Hi,

I am designing a report using SRS 2000. I want to validate parameters before executing the query. Suppose if user gives a random number for parameter (say Emp ID), i want to display a error message in a textbox "There are no records for the given employee ID" and stop executing query to display employee details in the table.

I tried using Visible expression for table IIF(Fields!EmpID = "", False, True). But when I run the report with a random number, an error populated saying "You cannot query a record for no data..."

Please suggest me a work around.

Regards,

In any data control you can set the NoRows property which will display a defined message (in the property) instead of the data control.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thank you. It is working for results with no rows but not for invalid rows. ie., if we search for employee details, the employees with no details "No Rows" concept is working, but for employees who doesnt exists, it is not working.

I even tried IF @.@.COUNT = 0 THEN RETURN -3.

But I am not sure how to catch that return value ?

|||

Hi,

from your description you retrieve all data within one dataset, right ? So there are two approaches achieve your goal as the NoRows cannot be tweaked to use the return value as you described.

1. Use two datasets to retrieve first the parent data and afterwards the child data (what you described as details)
2. Place a textbox behind the table for your details with label like "No rows/details found". Check the dataset for the expressions which makes you sure that the datagrid (or whatever data control you are using) and use this expression for toggeling the visibility or the data control, making the control visible which has been in the background.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de