Reports are essential components for every business. For those who use SAP Business One, report creation and maintenance is accomplished through Crystal Reports. You may use it on a daily basis and not even realize it!
If you want to check it out, here’s what you do in SAP:
- Click on the “Administration” module
- Click on “Setup”
- Then click on “General”
- Select the “Report and Layout Manager”
- Surf through some of the reports in the system and you’re bound to find a couple that read “Crystal Reports” in the “Type” column.
Crystal Reports is a powerful tool that offers tons of flexibility when it comes to the creation of reports and document templates, yet many SAP Business One users are unaware of what happens under the hood of a Crystal Report.
Let’s start from scratch!
This is what you’ll see when you create a Crystal Report from scratch. For now, let’s only focus on the red arrow, which happens to be pointing to “Database Field” in the Field Explorer section. Right click on “Database Field” and select “Database Expert.” We’ll select all of our data sources from the Database Expert. There are three data source categories for us to choose from:
- System Tables: The most common. Simply select the tables which contain the fields you want to use and drag them over to your report.
- Views: Requires access to Microsoft SQL Server and a basic understanding of SQL queries. A view is essentially a SQL query that is converted into a virtual table.
- Stored Procedures: Also requires access to Microsoft SQL Server, but may require a deeper understanding of SQL depending on the complexity of the query. A stored procedure acts similarly to a view, but a stored procedure allows you to use parameters.
If you’ve decided to make use of views or stored procedures it’s best to do most of the data manipulation in the query rather than in Crystal Reports; the reason is that Microsoft SQL Server is better suited to the task of manipulating data. Crystal Reports will allow you to enter data manipulation logic, but it just can’t match the speed of Microsoft SQL Server.
Once the sources of data have been selected we can focus on the design aspect of our report. You’ll notice that the Crystal Designer is divided by two small tabs (Design and Preview). You’ll be spending a lot of time working in the Design tab, so let’s break down some on the elements found in this tab.
The Design tab is broken up in five sections into which we’ll drag our data fields. Here’s a quick breakdown of how each section affects your report:
- Report Header: Fields placed in this section appear at the top of the first page of the report.
- Page Header: Fields placed in this section appear on top of each page of the report.
- Details: This section will contain the bulk of the information that will appear on the report.
- Report Footer: Fields placed in this section appear at the bottom of the last page of the report.
- Page Footer: Fields placed in this section appear at the bottom of each page in the report.
We have two options when it comes to placing fields in the sections mentioned above. We can drag the field from the Field Explorer and drop it into any of the five sections of the design tab. For most purposes the drag and drop straight from the source method works just fine, but let’s say that we need to manipulate the field before it get displayed in the report. In order to do that we’ll need to create a Formula Field.
Within a Formula Field we type in our logic and select the field to which the logic will apply. Once we’ve checked that there are no errors in our Formula Field, we can simply drag and drop the Formula Field onto the report.
Formula Fields give us incredible amounts of flexibility when it comes to formatting and manipulating data. By using formulas we can do things like:
- Perform mathematical equations
- Format text so that it’s displayed in either uppercase or lowercase
- Select which part of a string we want to show on the report
- Convert fields from one data type to another
Once you have previewed a report using the “Print Preview” function, the Preview tab will be visible. The Preview tab will show you how your report will look and within the Preview tab you’ll be able to make style changes as well as some data formatting.
That’s essentially how a Crystal Report functions at a very high level. I encourage SAP Business One users to make at least one Crystal Report, even if it’s just a simple one. The experience will allow you to familiarize yourself with the software, and it will give you a better understanding of how SAP stores data. If you have any Crystal related questions don’t hesitate to reach out to our support department; they will be more than happy to help. For businesses and individuals looking for in-depth, extensive professional tutorials of Crystal Reports and SAP Business One, look no further than our Business One Training site.