Improving the SalesLogix Contract Crystal Report

Still working through fixing, adjusting, or updating the Crystal Reports that come with a standard Implementation of Sage SalesLogix.  I have moved into the Contract Family of the reports.  On December 08, 2008 in a blog post labeled SalesLogix Contract and Defect Level Reports, I wrote the following section on the Contract Report.


Begin Post————————————


The Contract report provides very high level detail of the agreements that you have in the system along with some roll up of data.  This report is a candidate for major modification.  I would like to see the list of covered Assets and Tickets that have been worked under each Contract.  I would add a parameter for ticket with in the last 30, 60, or 90 days.  I would also include parameters for Current or Expired Contracts.



 End Post —————————————


I can do most of what I said I wanted to do fairly quickly. But as much as I hate to admit it, I am having difficulty trying to figure out the 30, 60, and 90 day item for just related Tickets. So today I will stick to the other items mentioned in the post.  If I come up with a good way of accomplishing that item I will update this blog.


In order to make adjustments to any Crystal Reports in SalesLogix (SLX) you need to have access to a machine with the SLX Architect and Crystal Reports installed.  You will need your SLX Admin Password from your Administrator.


First thing to do is log into into the SLX Architect. Close the Open Project window that first opens and open the Manage Crystal Reports view via the Manage/Reports menu item. Highlight the Contract Report report under the Contact family and select the Check Out icon in the tool bar near the top of the view.  Then select the Launch Crystal Report System icon on the tool bar located all the way to the right. When the report opens, use the key combination of CTRL R to run the report against your SalesLogix database this makes the database connection needed for your sub reports. 


All tables are providing data so no adjustments are needed to the main report Database Expert.  I do notice the main report has the “Period” label but the field was never added. I found the field in the Contract table, added it to the report, formatted to display number with no decimal.


We need 2 subreports to bring in the Asset (or AccountProducts) and Tickets.  So, next I added two new section in Group Header 1. 


To create the subreport for Assets, I went to the Insert menu in the Crystal Interface and selected “Subreport” from the list.  The Insert Subreport interface opens, we are going to select the “Create a subreport with the Report Wizard” selection.  Name the report “sub_Asset” and select the “Report Wizard” button.  


Data is the first step in the wizard, the interface allows us to select the tables needed for our subreport. The ContractItem is the table that provides the relationship between an Accounts Contracts and their Assets.  We will also need the AccountProduct table.  Once the tables are in you will be prompted to update the links. The only link required between the two tables uses the AccountProductId field, delete all otheres.  Once the link is established right click on the line and update the the join as a Left Outer.


Next up in the Wizard is selecting the data fields. All of the fields we want to display in the subreport are in the AccountProduct table.  I will leave it up to you to decide which are important for your situation. 

Once the fields are selected you can select the “Finish” button near the bottom of the wizard.


Select the “Link” tab on the Insert Subreport form.  Find the Contract.ContractId field in the Upper left hand panel and select the right arrow in the middle section of the view to move the field into the Upper Right panel.  The ContractItem.ContractId should auto populate the lower right field.  Make sure the the “Select data in subreport based on field” check box is checked.


Select Ok on the bottom of the Insert Subreport view.  Immediately you will notice a box attached to your cursor, place this report in section Group Header 1b.  Play with formating fields, labels, and alignment. 


You will need another subreport for Tickets related to a Contract.  This report will go into Group Header 1c.  Follow the same process as with the Asset subreport.  The primary difference will be in selecting the tables.  You will use the ContractIncident, Ticket, AccountProduct, and (in my example) the Picklist table.  The ContractIncident table is linked to Tickets using TicketId field.  Tickets are link to the AccountProduct table using the AccountProductId field.  Because I want to display the Status of the Ticket I will link Picklist.ItemId field to the the Ticket.StatusCode field which will then allow me to used the Picklist.Text field to display the Ticket Status. Remember to change all of the links to left outer joins.


We do not want the Assets or Tickets subreports to display if they do not contain records so I copied the following into each of the  subreport sections suppression formulas.


For Asset subreport


IsNull({CONTRACTITEM.CONTRACTID})


For Tickets subreport


IsNull({CONTRACTINCIDENT.CONTRACTID})


We need three parameters for our main report.  One each for whether or not to display related Assets and Tickets, and one for displaying Active, Not Active, or All Contracts.


For the Assets parameter.


Name this parameter “ShowAssets”, the Type will be “String”, under the Value section add “Yes” and “No” to the list,  under Value Options section I added the Prompt Text of “Display Assets related to each Contract?” then set the Prompt with Description only to “True”, Default to “Yes”, and Allow custom values to “False”. 


Write the following formula to the Group Header 1b suppression formula Display Tickets related to each Contract?


{?ShowAssets}=’No’


For the Ticket parameter.


Name this parameter “ShowTickets”, the Type will be “String”, under the Value section add “Yes” and “No” to the list,  under Value Options section I added the Prompt Text of “Display Tickets related to each Contract?” then set the Prompt with Description only to “True”, Default to “Yes”, and Allow custom values to “False”. 


Write the following formula to the Group Header 1b suppression formula Display Tickets related to each Contract?


{?ShowTickets}=’No’


For the Contract Status parameter.


Name this parameter “DisplayContract”, the Type will be “String”, under the Value section add “Active”, “Not Active”, and “All” to the list,  under Value Options section I added the Prompt Text of “Display Contracts that are?” then set the Prompt with Description only to “True”, Default to “All”, and Allow custom values to “False”. 


Click on the Report menu item in the Crystal Interface and select Record, when the Formula workshop opens add this formula:


If {?DisplayContract} = ‘Active’ then {CONTRACT.ISACTIVE} = ‘T’


else if {?DisplayContract} = ‘Not Active’ then {CONTRACT.ISACTIVE} = ‘F’


Else {CONTRACT.CONTRACTID}>”


That should do it, here is a picture of the updated report.




The report is available for Free Download using this link.


Get all of our Free SalesLogix Reports here…


 


 

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!