Possible to stop report from running without a minimum length of combined parameter characters? 3

[JetRamsey]

I'm running CR version 14. I have a report that will return all records in a database if someone leaves all the parameters blank, which I don't want to happen. I have 10 parameter fields and if at least one of them is filled in, there should not be a problem with wasted processing time on the Server. Is there a way to stop a report from running if at least one parameter field is not used?

TIA

 

[pmax9999]

Assuming the parameters have been set to optional so they could be left blank (as opposed to them having a string type which will also allow them to be ommitted), add the following to the beginning of your record selection formula:

(
	Not(HasValue({?Param1}))  and
	Not(HasValue({?Param2}))  and
	Not(HasValue({?Param3}))  and
	Not(HasValue({?Param4}))  and
	Not(HasValue({?Param5}))  and
	Not(HasValue({?Param6}))  and
	Not(HasValue({?Param7}))  and
	Not(HasValue({?Param8}))  and
	Not(HasValue({?Param9}))  and
	Not(HasValue({?Param10})) and
	{table.some_field} = [something unlikely] 
)	and

For the last line, replace "{table.some_field}" with a column from the database and "[something unlikely]" with a value that will not appear. For example an ID field = 0, or a Date field = Date(1900, 1, 1).

You could also add a formula (placed in the report header) to return text explaining that no records were returned because no parameters had been entered.

The other alternative would be to use Alerts and test for all parameters having no value.

Hope this helps.

Cheers
Pete

 

[pmax9999]

Was just giving this some more thought.

The final "and" (after the closing bracket) should be an "or". Also, enclose the rest of the selection formula (ie, what you started with) in parentheses.

Pete

 

[RTag]

Crystal reports will try to convert a simple record selection formula to a WHERE clause and send it to the database. However if you are using specific crystal reports functions in the record selection formula it will be very likely that Crystal will send a flat SQL without a WHERE clause to the database, retrieve all the possible records and filter them locally evaluating the record selection formula for each of the records. This might be very slow process and as far as I understand is exactly what you are trying to avoid.
I would do what pmax999 is proposing , but using a command. The command will be executed on the database side and will always return filtered data.

A command for SQL SERVER and string parameters may look like this :

SELECT a,b,c ... FROM tablename
WHERE
1 = CASE WHEN {?parameter1}+{?parameter1}+...{?parameterN} ='' THEN 0 ELSE 1 END
AND
(
X like '%' + {?parameter1} '%'
OR Y like '%' + {?parameter2} '%'
OR ...
OR Z like '%' + {?parameterN} '%'
)

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[RTag]

If you already have the report working and it is developed using the database expert you will need to rewrite it to use a command. If you cannot rewrite it you can check this video for another way to handle the case:

http://www.r-tag.com/Pages/Preview_Demo.aspx

(from the menu in left select "Events" or jump directly to 8:30 min.)

The video shows how to use a 3rd party viewer to check parameters' values before to start the report. If the values are not correct you will be able to show a message and cancel the report execution.

The previous approach (with 1=0 clause) will hit the database and will return an empty rowset in case the parameters are empty. This approach will not even start the report. The advantage of the first one is that you can use it with any viewer. However it might require you to rewrite the report to use a command.
The advantage of the second one is that you can control parameter values very precisely: for example in other cases when the user inserts a period you can check if the second date is not before the first one etc. However this approach will require a 3rd party viewer.

If the 3rd party viewer is an acceptable solution and there is one user, which is using the report or the report is used on a terminal server you cane request a free license from this link :

http://www.r-tag.com/Pages/FreeCrystalReportsViewer.aspx

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[pmax9999]

I just tested my approach and the SQL generated by Crystal correctly had a WHERE clause of {IDfield} = 0, and returned no records as expected. Processing time was minimal.

The one thing I would suggest is to make sure the "{table.some_field}" is one that is indexed.

Pete

 

[IanWaterman]

Another alternative is to make the report a subreport in a container report which only holds the Parameters. You can link parameers from main report to subreport in lower left hand drop down of the SR linking dialog box.

Then in contatiner report have two sections one holding the Subreport and the other a text message saying faulty parameter.

Conditionally suppress these sesctions depending on the test of your paramters.

This saves rewriting report to use a command, BUT on really works if the report does not already have subreports.

Ian