Fixing the Territory Realignment Report

I am in the early stages of going through and fixing the Crystal Reports that come with a standard Implementation of Sage SalesLogix.  The fixes or adjustments are based on my review of the reports from November  2008 until the middle of February 2009.  The SalesLogix Report I am targeting today is the Territory Realignment Crystal Report.  On November 20th 2008, I wrote a bog post labeled SalesLogix Account Level Reports that contained the following about this report:

The final report we will look at on the Account layer is called Territoryrealign.   If you are looking for a list of Accounts by Account Manager then you have found it here. Condition filtering is set up to use the records Account Manager and Modify Date. 


The report has some parameters established that basically just insert the value you type as a parameter into the appropriate field on the report.  For this example I typed in “Lee The Man” on the Account Manager parameter.  So do not count on filtering  this report using any of the parameters established.  What this report will do is allow you to see on paper who will own what accounts based on rules developed in a specific group. 

End of comments.

So there is not a lot actually wrong with this report.  I see one formating issue and we know the established parameters are not really worth anything.  If you noticed in the beginning of the post I state that this would be a good report if you wanted a list of Accounts by Account Manager. Lets take this thought one step further and make this a report of New Accounts by Account Manager and add a date parameter.

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 also 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 Territoryrealign report under the Account 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. 

The first thing I have been doing to update these reports is to remove any excess tables that do not provide a purpose to the report.  I removed the ADHOCGROUP table, all the other tables were providing data to the report.  

I realize some people may like the report the way it so I am going to format the Create Date in the report just to clean up things a little. This is done by right clicking on the field and selecting Format Field from the menu that appears.  When the Format Editor open you will see a tab called Date and Time.  Along the left hand side you will all of the formatting options available. I chose the format that looked like 03/01/1999 and selected OK.  This removes the time from the value that is displaying in the report. 

A copy of the saved report is available here.  If you are interested in the New Account By Account Manager Report, please keep reading.

To make sure I am not going to loose my changes to the Territoryrealign report, I am going up to my File Menu, select Save As and rename and save the file as CFX_NewAccountsByAccountManager.  With the report saved I changed the title to read as New Account Report.  My plan is to use the Account Manager name to group by, it would make sense to have the Main Phone for the Account replace the Account Manager in the details of the report.  I first changed the label by right clicking and selecting the Edit Text menu item.   This label actually contains the text for both the Acct. Manager and the Owner, I deleted just the “Acct. Manager” text and added the text “Main Phone”.

To add the Group for Account Manager, find the Insert Menu from the top of the Crystal Reports interface.  Select the Menu Item Group…, once the Insert Group form appears select Account_Manager.UserName from the list and select OK. You will see Group #2 appear but it is under the details, so click and hold on the Group #2 Header and drag up to the position of Group #1 header.  The groups should change positions so that detail that was in Group #1 is now in Group #2.  Ensure the font is set to 10, Font weight is set to Bold and adjust the font color to Maroon then save the report.

Next we want to build the Main Phone field.  SalesLogix stores the Phone number as an string so we need to format the field so the number displays like you see it in SLX.  To do this we need to create a formula field.  Actually what we will do is copy and paste the formula fields from the Account Detail Report, why recreate the wheel. To create a new formula, open the Field Explorer, it may already be open along your right side but if it is not then select the View menu and then find Field Explorer in the list that appears.  You are going to right click on the value “X-1 Formula Fields” and select New from the list.  the Formula Name view appears, we want to name the first formula “Telephone_Country“.  Now follow the same process to create another new formula labeled “MainPhone”.

Copy this text into the Telephone_Country formula

Global StringVar Country_Type;

If {ADDRESS.COUNTRY} in [“USA”,”U.S.A”,”US”,”United States”,”U.S.”, “U.S”]
Country_Type:= ‘Standard’
Country_Type:= ‘Standard’
Country_Type:= ‘European’

Copy this text into the MainPhone formula

If  Length ({ACCOUNT.MAINPHONE}) = 10 and {@Telephone_Country} = “Standard”
    “(” + {ACCOUNT.MAINPHONE}[1 to 3] + “) ” + {ACCOUNT.MAINPHONE}[4 to 6] + “-” + {ACCOUNT.MAINPHONE}[7 to 10]
    If  Length ({ACCOUNT.MAINPHONE}) > 14 or Length ({ACCOUNT.MAINPHONE}) < 10
    if Length ({ACCOUNT.MAINPHONE}) = 14 and “(” in {ACCOUNT.MAINPHONE}
    //then “(” + {ACCOUNT.MAINPHONE}[2 to 4] + “) ” + {ACCOUNT.MAINPHONE}[7 to 9] + “-” + {ACCOUNT.MAINPHONE}[11 to 14]
    if Length ({ACCOUNT.MAINPHONE}) = 14 and {@Telephone_Country} = “Standard”
    “(” + {ACCOUNT.MAINPHONE}[1 to 3] + “) ” + {ACCOUNT.MAINPHONE}[4 to 6] + “-” + {ACCOUNT.MAINPHONE}[7 to 10] + ” ” + {ACCOUNT.MAINPHONE}[11 to 14]

When both formulas are saved and closed you can drag and drop the MainPhone formula onto the report replacing the UserName field locate at the top of the second row from the left.  Set the font size to 8.

The last thing we want to do is establish a date parameter that allows us to set how far back we want to search for new Accounts.  Back in the Field Explorer, look for the value “(?) Parameter Fields”, right click and select New from the menu that appears.  When the Create New Parameter form opens, name this parameter “AcctCreateDate” and select a Type of Date.  Under the Value Options on the same form, change the Prompt Text value to ” How far back do you want to search?” and set the Prompt With Description Only value to True.  Select OK.  To make the report honor the parameter, find the Report Menu item, hover on the arrow along the right side of the list by the Selection Formulas menu item, then select the Record sub menu item.  When the formula Workshop opens paste this in as the formula:


I spent some time playing with alignments and highlighting and here is the result:

Download this report here:

Get all of our Free SalesLogix Reports here…

What we have here is an excellent example of how you can take a report that exists, fix it up to something more valuable for your company.  I am having a lot of fun with this project I hope you are enjoying the post, please feel free to comment on content or provide ideas to make these posts better.  Thanks







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!