Formula fields within Crystal Reports allow you to inject code blocks within a report when simple Select SQL statements can't do the trick.  You can declare variables, create if-then blocks, create arrays, execute loops, or call built in functions within Crystal Reports.  This article will create a Crystal Report in an ASP.NET web site project and demonstrate how to create a formula field, manipulate a date in the formula as well as elaborate on how to declare a variable and assign it a value, and also use if-then logic to create summary fields by quarter on the report.

Before you begin you will need to have installed Visual Studio 2008 with Crystal Reports for .NET.  The samples are written in Visual Studio 2008 but they will work with Visual Studio 2005 also. I created a simple database with one table to use for the sample report. The code for the sample report created in this article can be found here.

1.    Open SQL Server Management Studio 2008 and connect to your server.

2.    Create a new database and name it CrystalFormulas.

3.    This database will contain only one table called SalesHeader. Create this table with the following fields.

I pre-populated this table with records for January through June.  You can download the script to create this table and the seed records here.  You can run this script in SQL Management Studio to build the table and insert the records or you can comment out the creation of the table and just run the insert statements if you created the table manually.

You can use either Integrated Security when connecting to this database or create a SQL Login.  Either way you need to remember which method you used when you are ready to connect to the database as defined in the steps below.

1.    Launch Visual Studio 2008 and select FileàNew Web Site… from the menu.

2.    Select the ASP.NET Web Site template.  Set the Location to File System and set the path to c:\Documents and Settings\YOURNAME\My Documents\Visual Studio 2008\Projects\CrystalFormulas.  Set the Language to C# and then click the OK button.  This will create the web site with the Default.aspx page and the web.config file.

3.    Right click on the project in the Solution Explorer and select Add New Item… from the pop-up menu.

4.    Select the Crystal Reports template and change the name of the file to QuarterlySales.rpt.  Click the Add button.

5.    The Crystal Reports Gallery dialog will appear.  Select the "As a Blank Report" option and click the OK button.  This will add the Crystal Report file to your project and open up the Crystal Reports designer.

6.    Now you need to connect to the database.  Right click on the Database Fields node in the Field Explorer and select Database Expert… from the pop-up menu.

7.    The Database Expert dialog will appear.  Click the plus sign next to Create New Connection.  Click the plus sign next to OLE DB (ADO).  The OLE DB (ADO) dialog box should appear.

8.    Select SQL Native Client from the list of providers and click the Next button.

9.    Enter your server name.  You then have the option to either use a SQL Login account to connect to the database of use integrated security.  For this example I'll use Integrated Security by checking the Integrated Security checkbox.

10. Enter the name of the database that was created in step 1, CrystalFormulas.  Click the Finish button.

11. You should see the database listed under the OLE DB (ADO) node.  Click the plus sign next to the database to view the schemas.  Click the plus sign next to dbo to view the tables.

12. Click on the SalesHeader table and then click the > button to move this table to the list of selected tables for this report.

13. Click the OK button.

Now that you are connected to the database you can add your fields to the report.  Any fields that you want to show up as line items in the report should be placed in the Details section.

1.    In the Field Explorer click the plus sign next to Database Fields.  Click the plus sign next to SalesHeader to view the fields in this table.

2.    Drag the SalesDate and Total fields to the details section in the report.  Crystal will automatically add the column headers to the Page Header section when you drop fields into the details section.

3.    Now we'll create a formula field to display the Quarter of the SalesDate.  Right click on the Formula Fields node in the Field Explorer and select New… from the pop-up menu.  Enter Quarter for the formula name and click the Use Editor button.

4.    Crystal has numerous built in functions that you can use in your own formulas.  The functions are listed in the Functions pane in the Formula Editor.  Click the plus sign next to the Functions node and then click the plus sign next to the Date and Time node.  This will display the list of available functions to manipulate dates.  The one we are looking for is called DatePart.  If you scroll down the Date and Time node you should see this function.  Be aware that the functions aren't listed in alphabetical order so you have to scroll down near the bottom of the list.

5.    The DatePart function has three different signatures.  If you click the plus sign next to the DatePart node you'll see the three signatures.  Double click on the first signature and it will then appear in the body of the formula.

6.    The first parameter to the DatePart function is the interval.  The interval can be year (yyyy), quarter (q), month (m), day (d), hour (h), minute (n), second (s), day of week (w), and week (ww).  Enter 'q' so that the quarter is retrieved from the date.

7.    The second parameter is the date expression that should be evaluated.  This is the SalesDate field in the SalesHeader table.  The formula text should appear as follows.

DatePart ('q', {SalesHeader.SalesDate})

8.    Click the Save and close button.  The Quarter formula will now appear under the Formula Fields node in the Field Explorer.

9.    Drag the Quarter function to the details section.  Click the Main Report Preview button to display the report.  You should see either 1 or 2 for the quarter.

The next step will be to add two formulas to the report.  This report should display a summation for quarter 1 and quarter 2.

1.    Click back to Main Report design view.

2.    Right click on the Formula Fields node and select New…

3.    Enter Quarter1 for the name and click the Use Editor button.

This formula will create a variable to hold the running total of any sales that occurred in quarter 1.  Variables have three options for scope: Local, Global, or Shared.  Local means that the variable is used in the specific function and its value is lost once the formula exits.  Global means that the variable will retain its value even after the function exits and can be used in other formulas in the report.  Shared means you can use this variable in other formulas or even sub reports.  By default if you omit the scope of a variable it is Global.

4.    Enter the follow code for the formula text.

Global NumberVar quarter1;
 
if DatePart('q', {SalesHeader.SalesDate}) = 1 then
    quarter1 := quarter1 + ToNumber({SalesHeader.Total})
else
    quarter1 := quarter1

This creates a global variable called quarter1.  The code then checks if the date is in Quarter 1.  If it is then it is added to the quarter1 variable. 

5.    Click the Save and close button and then add the formula to the Details section.

6.    Click the Main Report Preview button and you'll see the running total in the Details section.

7.    Click back to the Main Report design view.  Create a second formula field called Quarter1 and set the formula text to the following.

Local NumberVar quarter2;
 
if DatePart('q', {SalesHeader.SalesDate}) = 2 then
    quarter2 := quarter2 + ToNumber({SalesHeader.Total})
else
    quarter2 := quarter2

8.    Save the formula and drag it to the Details section next to the Quarter1 formula and click the preview button.

Notice that the field is not a running total.  That is because the variable was declared as Local rather than Global.

9.    Click back to the design view.  Right click on the Quarter2 field in the Details section and select Edit Formula from the pop-up menu.  Remove the word Local before the variable declaration.  By default it will be Global.  Click Save and close and then preview the report again.  This time you'll get the summarized total for Quarter 2.

10. Click back to the design view.  Drag the Quarter1 and Quarter2 fields to the report footer in their respective columns. 

11. Delete the Quarter1 and Quarter2 field from the details section.

12. Right click on the Details section and select Suppress (No Drill-Down) from the pop-up menu.

13. Now if you preview the report you will only see the totals rather than the running total.

Formula fields in Crystal Reports really empower you to manipulate your data in a variety of ways when a simple select statement can't do the trick.  The syntax is quite simple to use if you already know C# or VB and the help documentation is packed with samples.  Crystal also has numerous built in functions that you can use in your formulas.  There are built in functions for standard deviation, variance, Nth Largest, Nth Smallest, amortization, fair value, trim, and uppercase just to name a few.  You can also use looping structures such as for, while, and do loops.  You can even find the previous or next value of a field.  The power of the formulas and scripting language is tremendous and can allow you to accomplish almost any requirements defined by even your toughest users.