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 calculate cumulative annual growth rates (CAGR).
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, as we’ll show you in this article, your CAGR. The benefit of using Crystal Reports to calculate your CAGR is that it uses live data from your database, which means it’s automatically updated as your data changes and time spans for your calculations are easy to update.
For the purposes of this guide, we’ll 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 reports. 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.
Adding Data from your Database
The purpose of a CAGR report is to show the cumulative growth over a period of time. For the purposes of this guide, we will calculate the growth in sales; therefore, we will need to access the sales orders section of our database — specifically:
- Sales Orders
- Sales Orders Lines
Both of which are located in the sales orders table.
Locate each table, click on them to highlight them, then click on the arrow in the middle of the screen to mark that table as selected.
Next, click on the Links tab in the upper-left corner. This will display a new screen where you can move the tables around and expand them to view their individual fields. These fields are the equivalent to the header information in the columns in the Excel spreadsheet. Under each heading are the individual records.
For our report to be meaningful, we’ll need to extract data from several different tables. In order for Crystal Reports to do that, they have to be linked so they can “talk” to each other. How we link those tables will affect how the data will be presented in the report. On a basic level, you match the key fields from one master table to the key fields in another table.
Information always flows from one master table toward the rest of the tables in one direction. To link tables, choose a field that contains unique information, such as sales order number, to link to the secondary table for the Sales Orders Lines. To do this, click on the field you choose, drag it across, and release it over the matching field in the secondary table.
Once you’ve linked the tables, right-click on the link itself and choose the Link Options, then Left Outer Join, then click the OK button to be directed to the screen below.
If you click on the Database Fields in the Field Explorer in the top of the panel on the right, you can see the two 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.
Please note that there are two types of views of the report – the first is the preview as shown in the first screenshot below and the second one is Design shown in the second. The tab in the upper-left corner of the screen gives you the option to see either one of them.
Our next step is to drag the fields that we want to include in the report from the tables on the right. To do this, simply click on the field and drag it into the details section of the report and release it.
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, you can go to the File – Page Setup menu 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”.
You can refresh your screen to see what the report looks like. As you can see in the screenshot below, our report shows all of the sales orders ever generated. Also, the data in the field isn’t formatted to be presentable.
Formatting your CAGR Report
To format the data for the dates, right-click on the actual field (any field that contains data in that column) and choose Format Field. You can format the rest of the fields in the same way.
For this report we’ll need the summaries for the annual sales and not the individual sales orders. So, instead of having a report that contains 289 pages with individual records, we’ll summarize the sales for each year. To do so, click on the columns that contain the sales order date, go to Insert – Group, and choose the option “for each year.” Click OK and the results will appear as below. As you can see, the group for the year is inserted in the dates column.
To display the total sales per year, click on the total order amount, go to Insert — Summary, choose Group #1, and click OK. You can now see that the total sales amount is shown for each year in the far-right column.
Because we’re reporting on the cumulative annual growth rate, not individual sales orders, let’s move the individual orders out of the view. Click on the Design tab in the upper-left corner of the screen, right-click on the Details section, and choose Hide. The following screen will appear:
Click on the Preview button to view the results. Now, we’ll rename the headings of the columns, remove any unnecessary columns with unneeded data, and move the remaining data fields closer to each other.
You’ll notice that the records for the year and the total sales amounts are not in the same row. To fix that, go to the Design tab, click on the Field name: Group #1 that is located in the Group Header #1 section, and drag it to the Group Footer #1 section and release it next to the total sales per year. If you click on the Preview tab again you can see the results as shown below.
Looking at our data, there is a gap between 2005 and 2012. To remove 2005 to eliminate the gap, go to the Design tab, then the Group Footer #1 section, right-click and choose Section Expert On the screen that will appear, check the box beside “Suppress” and click on X+2 button next to it. Enter the formula as shown below, then click Save & Close. This will remove 2005 from the report.
Follow the same steps to remove the data from 2018, since we only have partial data for the year.
Using the CAGR option presents our data as a consistent growth rate and smooths out any volatility from year-to-year changes.
Calculating your Cumulative Annual Growth Rate
Next, we want to calculate the year-to-year growth rate. This is done by using two formulas for each of the years presented in the range 2012 – 2017, which are shown below.
Using the values from the annual sales as well as the year-to-year percentage change of the sales, we can calculate the CAGR using the formula shown below.
Finally, we want to visually represent the sales in a chart. The first step is to use the formulas shown below.
Using the values that the formula will calculate, we can create columns for CAGR. Go to Insert — Chart and place it in the Report — Footer section. The chart will use the first two columns in the report as defaults in order to construct the graphics.
Right-click on the chart, go to Chart – Expert, go to the Data tab, and choose the values you want to show on each of the axis on the chart. The rest of the formatting of the chart (axis labels, title, legend, tend lines, value labels, colors, styles) functions similarly to Microsoft Excel.
Clicking on the Advanced button under the Data tab will give you the option to add or remove other data series to or from the report.
The Bottom Line
Presenting your CAGR in chart form helps you to visually compare the growth between sets of data, such as company divisions or product groups, in a way that’s easier to understand than simply reading the data.