Updating the SalesLogix History Summary Crystal Report

Next up in our updating of Sage SalesLogix standard Crystal reports, History Summary.  On December 16th 2008, I wrote a blog post on the SalesLogix History Reports.  In the post I wrote the following on the History Summary Crystal Report.

Start Excerpt………………..

The History Summary Report is very impressive.  This report is a user based report.  The User group has some useful header information.  The footer displays some roll up information. The report is sorted alphabetically by User last name, then by Account, and then by History date with the latest record displayed first. 

I would advise the same improvement items as in the previous reports in this post.  Other improvements would be to have roll up by Activity types.  Overall, this would be a nice starter report for just about any type of user summaries.

End Excerpt………………….

The following list details what was updated in previous History Reports and will be included in the updated version of this report:

1. Update the layout from Landscape to Portrait.


2. Add a parameter field that allowed the user to filter out database changes. 


3. Insert the HISTORY.LONGNOTES field to replace the HISTORY.NOTES field (which is 255 character text field), then enter some objects and formulas to handle when the field is empty. 


4. Format the duration field to read as hours and minutes. 

The additional change I will make to this report is to allow the user to filter out by different types of History Records (ie…  E-mail, To-Do’s, Meetings, or Phone Call).  Since this is the only item we have not discussed in previous posts I will detail how I handled this challenge in todays blog. 

First I created the parameter.

Name the parameter “HistoryType”, the Type will be “String”, under the Value section add “All”, “E-mail”, “To-Do”, “Meeting”, and “Phone Call” to the list (Note: these are the Default Pick list values, adjust to your needs), under Value Options section I added the Prompt Text of “What Type of History do you want to review?” then set the Prompt with Description only to “True”, Default to “All”, and Allow custom values to “False”.

Since the HISTORY.TYPE stores the ID of the Picklist table, we need to look at SQL to determine what the specific ID’s so we use them for our formula.

This is my SQL query:

SELECT DISTINCT sysdba.HISTORY.TYPE, sysdba.PICKLIST.TEXT
FROM         sysdba.HISTORY LEFT OUTER JOIN
                      sysdba.PICKLIST ON sysdba.HISTORY.TYPE = sysdba.PICKLIST.ID

These are my results:

I now entered a Record Selection Formula of:

(if {?DatabaseChanges} = “No” then {HISTORY.CATEGORY}<>”Database Change”
else
{HISTORY.HISTORYID}>”)
and
(if {?HistoryType} = “Phone Call” Then {HISTORY.TYPE}= 262146
else if {?HistoryType} = “Meetings” Then {HISTORY.TYPE}= 262145
else if {?HistoryType} = “Email” Then {HISTORY.TYPE}= 262154
else if {?HistoryType} = “To-Do” Then {HISTORY.TYPE}= 262147
Else {HISTORY.HISTORYID}>”)

With all of the changes complete I think this already great report is improved.

Big challenge in our next post, we tackle a report that needs a lot of help.