Crystal Reports: using "startswith" function for multiple parameters in the select expert.

[former_member548403]

Per my supervisor's request, I have to modify a report in a way that two particular parameters that allow multiple selections have to be moved to the select expert section. Since the parameters are allowing the multiple selections, I am trying to use the "startswith" function like this.

({Command.incident_type} startswith {?IncidentType})
and
({Command.property_use} startswith {?PropertyUse})

I've used the startswith function before, but not for more than one parameter. The report is showing data, but it's not showing all the data. It is obmitting one or two fields and I am not sure why?

[DellSC]

By "omitting one or two fields" do you mean that a couple of fields don't have values or do you mean that some of the values in the parameter lists aren't appearing?

Using "and" in the selection formula means that both the incident_type AND the property_use fields of a single record in the command must have a value in the corresponding parameter - it won't select records where only one of the fields match. If you want data where either the incident_type or the property_use field matches, you'll need to change "and" to "or".

Unfortunately, the only way to do this "startswith" type of comparison with multiple selections is to use the Select Expert, which is a much slower way to filter data from a command. Generally, you need to filter data in a command using the where clause of the SQL in the command. For more information about using commands, see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

[former_member548403]

Hi, Dell. My original query for the report did include the filter in the where clause, but by the request of my supervisor, I'm modifying the report like this.

And you're right. I am using the Select Expert to use the "startswith" and I did try changing "and" to "or", but when I do that, the report displays more fields than it needs.

Lastly, by "omitting", I mean the report isn't displaying some of the values in the parameter list.