Adding to the SalesLogix Support Call Turn Around Crystal Report

Today we are going to look at the SalesLogix Support Call Turn Around Crystal Report.  I wrote about this report on January 27th 2009 in a post labeled SalesLogix Ticket Reports (Part 1).  This is what was written about the report. 

Start Excerpt…………….

Now lets look at the Support Call Turn Around report.  The purpose of this report is to provide the number of hours it took to close a ticket.  When you run the report from SalesLogix you will prompted for a Date Range.  The date range is then compared to the TICKET.CLOSEDATE to select records for the report. 

From my point of view all of the data is displaying as intended.  I think there is enough room in the report to add additional items like the Tickets Account and Contact names.  I also think it would be important to sort by the Actual Time open.  I mean if the intent of the report would be to find the tickets that took longer to close. This change would display those records up front allowing you start your research instead of having to spend time searching for the records from within the report.

End Excerpt……………..

With my second review I agree with everything I stated in the first review.  The only other thing I can suggest is that we use some code from an earlier post to change the Actual Time Open Column to display the Hours and Minutes.  But since the code is already written this should not be very difficult to accomplish. 

The first thing I am going to do today is add the Contact table to the list of tables available for the report.  To add this table I clicked on Crystals Database menu and selected “Database Expert” from the list that appears.  I found and highlighted the Contact table in the list on the left and selected the arrow pointing to the right located near the center of the open interface.  Then I selected the “Link” tab at the top of the open interface, when the tab opened I found that Crystal made the correct link to the Ticket table and all I had to do was make the join a left join which is done by clicking on the line forming the link between the tables and selecting the “Link Options” button located on the right side of the interface.  Selecting OK, I am returned to the report.

Having access to the Contact table I know want to create a FullName field.  Back in the Field Explorer tree control find the entry for “Formula Fields”, right click on the entry and select New from the list that appears, name the formula “FullName”  When the Formula Editor opens add this formula:

{CONTACT.FIRSTNAME}+” “+{CONTACT.LASTNAME}

Now I need some room to add the Account and Contact to the list.  I like where Ticket number is located, I believe the best location for the Account and Contact data is in a single column next to the Ticket Number.  So I closed the space between the fields Received Date/Time, Completed Date/Time, Completed By, Urgency, and Actual Time Open fields.  I then selected the Account.Account and the new Contact Formula fields in the space that opened up. 

Now to address sorting, the field we need to sort by is already available, it is a formula field labeled Hours.  To get the sort we need we will create a new group by clicking on the Crystal Insert Menu and selecting “Group” from the list that appears.  When the insert Group interface opens I searched for the Hours field in the drop down. and changed the direction from “in ascending order” to “in descending order”.  Crystal automatically creates the new group as Group 2.  So in the Design mode I clicked and held on Group Header 2 and dragged it to the Group Header 1 position.  I then right clicked on Group Header 1 and selected “Section Expert” from the list that appeared and then selected the Suppress Check Box. The sort is in place.

The “Hours” field is also displaying the data for the Actual Time Open column.  This is the field that we will convert to display Hours and Minutes.   I searched back in previous post and one labeled Improving the SalesLogix History By Account Crystal Report contained the formula to convert the data to Hours and Minutes.

So again go into the Field Explorer tree control find the entry for “Formula Fields”, right click on the entry and select New from the list that appears, name the formula “ConvertTime”  When the Formula Editor opens add this formula:

if {@Hours} >59 then
    if {@Hours}/60 < 10
        then Left(CStr({@Hours}/60),1)+” Hour(s) ” +
            if (left(CStr(Remainder({@Hours},60)),2) + ” Min”) = “0. Min”
            then “”
            else left(CStr(Remainder({@Hours},60)),2) + ” Min”
    else Left(CStr({@Hours}/60),2)+” Hour(s) ” +
            if (left(CStr(Remainder({@Hours},60)),2) + ” Min”) = “0. Min”
            then “”    
            else left(CStr(Remainder({@Hours},60)),2) + ” Min”
else
    if (Left(CStr({@Hours}),2)+ ” Min”) = “0. Min”   
    then “None”
    else Left(CStr({@Hours}),2)+ ” Min”

Now I removed the “Hours” field from under the Actual Time Open column and added the new formula field.  I justified the text in the field to the right and adjusted the font size to match the rest of the report. I then adjusted the Average Turn around time using the same method.  Here is the modified report.

So thats it, we met our goals and my boss will be happy that it did not take very long.  As always you can down load this updated report using this link.

Get all of our Free SalesLogix Reports here…

Stay well until next week.  Tonight I get to go home and live through a party for my now 12 year old daughter.  7 – 12 year old Girls, this should be entertaining, and noisy.  Please Keep Smiling, I will try to also.  Geo