SAP Crystal Reports is a universal reporting tool that can read data from many different database formats and present it in an organized and professional manner. For most of the widely used database formats, Crystal Reports includes the drivers needed to read them; and if not, most database vendors can provide it. In this article, we’ll show you how to use Crystal Reports to create invoices for your business.
Thanks to SAP, the creator of Crystal Reports, for sponsoring this article.
Crystal Reports is a powerful tool that allows you to extract data from your databases and generate reports. For example, you can extract all sales data to create a quarterly summary or individual customer and sales data from a transaction to create an invoice.
This guide covers some important basic concepts in Crystal Reports, such as creating new formula fields, grouping records, sorting, and summarizing. For the purposes of this guide, we will be using Crystal Reports 2013, Service Pack 7.
Connecting Crystal Reports to your Database
These instructions begin by connecting Crystal Reports to the database that stores the source data for your invoice. The data in a relational database is stored in sections called tables, which are organized similarly to Excel spreadsheets. The data in each of the tables is grouped depending on the type of information that it contains. For example, some might contain customer information, others might contain vendor information, and others might contain inventory information.
Applications such as QuickBooks, SAP, Sage, JobBoss, and all MRP/ERP systems have two sides to them — one to enter the information and another to store it. The application will then use the data from the database depending on the manner of the information flow that is established in the application.
By using the Crystal Reports, you have the flexibility to display the data and generate a report that has a different data flow than the ones given in the source application. This is made possible by minimizing the need for writing code by visually establishing links between the data in various tables and placing the fields in the reports like pieces in a puzzle.
When you open Crystal Reports for the first time, you will see the following screen:
To begin your reporting, go to File – New – Standard Report:
Then, Crystal Reports will ask you where the data you’d like it to connect to is stored. Click on the Create New Connection folder and you will see several subfolders allowing you different ways to connect to your source data.
The connection type will vary based on where your data is stored. For example, the source of your data may be Microsoft Access, Microsoft Excel, an SQL database, or any other program. At the minimum, you should know where the source data is stored and what type of connection it requires to be read. The most widely used ones are ODBC, which is an older connection type, and OLE DB, which is a newer connection type.
The majority of modern business applications use SQL databases, to which you can connect either via the ODBC or OLE protocols. In this example, we’ll show you how to connect to an SQL database using the OLE protocol.
Click on the OLE DB folder, then on the next screen scroll down and click on the highlighted option Microsoft OLE DB Provider for SQL Server.
Click the Next button and you’ll see the screen below:
Here you will enter the server’s name and database. See below for the required connection parameters for our example.
If you are not sure or don’t know what the connection parameters to the server in your company are, then you will have to consult with your IT department to confirm.
Once you enter the connection parameters, click on the Next button and you’ll see the screen below. Click on the Finish button on this screen.
Once the connection is established, you’ll see the screen below. It displays the name of the SQL server (cp-sql01\m1) and the name of the database (M1_OS). If you click on the M1_OS (or the corresponding name in your case) and navigate to dbo, then you can see all of the tables in the database where the data is stored.
Next, we have to locate the tables where the data that will be used in the reports is stored. This requires some knowledge of the application and the database and to have a general idea of what data should go where. It’s to be expected that the data for the invoices is to be stored in the Accounts Receivable module/database table.
As mentioned above, the first step is to locate the table(s) where the information is stored. For invoicing, we’ll need the tables that contain the general information for the invoice (who we’ll bill – the customers) and the details (what we’ll bill for – goods or services). In our example the information for the invoices is stored in the following tables:
- AR Invoice
- AR Invoice Lines
We’ll locate each of the tables, click on the table to highlight it, and then click on the arrow in the middle of the screen to mark that table as a selected one. Next, in the upper left corner of the screen, click on the Links tab.
The following screen will then appear:
On this screen you can click on each table, move it around, and expand it so that you can see the individual fields in it as shown below:
These fields in the tables shown are equivalent to the header information in the columns in the database. The actual records are stored under each of these field labels.
Adding Data from your Database
To make a meaningful report, you’ll need the information that is in several tables. In order for Crystal Reports to pull the data from these tables, they have to be linked so they can “talk” to each other. A report can be made just with the information from a single table; however, information from multiple tables will create a more complete report.
The way we link will affect how the data is presented in the report. On a basic level, you match the key fields from one master table to the key fields from another table. Information always flows from one master table toward the rest of the table and it’s always in one direction.
To link tables, choose a field that contains important and unique information (such as invoice number) to link it to the secondary table for the Invoice Lines. Click on the field, drag it across, and release it over the matching filed in the secondary table. In the same way, we link the information from the AR Invoice table with the organizations table.
Now, right click on the link itself and choose the Link Options, then choose Left Outer Join and click the OK button. Repeat these steps for the other links. Please note that the link type has changed now and it is with arrows pointing to the target tables. Click the OK button in the lower-right corner of the screen and you will be taken to the screen shown below.
If you click on the Database Fields in the Field Explorer (at the top of the right panel on the screen) you can see the three tables that we have just connected. By clicking on each of the tables you can expand the view further and see the individual fields in each of them.
The next step is to drag the fields that we want to be shown in the report from the tables that are on the right-side panel. Simply click on the field, drag it to the details section of the report, and release it to place it. It’s important that you release the fields in the correct place in the details section of the report. The fields’ labels (descriptions) are automatically placed above the fields in the Page Header section of the report. Each field that’s inserted into the report can be resized by clicking on the dots on the border of the field and dragging it.
If you run out of space on the page and you plan to add more fields to it, you can click on File – Page Setup and change the layout of the screen to landscape or choose a different size of paper. The default paper size is Letter 8.5” x 11”.
If you refresh your screen, you can preview your report. As you can see in our example below, the report shows all of the invoices ever generated for all of our customers.
Formatting your Invoice
The data in the field is not yet formatted to be presentable. We’d also like to be able to print an individual invoice or to batch print several of them using our company’s logo.
In order to format the data for the dates, right-click on the actual field (any field that has data in that column) and choose Format Field. On the next screen, you can choose the data layout format you prefer. You can format the rest of the fields using the same steps.
Now, we’d like to create an individual invoice. An invoice can have one or multiple line items that list the products or services that we bill the customer for. In this example, we’ll choose an invoice that has two line items on it. Please note that in our example, the invoice number 4276P9 is shown twice. That is because this invoice is linked to the invoice details table in the database and in it there are two lines for the two items that the customer is being billed for.
To select an individual invoice to be printed, click on the field that has the invoice number in it and click on the Select Expert (funnel icon) from the top menu. From the drop-down menu, choose “equal to” and enter the invoice number that you’ll select and click the Ok button. The report now shows just that invoice number 4276P9.
In order to make our example look more like an invoice, we’ll move the items shown on the page to more closely resemble a typical invoice. Move the items that need to be printed just once – such as the customer name, invoice number, invoice date, etc. — from the Details section of the report to the Page Header section.
In the process we can delete the field’s labels. This won’t delete the data fields like it would have previously. Also, please note that because we moved the invoice number, invoice date, and customer field from the details section to the report header section the records are not duplicated anymore. We’ll keep the line items in the details section because there are two of them and we’d like both of them to be shown to the customer in order to provide the breakdown of the costs.
If you click on the Design tab on the far-left of the screen, you can see the screen as shown below.
Drag the horizontal lines to create more space in the Report Header section. On the very left of the screen in the top menu is the icon for inserting text objects. Click on it and, using the cross, drag the rectangle in which we’ll enter the text INVOICE into the Report Header section.
In the right corner of the same Report Header section, we can insert our company’s logo. Click on the insert picture icon, navigate to where the file is located on your computer, and insert it into the report. You can resize it to fit the available space and to the shape that you like.
The next step is to organize the rest of the data on the invoice form. We’ll move the field for the invoice number and invoice date to a new position. Also, we’ll insert a line that separates the logo and the rest of the data below it. The icons for drawing lines and shapes are located in the top menu.
Now we can enter the static data about our company, such as its name, address, etc. We do this in the same way that we added “INVOICE.” In our example, we’ll add this under the company logo.
Now let’s arrange the dynamic data, such as the customer’s address, in the same section along the left. The information here will be changing depending which invoice number we’ll print. Because of that, we’ll place the fields from the database. All of the fields used from the database are being marked with a green checkmark in the field explorer in the right panel.
Please note that in this example, the data fields are blacked out because this we’ve used live data from real customers in a database.
Next, we’ll arrange the details section. We’ll move the fields that describe what we’re billing the customer for and the parts or services that are being itemized on each line. We can insert lines that separate each item land add any additional relevant information.
Adding Calculations to your Invoice
Next, we’ll show how to insert a calculated field on the invoice. Let’s say the total per line “Extended Price” is not in any of the fields in the database. We can calculate it by creating a formula field for it. To do so, we’ll go to the Field Explorer, right-click on the formula field, and choose New. On the next screen, enter the name for the new field and click OK.
You’ll now be asked to construct a formula for the newly added calculation field. In this case, we’ll enter a simple formula that calculates the subtotal price using the invoiced quantity and the unit price. Both the invoiced quantity and the unit price fields are dragged and dropped from the window above where the fields in the tables that we’ve linked to previously are displayed.
If you expand the sections in the different windows above, they have different functions and operators. The functions can be used for manipulation with different data types. For our purposes, we can save and close this new calculated field that we’ve just created by clicking Save and Close in the upper-left corner.
Now we have to show the total amount of the invoice that we’re billing the customer. To do so, right-click on the field that is to be summarized — in this case, “Extended Price,” and choose “Summary.” On the next screen, choose the default options and click the OK button to insert the summary.
If we want to print a different invoice then we have just to select a new invoice number with the funnel icon as it was shown in the beginning of this section.
Printing Multiple Invoices
If we want to print multiple invoices at once then we have to make a few modifications to the report. First, we’ll insert a group for the invoice number; to do so, click on the actual invoice number field, go to Insert – Group, and click OK.
Next, click on the Design tab, right-click on the Group Header #1 section, and choose Section Expert. On the next screen, check the boxes “Suppress” and “Hide” and click OK. The section will change the appearance (see below) and it won’t show on the invoice. Also, we’ll move the invoice total field from the Report Footer section to Group Footer #1 section.
Right-click on the Group Footer #1 section and choose Section Expert. Click on the Paging tab and click on the “Reset the Page Number After” and “New Page After” checkboxes. Click on the “X+2” button that is next to “New Page After.” In the next screen, enter “not onlastrecord” and click on Save and Close. Now you can see that the button has changed to show that it has a command string behind it. Click OK on this screen to return to the report.
These steps have allowed several invoices to be printed at once. With the commands that we entered, we have instructed Crystal Reports to insert and print each invoice number on a new page — but to stop once the last invoice has printed, not insert a new page.
By using the funnel icon and choosing the option “one of” you can select several invoices to be printed at once and each one of them will be printed on a separate page.
You can see below that we have two separate invoices no. 4276P3 and 4276P9 printed on separate pages. If you go to File – Print you can print them at the same time. With the funnel icon you can select to print as many as you want or you can select to print a range of invoice numbers.
The Bottom Line
You can add as many details to your invoice as your business needs. Once you’re happy with it, make sure to save the file — that way, the next time you want to print an invoice, you only need to open the Crystal Reports file and select the invoice number to be printed.
The advantage of this method, and of Crystal Reports in general, is that you minimize the need to write code and can customize the invoice, or any other report, to suit your business’ needs.