Welcome to another Crystal Tip.

This time, I'm looking at creating a Crystal Report which needs to take more complex business logic into account when selecting data for the Report.

As with my previous Blog, Crystal Tips 1, this Blog 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”.

Let's take an example to demonstrate what I'm thinking of:

The Requirement:

A Customer would like to create a Custom version of Sales Invoice SBONFAC Report which includes the "Pay into :" Bank details on the Footer. 

The Problem:

This sounds simple enough to achieve : the Bank is associated with the Company related to the Invoice's Sales Site and the Company is already part of the standard SBONFAC Report definition - it should just be a case of adding a join from the COMPANY table to the BANK table on COMPANY.ACCCUR_0 + COMPANY.CPY_0 to BANK.CUR_0 + BANK.CPY_0.

However, there are a couple of issues when there is more than one Bank for a particular Company/Currency combination:

1. How to determine which Bank is the "right one"
2. Since the Crystal Report relies on a SQL Statement which does a JOIN on all the tables on the Report, it will print all the Invoice Lines for every Bank satisfying the Join - for example, all the Lines will be printed three times if there are three Banks for a Company/Currency combination.

So, the question is "How do I determine which single Bank Code is appropriate to an Invoice?".

Well, there should be some business logic within the organisation which can be used in this situation. In this case: 

1. Any invoices for a UK "Paying BP" starting with "HS" need to pay into the Company's Bank which has "HSBC" in the Description and Currency "GBP"
2. Any invoices for a UK "Paying BP" starting with "NW" need to pay into the Company's Bank which has "Nationwide" in the Description and Currency "GBP"
3. Any invoices for a "Paying BP" not starting with "HS" or "NW" need to pay into the Company's default Bank  - in these cases, there is only one such Bank and its Description doesn't contain either "HSBS" or "Nationwide".
4. It is assumed that any Site which can be entered on Invoices for "HS" and/or "NW" Paying BPs has a single appropriate Bank associated with its Company.
5. It is assumed that any Site which can be entered on Invoices for non HS/NW Paying BPs has a single appropriate Bank associated with its Company.

Given Rules 4 and 5 above, having a GBP Site with Company Banks "HSBC1, HSBC2, GB1 and GB2" could be invalid as more than one Bank is associated with both "HS" and non-"HS" Paying BPs (and it would be invalid for "NW" Paying BPs as there is no Bank at all).

So, an example would be

How can the above rules be "translated" into Crystal Reporting?

Well, it's too complex to be fully represented in the normal Report > Select Expert - we need another mechanism, and that's to create a Formula and add that in Select Expert Formula Editor.

In this case, I've created a Formula called bank_code to encapsulate the Business Logic:

and then added this to the Record Selection Formula - since it's a formula, I had to use "@bank_code" to reference it:

The Selection Formula can be thought of as a "where-clause" in a SQL Statement, and the Database Links within Database Expert are the "from-clause" of a SQL Statement.

You may wish to make the bank_code formula more robust by including "UpperCase()" and  more extensible by using substrings - that way, you could say Companies with "HB"/"hb" would be associated with a Bank starting with "HB" (e.g. HBOS01).

So, I hope this Blog helps you to create more advanced Crystal Reports in the future.

Happy reporting!