Analizing data to best customers using Crystal Reports

Introduction

In the sales analysis, there is a principle named the Pareto principle also known as the 80-20 rule. The 80-20 rule states that 80% of the effects come from the 20% of the causes and then the remaining 20% effects come from 80% of the causes. Pareto observed that 80% of the land in Italy was owned by 20% of the population. One example in the IT world is that Microsoft has noted that 20% of the most reported bugs are the causes of the 80% of the errors or crashes of the systems. After Pareto observed this principle, a lot of application appeared. One of the most common applications of the Pareto principle is in the sales analysis. Companies perform analysis in their sales orders in order to discover the best customers, that is, the 80% of the sales come from the 20% of the customers.

In this article, I will show how to perform a sales analysis using Crystal Reports. For this example, we will use the AdventureWorks database shipped with SQL Server 2005.
Getting started with the solution

The first step is to create an example solution using Visual Studio.NET named. Then add a Crystal Reports report to the project (see Figure 1).



Figure 1

Now let's define the data schema for the creation of the report using strongly typed Data Set (see Figure 2).



Figure 2

And define a data table and table adapter in the strongly typed Data Set using the following query (see Listing 1, Figure 3 and Figure 4). This query displays the full name of customers and the total due for their sales orders.

select c.LastName+' '+c.FirstName as Fullname, soh.TotalDue as SumTotalDue
from Sales.SalesOrderHeader soh inner join Person.Contact c on soh.ContactID = c.ContactID

Listing 1



Figure 3



Figure 4

Now let's add the XML schema defined by the strongly typed Data Set in the report using the Database Expert wizard. Add the SalesReport table in the Selected Table list (see Figure 5).



Figure 5

Next step is drag and drop the Fullname and SumTotalDue fields from the Field Explorer window onto the report and insert a line to divide the field name from the underlying values. Let's add a title and current date to the report (see Figure 6).



Figure 6

Next step to create the intended report is to group the records by the Fullname field, hide order details, sort by the sum of the total due, and finally include the top 20 percent customers in the report.

In order to group the records by the Fullname field, right-click on the report and select Report|Group Expert from the context menu. Move the Reports Fields|SalesReport.Fullname field from the Available Fields list into the Group By list (see Figure 7).



Figure 7

In order to hide the detail section of the report, you need to right-click on the Details section of the report and select the Suppress (No Drill-Down) from the context menu (see Figure 8).



Figure 8

Next step is to create a sum of the of the total due, we need to right click on the SumTotalDue field on the report and select Insert|Summary option from the context menu. The Insert Summary window enables selecting this field, the operation to perform (in this case, it's a sum) and level of grouping (in this case, it's to the level of the former created group) (see Figure 9).



Figure 9

Drag and drop this summary to the GroupHeaderSection1 (Group Header #1 – SalesReport.Fullname) (see Figure 10).



Figure 10

To sort by the sum of the total due and select the 20 percent best customers (using the total due), we need to right-click on the report Report|Group Sort Expert option from the context menu. In the Group Sort Expert window, we need to select Top Percentage option and select Sum of SalesReport.SumTotalDue and finally enter 20 in the "Where Percentage is" field (see Figure 11).



Figure 11

Now we're ready to run the application and find out the best customers in the AdventureWorks database (see Figure 12).



Figure 12

Conclusion

In this article, I've showed how to perform a sales analysis using Crystal Reports to find out the best customers.