Tips and Tricks in SAP Crystal Reports

Question: I would like a user to have a menu option to select ALL values in a dynamic prompt list, instead of having to set up the prompt to allow multiple values and the user having to select the double-arrows, if they want to use all the prompt values to run the report. How would I do this?

Answer: In order to do this, you will need to create a SQL command, that grabs all the values you want for your prompt and joins it to another query, that pulls the wildcard (*) value and the word ALL that a user can select.

Steps:

We will be using the Northwind sample SQL database that comes with SQL Server 2000 and the “Customers” table.

1.  After selecting the data source, add the “Customers” table.

2.  Add a command

3. The Command looks like this.

SELECT ‘*’ as ‘Country’,

‘All’ as ‘Description’

UNION ALL

SELECT COUNTRY,

COUNTRY as ‘Description’

FROM CUSTOMERS

GROUP BY COUNTRY

4. The Selected Tables have 2 sources as shown below

5. The “Links” window is as shown below

6. Ignore the database warning and click ok

7. Inside the report, create a parameter using the field from “Command” table
8. The parameter should look like this.

9. Go over to the “Select Expert” enter the formula as shown below.

10. Refresh the report, enter the value for parameter

11. View report to see data.

Question2: I often need to display Boolean data on my reports, but am looking at other ways to represent this other than just “True” or “False.” Is it possible to do this – for example, could it be set up to display check boxes instead?

Answer: Yes, it is often required to display Boolean values on a Crystal Report, and it is reasonably straightforward to provide a more intuitive display, such as check boxes either checked or unchecked, rather than the standard “True/False” rendering. This can be achieved by using a little imagination and the Crystal formula language. Without this solution, the display of the Boolean value is as follows:

This example is going to use the Xtreme sample database and we are going to set the check box on the “Shipped” column. How do we do this? First, we set up a formula to evaluate the Shipped Boolean value and assign the appropriate character symbol for a ticked check box if the value is true and a crossed check box if it is false. The formula is listed below:

We then place this formula onto the report – removing the Shipped report field.  Once the field is on the report, we just need one more step – that is to use the format editor to set the font to “Wingdings” so that the character is rendered properly.  (As an added bonus, the format editor was used to change the color of the font based upon the same condition as is in the formula to make the display even clearer to the person viewing the report).

Of course, this solution will allow you to use any number of different character representations for your data using any of the supported symbol fonts. One good reference to determine which “chr()” value to use is at this site – http://cloford.com/resources/charcodes/symbols.htm