Improving the Sage SalesLogix Support Follow-up Crystal Report

Today we will get back to fixing, adjusting or improving the standard Crystal Reports that come with an implementation of Sage SalesLogix.  I originally commented about this report on January 29th, 2009 in a post labeled SalesLogix Ticket Reports (Part 2) 

Start Excerpt……………

Now let’s review the SalesLogix Support Follow-up – Sample report.  The report allows you to see a list of Activities that were scheduled in support of resolving the Ticket.


I ran the report and was not prompted for additional parameters.  I believe the report fulfill it’s intent but there are some issues.  The Activity Code on the far right is actually displaying the Ticket Activity ID which brings no real value to the user, this is also the field the list is being sorted on.  I think the Assigned Date would be a better sort field allowing us to see the progress of the resolution.  The Activity Assigned and Completed Dates could be formated to just display the dates.  There could also be an argument that the Activity description would fit in this list, right now you would have to dig into the ticket to discover what the Activity was about.

End Excerpt…………….

So I think we should add some sort of date range parameter to limit the number of records in the report.  I will try and figure out a better way to accomplish the sort using the Assigned Date, I will work with the formating of the date fields and see if I can bring some more detail about the each Activity so the user has all of the important information in the report.  I think I should also organize the data by the assigned user to make this report easier to read.

I am going to first check the report tables to make sure everything looks correct.  I did this by right clicking on the label “Database Field” in the Field Explorer.  Nothing seemed out of the ordinary.  As long as I am in here I will make sure I have a path to the Assigned User or Team.  The TICKET.ASSIGNEDTOID is linked to the SECCODE table through SECRIGHTS, so I know I will use the SECCODE.DESCRIPTION field to list who the assigned user is for each Ticket record.

Next, I will add a Date Range Parameter so we can limit the number of records coming into the report.  The Name for this parameter “Date”, the Type will be “Number”, under the Value section add “30, “60”, “90”,”120″ to the list,  under Value Options section I added the Prompt Text of “How far back do you want to search?” then set the Prompt with Description only to “True”, Default to “30”, and Allow custom values to “False”.  You could also skip the values and just allow the user the ability to add custom values.  Now I added the following to the Report Record Selection formula area: 

{TICKET.RECEIVEDDATE}> (CurrentDate -{?Date})

This formula brings in all records where the Received Date is greater then the Current Date – the value selected in the Date parameter field.  There are many ways of accomplishing this same filtering, I usually use the date range for this but it always seems to be such a hassle entering both dates.  I thought this might be a faster method.  Comments welcomed on this idea or other methods you have seen used.

Next I want to add a group by to sort the records by Ticket Assigned To.  For this we go to Crystals Insert Menu and select “Group” from the list that appears. When the Insert Group dialog opens select SECCODE.DESCRIPTION from the first drop down, then select “OK”.  The new group is created as Group 3, we need to move this group to the Group 1 position.  On the Design Tab, left click and hold on the Group Header 3 label located along the left side of the screen, then drag Group Header 3 to the Group Header 1 Position.  Immediately you should see all of the tickets fall under it’s assigned to user. 

So lets take a look at the Ticket Activity Section.  First I want to change the Group By for now Group 3 which is using the TICKETACTIVITY.ACTIVITYCODE, this field allows no real control over the sort.  So I want to use a date field to give me the order in which the Activities were carried out.  I will right click on the Group Header 3 and select “Change Group” from the list that appears,  When the Change Group Dialog opens, select TICKETACTIVITY.ASSIGNEDDATE from the list, I like Ascending order but we should change the value on the bottom of the interface “This section will be printed” to “for each second”.  I am going to remove the activity Code field from this section because it brings no value to this report.  As I realigned the fields I notice that the Activity Assigned To field is using the SECCODE.DESCRIPTION this would always bring in the Ticket Assigned To user, but I think we would want to see the User that is accomplishing the Activity.  Looking at the Database Expert, I see the name is being pulled in correctly using the UserInfo table so I will change the field to use the USERINFO.USERNAME field instead.  Also while realigning fields I came up with a blank End Date for the Activity.  I thought the report would look better if I added a formula that allowed for the words “Still Open”.  This is the formula I used:


This worked but I want to also draw attention to the fact this activity is still open so I added two formulas to the formating of this field:

Font formula: if {@EndDate} = “Still Open” then crRed else crBlack

Style Formula: if {@EndDate}= “Still Open” then crBold else crRegular

These two formulas changed the font and style of the text if “Still Open” is displayed.

Finally I added the TICKETACTIVITY.ACTIVITYDESC field that provides the details of the Activity to the reader.  Here I combined the label of Activity Description with another formula field that added text if there weren’t any notes added to the description.  That formula reads:

if Not({TICKETACTIVITY.ACTIVITYDESC}>””) then “No Description Provided”

With all of that done, I am struggling with the intent of this report, the report is called Support Follow-up Report but this report provides all activities for any Ticket that meet your date parameter.  How do you define what a follow-up activity for a Ticket is?  I could write a rule that only allows specific Activity Types into this report.  From the eval database those values might be “Customer Update”, “E-Mail”, “Phone Call”, and “Follow-up”.  Or, I could just rename the report to something like Ticket Activity Report because that really is what is going on with the current report layout. 

I renamed the report to Ticket Activity Report, this provides more flexibility for anyone to use the report.  I think the report looks pretty good, there is something off about the layout but I cannot figure what it is.  Overall, this report should give anyone who is looking to see Ticket Activities all in one spot a good start .

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here…

Keep Smiling everyone.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!