Sunday, October 28, 2007

Automating the Parameters in a Crystal Report

Well the advantage with using the parameters is that is reduces the traffic from the server to the client. If a condition is used in the record selection with formulas then the filtering is done on the client which is not the best way when the data on the underlying report is too much i faced this issue and has to resolved to use the parameters collections of crystal.

Well as i was using a crystal reports control and did not want the ugly parameter box popped by crystal as it does not provide features like browse etc which i had build on my request form. I then realized that there is a way to this although easy but had to really look around to get he syntax right :-) anyways here it is: -

ParameterFields:
This property specifies the default value of the specified parameter field.When the prompting dialog box appears for the parameter field, the value specified with this property will be the default value that appears it can also be skipped as we will see later. This property is not available for subreports.


Syntax
[form.]Report.ParameterFields(ArrayIndex)[=”ParameterName;NewValue;SetCurrentValue”]

Remarks:
 The parameter, SetCurrentValue can either be set to TRUE or FALSE.
 If set to TRUE, the parameter value is passed to the current value in the report; the user is not prompted to enter the parameter value.
 If set to FALSE, the parameter value is passed to the default value for the parameter; the user is prompted to enter the parameter value, with the value you set showing as the default value.
 The default value for SetCurrentValue is FALSE.
 Use a separate line of code for each parameter field for which you want to change the value.
 The order of values in the array must conform to the order of parameter fields in the report.
 The first parameter field in the report is array index (0), the second is (1), etc.


For example:
 To change the value of the first parameter field in a report (parameter1) to “red” use the following syntax (user will not be prompted to enter a value):
CrystalReport1.ParameterFields(0) = “parameter1;red;TRUE”

 To change the value in a Date parameter field use the following syntax (user will not be prompted to enter a value):

CrystalReport1.ParameterFields(0) = “DatePar;Date(1998,12,7);TRUE”

 To prompt the user to change the value of the third parameter field in a report (parameter3) use the following syntax (user will be prompted to use the default value set using the NewValue parameter below - “blue”):

CrystalReport1.ParameterFields(2) = “parameter3;blue;FALSE”


Note:
Even though one might use parameters the filters will not be applied on the server if the parameters are used within conditions in the record selection formula.

I was trying to use if conditions in the record selection formula to have dynamic record selection as below :

local booleanvar showrecord;

showrecord := True;
showrecord := showrecord and ( {vw_transport_log.tr_closed} = 'N');

if {vw_transport_log.from_location} <> '' then
showrecord := showrecord and ( {vw_transport_log.from_location} = {@from_warehouse} );

if ({@to_warehouse} <> '') then
showrecord := showrecord and ({vw_transport_log.to_location} = {@to_warehouse});

if ({@transporter} <> '') then
showrecord := showrecord and ({vw_transport_log.transporter} = {@transporter});

if ( {@from_date} <> Date (1900,01,01) ) then
showrecord := showrecord and ({vw_transport_log.start_date} >= {@from_date});

if ( {@to_date} <> Date (1900,01,01) ) then
showrecord := showrecord and ({vw_transport_log.start_date} <= {@to_date}); showrecord; then i realized that in cases like this the filters are applied at the client so to force the filters at server the record selection formula should be modified as follows: and ({vw_sagya_movements.dated} >= {?FromDate})
and ( {vw_sagya_movements.dated} <= {?ToDate} ) and ({vw_sagya_movements.from_location} = {?FromWarehouse} );

which means "no if conditions"; the way around this could be to apply extreme values to the from and to parameters, in case a filter is not required. For eg if date filter is skipped then i apply a date filter like 1900-01-01 to 2099-01-01 it is as good as the filter is not applied.

No comments: