Fixing the Support Call History Crystal Report

The final report in the History Family is the Support History Crystal Report. On December 16th 2008, I wrote a blog post on the SalesLogix History Reports. In the post I wrote the following on the Support History Crystal Report.

Start Excerpt....................

The Support Call History report is truly a miss placed and poorly written report. The content of the report suggests the report would be better located under the Ticket level. The report is entirely focused on support calls with no reference to the History table what so ever. Not even the Ticket Activities Table is related to this report which would be the place where Associated History records would be saved. The condition filters for the report uses the History table Create User and Create Date fields which will not work since this report does not use the History table at all.

Although the report launched right away in version 7.5, I had a number of errors when I tried to launch from the 7.2.2 version of SalesLogix.

Finally the report puts out inaccurate data. Not all of the qualifying records are displaying and the Account Summary counts are wrong. In trouble shooting I found that some of the formulas do not account for null values correctly. Granted my view could be skewed because I am using evaluation data. I think the intent of the report is good but the development and testing was rushed prior to acceptance as part of the standard SLX. We will see this report again, trust me!

End Excerpt......................

SO our mission here is to rescue this report. When I dug into the issues with this report I discovered that the everything came down to the links created between the tables in the database explorer. So lets review joins in depth. In this report the Ticket table is the main table which in my terminology means the majority of the details I need for this report is from the Ticket table. The rest of the tables are what I call supporting tables. Usually there are ID's in each table that allows the user to align the data from a supporting table to the primary table. The alignment is accomplished through a link. Each link has the ability for three different types of joins - Inner, Left, and Right.

Crystal Help Defines each Join as:

An Inner join is the standard type of join. The result set from an Inner join includes all the records in which the linked field value in both tables is an exact match. For instance, you can use an Inner join to view all customers and the orders they have placed.

CustomerTable	CustomerTable	OrdersTable
Customer ID	Customer Name	Order Amount
52	Allez Distribution	25141.50
53	BG Mountain Inc.	19164.30
53	BG Mountain Inc.	1683.60
57	Hansen MTB Inc.	15716.40
58	La Bomba de Bicicleta	1956.20
60	Mountain Toad	24580.50
62	SFB Inc.	7911.80
63	Sierra Bicycle Group	19766.20
63	Sierra Bicycle Group	12763.95
64	Sierra Mountain	8233.50

Notice how the Customer table and the Orders table both have data represented in the result. This is the issue with the Support Call History report, because of the inner join, the missing data in the supporting table caused the report not to include data from the base table.

The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table. For instance, you can use a Left Outer join to view all customers and the orders they have placed, but you also get a row for every customer who has not placed any orders. These customers appear at the end of the list with blanks in the fields that would otherwise hold order information:

Customer Table	Customer Table	Orders Table
Customer ID	Customer Name	Order Amount
52	Allez Distribution	25141.50
53	BG Mountain Inc.	19164.30
53	BG Mountain Inc.	1683.60
57	Hansen MTB Inc.	15716.40
58	La Bomba de Bicicleta	1956.20
60	Mountain Toad	24580.50
62	SFB Inc.	7911.80
63	Sierra Bicycle Group	19766.20
63	Sierra Bicycle Group	12763.95
64	Sierra Mountain	8233.50
54	Bicicletas Aztecas
55	Deely MTB Inc.

Now notice that the two bottom records in the Customer table are not displaying data from the Orders Table. So for the Support Call History report, when I changed the join to the type of left, it now let the base table display when fields like "Assigned To" or "Recorded By" fields were not populated by data. To fix this report all I did is change the Link options to the Left Outer Join.

I also mentioned that the report was having some issues with null values but I may have been wrong. The change to the left join fixed the issue with miscalculations.

So now that I have the report working I did some clean up with some of our usual changes to include adding a parameter to allow the user to filter out tickets by Status. I am happy to say this report seems to be up and running as it should be. Enjoy!