Combing the SalesLogix Ticket Closed – “By Assigned” and “By Received” Crystal Reports

I wrote about these two reports back on Feb 12 2009 in a Post labeled SalesLogix Ticket Reports (Part 6) ,   Since the entire post is being refered to I will let you follow the link to see the detail.  These two reports are essentially duplicates of each other so I decided to make them one report and add the ability to change the group by based on user selected parameter.  My second review of the reports showed the report are not built correctly to meet their intent,  Group1 is sorted by SECCODE which is related to the TicketOwner and has nothing to do with Assigned to or Received by fields.  We will set the tables to bring in the correct data

First I want to create a parameter that allows the user to select which report they want to run.  There is already a parameter established to provide Data Range.  Now we will provide a parameter to allow the User to select either By Received or By Assigned.  Name the parameter “WhichReport”, the Type will be “String”, under the Value section add “Ticket Closed – By Received” and “Ticket Closed – By Assigned”, under Value Options section I added the Prompt Text of “Which Report do you want to run? ” then set the Prompt with Description only to “True”, and Allow custom values to “False”.

With the Parameter set I want to set the Report Header Label up so the reports is correctly labeled.   I created a new Formula field name ReportLabel and added the following formula.

{?WhichReport}

This field was then added to the report and formatted equally to the preexisting label.

Now I will fix the data coming into the report.  In the Database Expert I removed all of the tables but TICKET, PICKLIST, SECRIGHTs, SECCODE and then added another set of the SECRIGHT and SECCODE tables.  I kept the link for TICKET to PICKLIST table.  I removed all other links because they were wrong.  I built one Link from TICKET.ASSIGNEDTOID to the SECRIGHTS.SECCODEID then SECRIGHTS.SECCODEID to SECCODE.SECCODEID, the same links were created for the additional set of tables I added starting at TICKET RECEIVEDBYID. 

Since this report is labeled Closed Tickets I think we should make sure the Tickets being counted should be closed.  So I adjusted the Report Selection formula:

{PICKLIST.TEXT} = “Closed”

and

{TICKET.CREATEDATE} in {?Date Range}

Next I Created a new formula field labeled SECCODE, the following is the formula for the field.

If {?WhichReport} = “Ticket Closed – By Received” then CStr({SECCODE_1.SECCODEDESC})

Else If {?WhichReport} = “Ticket Closed – By Assigned” then Cstr({SECCODE.SECCODEDESC})

Now we need to adjust the label formula field to use the SECCODE, here is the formula.

 if {@SECCODE} = “” then “Un-Assigned”
else
{@SECCODE}

The last step is to adjust the Group By for Group 1 to use the SECCODE formula field.  The out come is a report I will call Closed Ticket Summary report that can be sorted by either the Assigned To or Received By fields.  Here is an example of the report using the Received By grouping.

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here…