Hopefully you’ve read Part 1 – Installation / Configuration and you’re ready to start building your first report? I’ll be using Microsoft’s sample database Northwind on SQL Server for this part of the tutorial but you should be able to adapt almost any datasource to the examples. So let’s get started…

Create
Create a blank report (File -> New -> Blank Report)
The Database Expert will automatically appear
Expand the OLE DB (ADO) node and double click Make New Connection

Select Microsoft OLE DB Provider for SQL Server and click Next
Enter your server login details, select the Northwind database and click Finish
*Note* If you are using Integrated Security ensure the User ID field is blank before you tick the Integrated Security box or else it will fail
Expand the server, database, schema & tables nodes and double click the Orders table to add it to the selected tables pane then click OK (we’re going to start real basic with just this one table)

Add Fields
If it isn’t already visible, open the Field Explorer (View -> Field Explorer)
Enable the display of field types (right click in the field explorer and tick Show Field Type)

Highlight the 6 fields as above and drag them into the detail section of the report. You will notice headers are automatically inserted.

Preview
Refresh the report (View -> Preview or Ctrl-R or F5 or the refesh icon from the toolbar)

Format
I’m not going to go into detail, but now is a good time to explore the various formatting options for each field type. Right click a field and choose Format Field
For all fields you’ll see a standard set of formatting options (on the Common, Border, Font & Border tabs) but depending on the field type you will see one of the following tabs: Paragraph, Number or Date and Time
I chose to hide the time from the OrderDate and RequiredDate fields and remove the comma from the OrderID

Create a Formula Field
A formula field can be used for many purposes but will primarily be used to manipulate one or more fields
Right click Formula Fields -> New in the field explorer. We’re going to create a field to show whether the order has shipped, so let’s call it has_shipped and click OK

Take a moment to familiarise yourself with the Field Tree, Function Tree & Operator Tree- these list every field, function & operation available to us in our formula
*Note* Pay attention to the Default Values For Nulls dropdown (which hopefully you configured In the global Crystal options whilst reading Part 1 of this tutorial)
Move down to formula area and type an open curly brace { – the autocomplete popup should appear

*Note* If the autocomplete popup does not appear you probably need to update to the latest service pack
Enter the following formula:

if isnull({Orders.ShippedDate}) then
'Not Shipped'
else
'Shipped';

Click Save and close and drag the formula from the field explorer into the detail section of your report
*Note* This saves the formula only and does not save the actually report
Return to the report preview to see your formula in action

Filter
Report -> Selection Formulas -> Record
Try experimenting- here are a few examples:

  • {Orders.Freight} < 10
  • {Orders.EmployeeID} = 3

After changing the record selection formula each time click Save and Close and refresh the report preview

Sort
Report -> Record Sort Expert
You can drag, double click or use the buttons to add sort fields

Use the radio buttons to determine whether the fields are sorted in ascending or descending order
After changing the record sort criteria click OK and refresh the report preview

Summarize
There are 3 main ways to your data- I wont get into which to use when and why yet but I will briefly demonstrate each of them

1.
Highlight the Freight field in the detail section of your report and click the Summary icon (or Insert -> Summary)

Refresh the report and scroll to the report footer to see your summary field
Delete the summary field so we can try method 2 (right click -> Delete or select the field and press delete/backspace)
2.
Create a new formula called freight_total and enter the following:

sum({Orders.Freight});

Drag the new formula field into the report footer directly below the freight column

Refresh the report and scroll to the report footer to see your formula field

Delete the formula field so we can try method 3
3.
From the field explorer right click Running Total Fields -> New

Let’s call our running total freight_total and choose Orders.Freight as out field to summarize
Drag the running total field into the report footer directly below the freight column
Refresh the report and scroll to the report footer to see your running total

Conditional Formatting
Another concept you’re probably familiar with and I’m just going to show you a few examples
1. Highlight freight costs > £50
Right click the freight field in the detail section of your report -> Format Field

The X-2 icon indicates fields which can be determined by evaluating a formula
Click the icon next to the Color dropdown and enter the formula

if {Orders.Freight} > 50 then
crRed
else
crBlack;

Click Save and close then OK
Refresh the report

2. Highlight rows where order was shipped after the required date
Right click the detail section -> Section Expert
*Note* Sometimes the section you right clicked on isn’t automatically highlighted in the section expert, so always check before proceeding to modify the section properties
Switch to the Color tab and click the conditional formatting formula icon and enter the following

if {Orders.ShippedDate} > {Orders.RequiredDate} then
crYellow
else
crNoColor;

Click Save and Close


Click OK and refresh the report

Wow- that took longer than expected. I’ve tried to skim the surface and will likely revisit a lot of the topics later in the tutorial.

If you feel I’ve missed anything or have something to add please comment!

L

Advertisements