CODEWITHSUNDEEP
crystal-reports
David Waters
David Waters

Reputation: 31

Using LIKE in Multi-Value Parameter in Crystal Reports 2011

I am developing a report in Crystal Reports 2011 that has 3 sub-reports pulling data from 3 different databases. I have a Multi-Value Parameter (String) in the main report that passes the input values to the 3 sub-reports which have the same Multi-Value String Parameter.

Sample Input Values are:

 P000000030,

 P000000930,

 P000001730

The user does not want to input the leading alpha character and preceeding zeroes. They want to input the following:

30, 930, 1730

The sub-report pulls all of the records successfully if the user puts the entire string value in with the following Record Selection Criteria, but it does not work with the partial strings input:

{Command.Puchase Order} in {?Pm-?Reference}

Can anyone advise the syntax needed to pull the data in the subreport with the substrings as inputs?

Thanks in advance!

Upvotes: 2

Views: 1656

Answers (3)

David Waters
David Waters

Reputation: 31

Thank you for your input guys!! I took a bit from everyone and came up with the following solution:

  1. Create a column in the datasource that trimmed out the desired value --> ltrim(regexp_replace(a."po_num",'P',''),'0') as "Puchase Order2"
  2. Modified my Record Selection Criteria to select for either column --> {Command.Puchase Order} in {?Pm-?Reference} or {Command.Puchase Order2} in {?Pm-?Reference}

I really appreciate your input! I am able to deliver the desired solution with your aid.

Upvotes: 1

dotjoe
dotjoe

Reputation: 26940

You could add the "number only" version of [Puchase Order] to your datasource...

cast(cast(right([Puchase Order], len([Puchase Order]) - 1) as int) as varchar(9)) 
as [Puchase Order Number]

...then use that in the select expert. I'm getting the number without the leading P, casting to int to remove the leading zeros, and then back to a varchar for the string comparison in Crystal.

You could do the same with a formula in Crystal reports. Then reference that formula in the select expert. Downside is having to repeat that in all 3 sub reports.

Upvotes: 0

cojimarmiami
cojimarmiami

Reputation: 659

go ahead and create a formula that calculate the length of your parameter(len({?Pm-?Reference})) and place it suppressed on your report header. Then put below in your record selection formula

right({Command.Puchase Order},{your length formula}) in [{?Pm-?Reference}]

Upvotes: 0

Related Questions