This article assumes that you are familiar with developing Crystal Reports in a Sage X3 environment – if you’re not familiar with the process, we’d recommend that you sign up for the SageU Course called “X3 – SageX3 – Designing Crystal Reports: Basics”.

Are you aiming to create a Crystal Report for Sage X3 which selects data based on multiple values for a particular Field in a Report – for example, running the PBONFAC Report for a variable number of Suppliers?

Is there a way of achieving this in Sage X3 and Crystal Reporting?

Well, the good news is, the answer is “yes” - in fact, there are two options!

Option I

The obvious choice is to create a new version of the Report, PBONFAC in this case, and add multiple Parameters in the Report’s Parameter definitions tab and then change the Crystal Report to select based on those Parameters:

Then, create the corresponding Parameters in Crystal Reporting:

Once the parameters are set up, they can be added to Select Expert – use the “is one of”:

This has the advantage that each Parameter is of type BPS, and allows the User to Browse/Select Suppliers and includes Validation at run-time.

The downside is that the User would need to put one Supplier in each Parameter field – this could be a bit tedious if the Report is set up to allow up to 20 different Suppliers at run-time  and you wanted to enter 15 or even 20 Suppliers.

When XBONFAC is run, the Parameter screen would look like

Option II

An alternative is to create a single field which accepts a comma-separated list of Suppliers – the list could even be copied-and-pasted from another source.

This would have the advantage of making it easier to enter – a simple list being entered in one Parameter.

The disadvantage would be that there’d be no Browse/Select or Validation on the field.

Set up a single Parameter with the appropriate maximum length in Reports option:

Then, add the corresponding Parameter in the Crystal Report:

Having set up a new parameter in X3 and the Crystal Report called suppliers, how do you divide the comma-separated string into separate sub-strings which can be used to select data at run-time?

Well, Crystal Reporting supplies the built-in function “split” to do this – it allows you to populate an array with sub-strings which can then be used in separate formulae within the Report.

The following shows two formulae, Supplier and Supplier 10, which return the first and tenth elements of the x array populated using the split function – note that, as the exact number of sub-strings isn’t known at run-time, you have to check if there’s any data to return in an array-element using the “ubound” function.

Once you’ve got your formulae set up, they can be incorporated into the Select Expert within Crystal Reports.

Normally, Select Expert is used in conjunction with Report Parameters directly, but we’re using formulae in this case, and need to add the conditions directly in the Formula Editor:

So, if you use “Show SQL Query” to check what the SQL Statement would look like for a typical test in SEED Folder, entering “GB013,GB015,GB021” in the suppliers field, the results would be

Notice that Crystal Reporting still includes OR-clauses for those array-elements which are empty - in this example, formulae Supplier 4 through to Supplier 10 return an empty string ('').

This is ok if blank is not a valid value in that there is no data satisfying that condition – if blank/empty is a valid value for the field, the formulae may need to be adjusted to explicitly return a different value which would never be in the X3 field in order to avoid the data being included in the report by accident:

In the above case, any OR-clause which doesn't have a value will equate to "PINVOICE"."BPR_0"='---' in the SQL statement sent by Crystal Reports at run-time.

When you select the YPBONFAC Report within X3, the Parameters screen will look like this:

So, it's as simple as that!

I hope this is of use to you in writing Crystal Reports for Sage X3.