13,002,628 members (54,348 online)
alternative version

#### Stats

173.5K views
18 bookmarked
Posted 8 Aug 2011

# Using Formula in Crystal Report

, 8 Aug 2011
 Rate this:
The formula field in a Crystal report is a functionality provided to enhance computation of values dynamically during the display/printing of records in a report of an application.

## Abstract

The ultimate goal of most commercial software is the generation of useful and accurate reports for the requirements of the users in enhancing the decision making process at the managerial level of the enterprise. Notwithstanding the existence of some other forms of reporting tools, Crystal Report has found enormous patronage in the development of concise and reliable reports. In this paper, the use of one of its functionalities – formula – is used to demonstrate the implementation of the electronic bin card (e-BinCard). The bin card is used to manually track individual items in stock of most merchandising company and stores/warehouses in most of the developing nations of the world, including Nigeria. However, this is gradually being replaced by software applications capable of performing the same function. For this demonstration, a stock management application is presented with the implementation of an e-BinCard using the formula field of the Crystal report design tools to compute dynamically the stock balance.

## 1.0 Introduction

The formula field in a Crystal report is a functionality provided to enhance computation of values dynamically during the display/printing of records in a report of an application. Most developers often fail to effectively explore the potentials of this Crystal Report functionality and often preferred other report viewing modules. Formulas in Crystal report have two forms of syntax; Basic and Crystal. In this paper, a few of the details on the syntax, data types, return values and case sensitivity will be demonstrated. Formulas are always used to return a value or Insert a value where desired. The data types returned must be simple and can be `Number`, `Currency`, `String`, `Boolean`, `Date`, `Time `and `DateTime`. You cannot return complex data types such as range or array. Formulas return a value by assigning the value to the Formula variable and must always return a value. These values are automatically assigned to the field the formulas are inserted. The Stock Bin Card will be used to demonstrate the use of formulas and this will enhance the reader’s knowledge of formula in Crystal report.

## 2.0 The Stock Bin Card

The Bin Card is a manual technique of maintaining stock movement in a warehouse. The Bin Card contains the following elements/fields; Stock Code, and Description, date of operation, Opening balance, Quantity received and dispatched/sold stock balance and staff Identity. (See figure 1). Although getting this type of report from the Crystal Report module may look easy but the `Group By `or `Order By` functionality may be muddled up when computing the Stock Balance rendering the report meaningless. It is expected the reader has minimal knowledge of Visual Basic.NET and MySql for ease of understanding as will be demonstrated in the course of this paper. The example is broken down into three parts; the table (MySql), form (design – VB.NET and Crystal Report) and the program (VB.NET).

### 2.1. The Database, Table and Columns

Databases are created in MySql using the instruction “Create database nameofdatabase” while tables are also created with the instruction “Create table tablename”. The bincard database and the `stockbincard `table are thus created. The creation and use of Database and Table are illustrated in MySQL Tutorial. Alternatively, the `MySQLAdmin `could also be used for the same purpose as illustrated in figure 2. The MySQL query browser could be used to confirm the creation of the database and the table respectively along with the columns.

## 3.0 The StockBinCard Project

The `Stockbincard `project is created in the VB.NET environment and the following forms are created:

1. The stkbinmenu.vb - The main menu form for the project (figure 3). The form contains three submenus to access the three basic functionalities of the e-BinCard. (Stock Receipts, Dispatches and printing e-BinCard).
2. The dataentry.vb - This form provides the mechanism for data entry into the database. The receipts and dispatches are registered for the stock item. If the stock item is a new product, it passes into creation mode, else it accepts the data in either of the operations (receipts or dispatches) (see figure 4). The following code fragments suffixes:
```Public Function GetDetails(ByVal strCode As String) As Int16

Sql = "Select StockDescr From stockbincard Where StockCode = ?Dcode "

Try

Try

With cmd

.CommandText = Sql

.CommandType = CommandType.Text

.Connection = BconnectObj

.Parameters.Add("?Dcode", MySqlDbType.VarChar).Value = strCode

label = .ExecuteScalar  // Returns the label if item is in the DB

End With

End Function```

Once the item is found in the e-BinCard database, the various objects are initialized and the operation is validated. It is important to state here that the `StockCode `column of the database is not used as a key, therefore multiple occurrence of records is allowed.

3. The stkModule.vb contains the declaration of common variables (global) used in the project.

Some of these declarations are commonly used and accessible variables or instances of objects in the project.

```// stkmodule.vb
Module MainModule
Public myReport As New ReportDocument
Public RData As New DataSet
Public XYcommand As New OdbcCommand
Public cmd As New MySqlCommand
Public BconnectObj As MySqlConnection = New MySqlConnection()
Public MyConString As String
Public Conn As New OdbcConnection() '
Public IsConnected As Boolean
Public Gflag As Boolean        'Global error flag
Public FormMain As New Form()
Public toggleForm As Integer = 0
End Module```

The file also contains some other `public `functions such as the `startup()` – starting point for the project, `DatabaseConnection()` – connects to the `BinCard `database, `PrintBinCard()` – to generate the e-`BinCard`.

4. The ReportBinCardView.vb – implements the viewing of the report generated by the crystalreport viewer (see figure 5): This is the form that ‘houses’ the object `ViewReportBinCard`.

The form is created and the `crystalreportviewer` object dragged and inserted into the form and the names associated appropriately. The following code segment is associated with the viewer to load the output of the select statement (see figure 7) to the `ViewReportBinCard`.

```Private Sub ViewReportBinCard_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles ViewReportBinCard.Load
ViewReportBinCard.ReportSource = myReport
End Sub```
5. The ReportBinCard.rpt - The Crystal Report file (design) for the `StockBinCard` (see figure 6).

Before using the ‘Crystal report wizard’ to create the “ReportBinCard.rpt”, the generation of the ‘`Rdata`’ set and writing in an XML into the file “StockBinCard.xml” should be realized.

The following code segment generates the dataset ‘RData in XML format:

```XYcommand.Connection = Conn

XYcommand.CommandText = Sql

RData.WriteXml("StockBin.xml", XmlWriteMode.WriteSchema)```

The crystal report creation wizard allows the creation of ‘New Connection’ by uploading the XML file (StockBin.xml) and attaching the table – `StockBinCard `– to the ‘ReportBinCard.rpt’.

In figure 6, the corresponding fields of the `StockBinCard `are shown with the formula field to compute the stock balance after each operation.

## 4.0 Generating the Report

Generating the report involves preparing the data from the database into a dataset for the report-loader. The following codes will be used to achieve the desired result in the following order (see figure 7):

1. The `select `statement
2. The connection to the database
3. An instance of `OdbcCommand ``Xycommand`
4. An instance of `OdbcDataAdapter ``Kadapter`
5. Filling the dataset – `Rdata`
6. Loading the report – `myreport `– and setting the Report `ReportSource`
7. Display the report - `ViewReportBinCard`

## 5.0 The Formula Field

As earlier indicated, the stock balance will be computed and displayed dynamically using the Crystal Report Formula. When creating formulas, you have the option of using either Crystal or Basic syntax. Almost any formula written with one syntax can be written with the other. Reports can contain formulas that use Basic syntax as well as formulas that use Crystal syntax, but a single formula can use only one syntax. In the computation of the stock balance, we shall demonstrate with both the crystal and basic syntax options.

### 5.1. The Basic Syntax

The Microsoft MSDN Library clearly explains the Basic syntax of the crystal report and employs the same syntax as the BASIC language. An exception is that a value must be returned in the special variable Formula. It is not case-sensitive and contains data types such as string, integer, etc. as commonly used in BASIC. In figure 8 below, the option Basic Syntax is selected, the table columns highlighted, and the formula also encircled.

The formula for the computation of the stock balance is given at the code section of the formula workshop:

```Global BalQty As Number 	      - declares the global variable Balqty as an Integer
if {Table.Flag} = 1 Then                        - if the record is Receipt of Stock
BalQty = {Table.qtyRec} + Balqty       - Add to the cumulative value of Balqty
else                             		      - otherwise
BalQty = BalQty – {Table.qtyDispa}
endif
Formula  = Balqty 		      - Return Balqty```

### 5.2. The Crystal Syntax

The crystal syntax is equally illustrated and explained in the MSDN Library. The syntax is quite different from the Basic Syntax. The value returned by a crystal syntax does not have to be attached to a variable but the variable value itself. Any of the data types could be returned as a value. This will be displayed at the position in the report containing the formula. This is demonstrated below in figure 9.

## 6.0 How the Program Works

Once the program is launched, upon a successful connection to the database – bincard - , message of successful completion is displayed otherwise an error message (See figure 10).

Thereafter, the Main Menu is displayed with the following options:

1. Receive Stock – To register receipts
2. Dispatch Stock – To register dispatches
3. Print Bin Card – To print Bin Card

During the registration of stock (receipts or dispatches), if the item is new, the program creates the item otherwise, the information on the stock is displayed, i.e., the stock description. A sample report output is shown in figure 11.

## 7.0 Conclusion

The `stockbincard `has been used to demonstrate the use of an electronic stock bin card. In its present form, it looks simple but is a good demonstration of the use of Formula fields in crystal report.

Other functionalities such as; periodic choice of printing the electronic Stock Bin Card, inclusion of the staff Identity, etc. could be inserted and full report of the stock during for the purpose of inventory. It is hoped the reader would find this example useful and be able to enhance it for other requirements.

The source program could be downloaded here.

## 8.0 Suggested Further Reading

1. MySQL 5.1 Reference Manual
http://dev.mysql.com/doc/refman/5.1/en/tutorial.html
2. MySQL Tutorial
http://www.tizag.com/mysqlTutorial/
3. MSDN for Visual Studio 2008
http://www.microsoft.com/vs
4. Visual Basic .NET Programming for Beginners
http://www.homeandlearn.co.uk/net/vbnet.html
5. Visual Basic and MySQL developer Notes
http://www.openwin.org/mike/index/index.php/articles
 ABC Company Ltd.43 Asokoro, Abuja, NigeriaStock Bin Card Stock Code: 2633774 Description: Michelin Tyre Tubes 295/15 Date(dd/mm/yy) Receipts Dispatch/Sold Stock Balance Staff Id./Sign Comments 12/05/2011 - - 200 Tony Opening Bal 16/05/2011 20 - 220 Tony - 22/05/2011 - 5 215 Andrew - 23/05/2011 40 - 255 Tony - 24/05/2011 - 55 200 John Transfer 27/05/2011 - 12 188 Andrew Damaged 27/05/2011 7 - 195 Tony -

Figure 1: A typical Manual Stock Bin Card

Figure 2: The Stockbincard Table

Figure 3: The Main Menu for the StockNinCard Project

Figure 4: The Data Entry Form

Figure 5: The Report Viewer Form

Figure 6: The Crystal Report file
```Sql = "Select StockCode,StockDescr,DateMvt,qtyRec, qtyDispa,Comments, _
Flag From stockbincard " _
& " Where StockCode = '" & idtx & "' "

errflg = False

If Conn.State = ConnectionState.Closed Then
Conn.Open()
End If
XYcommand.Connection = Conn
XYcommand.CommandText = Sql
myReport.SetDataSource(RData)

FormMain = New ReportBinCardView
FormMain.Text = FormMain.Text
FormMain.ShowDialog()```
Fig. 7: The Code for generating the report

Fig. 8: The Formula using Basic Syntax

Fig. 9: The Formula using Crystal Syntax

Fig. 10: The Successful Connection Message

Figure 11: A Typical report display

## About the Author

 Web Developer Nigeria
A Computer Scientist of high programming skill

 Pro

## Comments and Discussions

 First Prev Next
 My vote of 5 manish149822-Sep-13 3:48 manish14982 2-Sep-13 3:48
 My vote of 5 KiranKumar Roy11-Feb-13 22:02 KiranKumar Roy 11-Feb-13 22:02
 Good Article elshan001122-Mar-12 22:21 elshan0011 22-Mar-12 22:21
 My vote of 5 elshan001122-Mar-12 22:20 elshan0011 22-Mar-12 22:20
 Good One pashad27-Dec-11 19:15 pashad 27-Dec-11 19:15
 Last Visit: 31-Dec-99 18:00     Last Update: 26-Jun-17 23:23 Refresh 1

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.