Modifying the SalesLogix Expired Contracts Report

Finishing up on the Contract Family of reports, today we will modify the Expired Contracts Crystal Report that comes with a standard Implementation of Sage SalesLogix .  On December 08, 2008 in a blog post labeled SalesLogix Contract and Defect Level Reports, I wrote the following section on the Expired Contract Report.

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

The next report is the Expired Contracts report which indicated to me that the report provides a list of all the expired contracts.  I think the better name for the report would be the Contract Expiration report because this provides a list of all Contracts and their expiration dates.   The report displays very little information but I think with some additional filtering could be a valuable report.  Besides the naming of the report the only other modification I would make is to have the report sort by Expired Contracts, and then Active contracts grouped together by 30, 60, and then 90 days out from expiration. 


End Post—————————————-

My Primary issue is you have to really dig into this report to find the expired Contracts.  In addition to the modifications mentioned I also look at this report and see a lot of white space and one missing field.  There is not enough data to justify using landscape so that is the first thing I modified.  This is done through the File menu on the Crystal Reports Interface and selecting Page set up from the list, you will see the Orientation box toward the middle of the form that opens.  After doing this you will have move some of the field back on to the report.  Play around with field spacing and do what you think looks good clear up the white space.  Remember white space means more paper when you print reports

Lets create the parameter field first because we will refer to it several times in our formulas.  Name this parameter “SortBy”, the Type will be “String”, under the Value section add “Account” and “Expiration” to the list,  under Value Options section I added the Prompt Text of “How do you want to sort this report?” then set the Prompt with Description only to “True”, Default to “Expiration”, and Allow custom values to “False”. 

We will need four new formulas for our modifications.  

Formula 1 The name is “Grouping” with a formula of:

If {@ContractLeft} < 0 then “Expired Contracts”
else if {@ContractLeft} > -1 and {@ContractLeft} < 31 then “Contracts with Under 30 Days Remaining”
else If {@ContractLeft} > 30 and {@ContractLeft} < 61 then “Contracts with Under 60 Days Remaining”
else If {@ContractLeft} > 60 and {@ContractLeft} < 91 then “Contracts with Under 90 Days Remaining”
else “Contracts with Greater then 90 Days Remaining”

Formula 2 The name is “Groupings 2” with a formula of:

If {@ContractLeft} < 0 then 1
else if {@ContractLeft} > -1 and {@ContractLeft} < 31 then 2
else If {@ContractLeft} > 30 and {@ContractLeft} < 61 then 3
else If {@ContractLeft} > 60 and {@ContractLeft} < 91 then 4
else 5

Formula 3  The Name is “SortBy” with a formula of:

if {?SortBy} = ‘Account’ then {@Initial}

else Cstr({@Groupings 2})

Formula 4 The name is SortBy 2″ with a formula of:

if {?SortBy} = ‘Account’ then {ACCOUNT.ACCOUNT}

else {@Groupings}

The next step is to adjust each of the groups in the report.  Changes to Group Header (GH) 1 included changing the Group Options to use the new formula field “SortBy” and adding another section to the group.  Next I went into the section expert for GH1a and unchecked the “Underlay Following Sections” and added the following Suppress Formula:

 {?SortBy} = “Expiration”

Changes to GH1b .  I moved all of the labels from the Page header and added them to this new section.  I then went into the section expert and added the same suppress formula as in GH1a

Changes to Group Header (GH) 2 included changing the Group Options to use the new formula field “SortBy 2” and added the following Suppress formula:

{?SortBy} = “Account”

Next I played around with the report until everything looked fairly decent. The next two screen shots are my examples of what these modifications did to SalesLogix Expired Contract Crystal Report.


Noticed I did change the name.  FYI, the rest of the Contract fell into a final group labeled “Contracts with more then 90 Days”.  I think mission accomplished. 

You can download a free copy of the modified Contract Expiration Crystal Report by following this link.

Get all of our Free SalesLogix Reports here…

Until next week, enjoy Spring!

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!