Using Variables Within Crystal Report Formulas
page 4 of 6
by Vince Varallo
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 90905/ 110

Step 3: Add Fields and Formulas to the Report

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.

View Entire Article

User Comments

Title: Very goo   
Name: Shivayogi
Date: 2011-09-08 11:26:19 PM
This code is helped me a lot
Title: Formula fields in Crystal Reports   
Name: Esha
Date: 2010-07-01 3:11:46 AM
Very Good and useful example
Title: Surge Consulting Group   
Name: Surge Consulting Group
Date: 2010-05-07 4:14:41 PM
Very very cool! Thanks for the example!
Title: Using Variables Within Crystal Report Formulas   
Name: pavani
Date: 2010-04-28 3:22:02 AM
simple and easy to understand article.When i tried to add crystal report template in my vs2008 i could not find it so do i need to restall my vs 2008 ?

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2023  |  Page Processed at 2023-06-08 1:03:10 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search