CODEWITHSUNDEEP
parameterscrystal-reports
MrrMan
MrrMan

Reputation: 158

Multiple optional parameters for report

My report has 2 required parameters (Source; Location and 2 optional ones Access Type; Access Complication) I've tested the query and can prove that works correctly. The report also works if I populate every param.

Without a complication param, all complications should return, but no patients. If only access type is chosen, it should get a list of patients with all complications assigned to the chosen access types. But I get no records if either of the parameters is left out.

I tried this hasvalue formula in the select expert, to no avail

(not hasValue({?AccessType}) OR {Command.AccessType} = {?AccessType})
 and
(not hasValue({?AccessComplication) OR {Command.AccessComplication} <= {?AccessComplication})

If I set a new formula for Access Type in the select expert, then for Access Complication with the above hasvalue formula, then whichever one I do second overwrites the first. They don't seem to be able to exist alongside each other

Upvotes: 0

Views: 643

Answers (1)

KuKeC
KuKeC

Reputation: 4610

When used in a formula, optional parameters become mandatory parameters. End of story.

To achieve something similar, send your param as all or * (which means all records in SQL). Before selecting all the data, check your "optional" parameter values, and if they have all or * write a selection formula without including those parameters.

Pseudo code should look like this for 2 mandatory and 2 optional params:

if param3 = "all" or param4 = "all"
    if param3 = "all" and param4 = "all" select data only with param1 and param 2
    else
        if param3 = "all" select data without param3
        if param4 = "all" select data without param4
else select data with all params

Upvotes: 2

Related Questions