Skip to Content
0
Former Member
Jul 30, 2008 at 04:33 PM

Optional Parameters don't work

45 Views

Dear all,

I have created optional parameters in the report. These parameters if NOT selected, the records of the jobs should be loaded according to the date range however, this is not what happening.

However, if I use only one optional parameter, the report loads fine.

Example

Date Range: 01/07/2008 to 30/07/2008

Account number: None

Report Results= Fine

Date Range: 01/07/2008 to 30/07/2008

Account number: None

Product Type: None

Report Results=NOT Fine

Date Range: 01/07/2008 to 30/07/2008

Account number: 0010065

Product Type:None

Report Results=NOT Fine

In the above examples if I use two optional parameters and select only one or select none, the report starts reading alllllll the records and takes ages to load. Whereas if I use only one optional parameter and do not select the option the report loads fine. It works fine even if I select an optional parameter but it has to be only one.

Here is the formula I used:

(please note I used the same formula in the Report Selection formula and also Formla workshop -->Saved Record and in both ways it didn't work out.)

I have set all the account number, product type in the report with optional suppression e.g.(not hasvalue(?))

Both parameters are set to optional.

Formula used in Selection Record

{order_header.date_entered} >= {?Start Date} and
{order_header.date_entered} <= {?End Date} and
(not HasValue({?Account Number}) or {order_header.account_no} = {?Account Number})and
(not HasValue({?Product Group}) or {lens_types.prod_group} = {?Product Group})and
(not HasValue({?Sales Area}) or {slslsp.slr_slspname} = {?Sales Area});

I have also tried the following formula but it did not work.

Formula used in Saved Record (Please note this formula is an example taken from a sample report.)

if 
  (hasvalue({?Account Number}) = true and
  hasvalue({?Product Group}) = true and
  hasvalue({?Sales Area}) = true)
then
  ({order_header.account_no} in {?Account Number} and
  {lens_types.prod_group} in {?Product Group} and
  {slslsp.slr_slspname} in {?Sales Area})
else if 
  (hasvalue({?Account Number}) = true and
  hasvalue({?Product Group}) = true and
  hasvalue({?Sales Area}) = false)
then
  ({order_header.account_no}Cin {?Account Number}and
  {lens_types.prod_group} in {?Product Group})
else if 
  (hasvalue({?Account Number}) = true and
  hasvalue({?Location - Region}) = false and
  hasvalue({?Location - City}) = false)
then
  (order_header.account_no} in {?Account Number})
else if 
  (hasvalue({?Account Number}) = true and
  hasvalue({?Product Group}) = false and
  hasvalue({?Sales Area}) = true)
then
  (order_header.account_no}  in {?Account Number}and
  {slslsp.slr_slspname} in {?Sales Area})
else
{order_header.date_entered} >= {?Start Date} and
{order_header.date_entered} <= {?End Date} and
 true

The above code was under Saved Data Selection Under Formula workshop. I used this to make them as optional parameters.

Please note in the 2nd formula all the parameters except account number were dynamic.

Also I did not use both formula at the same time instead I tried each formula separately.

The sample report runs like a charm but mine it doesn't take effect.

Could you please help me with this issue.

many thanks

Kind Regards

Jehanzeb