Fixing the SalesLogix Account Detail Crystal Report

This next post is a long one but a good one if you want some experience using BuisnessObjects Crystal Reports.  Today we are fixing the Account Detail report.  This is a report that come standard with an implementation of Sage SalesLogix.  Over the past few months I have been writing a review on all 60 standard reports and I just started a series of posts that talks the reader through the process I used to update the report. This is the second installment. 


On November 20th 2008, I wrote a blog post labeled SalesLogix Account Level Reports, In the post I wrote the following about the Account Detail Report:


The Account Detail Report provides Contact, Opportunity, History, and Activities for an Account.  This would be a good report to use at Account review meetings.  Condition filtering is set up to use the Account Manager and Modify Date.  The User is provided several report parameters to setup the layout and whether to display Opportunities, Activities, and History.    I am not going to provide a screen shot of this report, it is just to big.  I think this report needs a lot of work but I also think with the proper changes this report could replace several others in the system.  The current layout uses a lot of space.  I would include the ability to filter out Contacts, and provide a way to easily filter how far back to go with History records.


I didn’t show this report last time because it’s just gigantic report.  So what I will do today is show you each area of the report, original and then modified, so you can see the improvements.


Our goal is to get people in their system and start writing reports, this one will expose you to a lot of the areas of Crystal Reports.  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 Account Details – Sample 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.


This is the original Account section of the report.




The first thing I notice as I start digging into this report is that there are a lot of fields and sub reports running out side the report boundaries.  I checked back to SLX version 7.0.0 and found the same issue.  The simple fix is to set the orientation of the report to Landscape, but that will limit the amount of data about an account you can see in one page.  So I will adapt the report to portrait.


In the Account section,  I am going to remove the following fields: E-mail, Misc Telephone, SIC Code and Nat’l Account.  Once the fields are removed I tightened everything up and moved the fields closer together to fit within the boundaries of the report. I then moved the field in Group Header 1a to make it line up better with account information.  Nothing really fancy, anyone can make these changes fairly quickly. 


A couple of tips.  I personally like to remove both the horizontal and vertical guidelines, you do this by right clicking on the ruler and selecting the appropriate menu items.  Then, use the File/Options menu, when the Options view appears remove the check from the Snap to Grid check box.  Theses changes allows the user to use the CTRL key with your key board direction arrows to freely and more accurately move fields around the form in small increments.  


To align a fields quickly you simply select a field that is out of alignment, then while holding down the Shift key, select a field you want to align to, now right click on the align to field and select the align menu item, then select the correct sub menu item that best aligns your field.  Remember the CTRL Z keys for a quick undo.


This is the Account section after my changes:


 


The Contacts section of this report is a sub report, this is what the section looks like before we dig in. 



Again we see data of this portion of the report moving off the edge, I also see some fields that we could probably do with out.  I will remove the following “Home Tel:”, “Web:”, and “AcctOwner:”  If you look at the example you can see the name and address info are in a separate section from the detail so I think we try and bring this all together following the Account layout.  Finally we will add a parameter to allow the user to choose whether or not to display the Contact info on the report.


I opened the sub report by right clicking the Contact sub report control on the main report and selecting the menu option of Edit Sub Report.  This adds another tab under the name of the report labeled Contacts.RPT.  The first thing I am going to do is remove all of these unneeded groups and fields.  I deleted Report Header 1.  I left Group Header 1 and Group Header 2a as is.  Group Header 2b was made big enough to hold all of the fields and I moved all of the Contact detail fields into this section.  Now I deleted all of the other section of Group 2 (C-O) bringing the report down to three sections. 


I want two columns of Contact details so I played around until I got the first label and fields for both columns aligned with the Account detail information in the main report.  I then aligned the other fields with their labels under those fields.  I tried to regroup the fields in an order that made sense and I was careful not to leave to much white space. 


I simplified the address by using an already existing formula field labeled CityStatePostalCode(CSPC).  I first pulled in the Address1 and Address2 fields into Group2b and then I pulled the CSPC field in twice.  I aligned the Address1 field under the TrimFullName field but I left just enough room to allow the name to display when I allowed the overlay of Group2a onto Group2b.  I then aligned Address2 and one of the CSPC fields under Address1.  My plan is to suppress the Address2 field and the CSPC1 field underneath Address2 if the Address2 field does not contain data.  I will then lay the CSPC2 field over the Address2 field and suppress it if the Address2 field contains data.


Here are the formulas I used to suppress Address2 and CSPC1:


IsNull({ADDRESS.ADDRESS2}) or {ADDRESS.ADDRESS2}=””


Here is the formula used to hide CSPC2, the field over Address2:


{ADDRESS.ADDRESS2}>””


To enter a formula to suppress a field you simply right click on the field you want to suppress and select “Format Field” from the menu.  When the Format Editor opens look for the Common tab along the top, along the left side you will see the word Suppress and directly across from it on the other side you will see a little button with an X-2 and a pencil underneath it. Select the button to Open the Formula Workshop Editor, write your formula in the lower box where your cursor appears.  We will see a lot of this area as we move through fixing reports.


After I had the Address block established I decided to move the E-mail address out of the details area to give it a little more room to grow if needed. 


To add the parameter that allows you not to include Contacts in the report.  Open the Field Explorer, look for the label of Parameter fields, right click and select “New” from the menu.  When the Edit Parameter view appears, Name the parameter “View Contacts”, select from the Type drop down list the value “Boolean”, under the Value list in the center of the screen enter a description for the True value of “Yes” and a description of “No” for the “False” value.  Toward the bottom of the view you will see a list of Value Options, set the Prompt Text Setting to “Would you like to include Contacts on this report?”, set Prompt With Description Only setting to “True”, and set the Default Value setting to “True”.  Select OK and the Edit Parameter view will close. 


Now right click on the Parameter Fields label in the Field Explorer again, this time select “Set Parameter Order….” from the menu that appears.  When the Parameter Order view appears you will see our new parameter on the bottom of the list.  highlight the Contacts view parameter and you will see the two little arrows on the upper right hand side of the list activate, select the up arrow three times and our View Contacts parameter will move up the list.


Next, right click on group Header 1c and select Section Expert from the menu that appears, find the Suppress check box and select the formula button directly across from the field.  When the Formula Workshop opens you will see an existing formula for suppressing this section of the report, lets add the following to the formula:


or
{?View Contacts} = false


That’s all we have to do to add this parameter. To test, simply select F5, select the “Select New Parameters” option and you will see the list of parameters appear with View Contacts parameter defaulted to “Yes”.


Here is the modified version of the Contact section:



 


I am one of those guys that if the data is redundant I believe it should not be on the report,  In the example of the Contact section, most of the time the Contacts had the same address as the Account.  To avoid seeing the address time and time again I added another address table into the subreport and linked it to the Account.AddressId and then added the following formula to each Address fields suppression formula.


{ADDRESS.ADDRESS1}={ADDRESS_1.ADDRESS1}


This suppressed the address field if the Contact primary Address1 = the Account Primmary Address1.


Now lets take a look at the Activities and History section of this report. Since the changes will be similar for both areas we will cover both at the same time. Here is the example of the Activities section prior to modifications:



There is not a whole lot to do here, the biggest thing I see is that the Opportunity description is running off of the right side of the report.  I think the best way of handling this would be to add an additional area for fields related to Opportunities and Tickets if associated with the Activity. Other then that it is just tightening things up a little. 


So the first step is to add the Opportunity and Ticket tables to the Activity sub report.  Make sure you have run the main report recently using the CTRL R Key function that comes with Crystal to ensure the database connection has been made.  Right click on the Activity sub report and select the Edit Sub Report menu item. With the Field Explorer open in the Activity sub report, right click on the very top item in the list labeled “Database Fields”, select the value “Database Expert” from the menu that appears.  When the Database Expert opens, open “Current Connection” in the “Available Data Source” tree control, then open “sysdba” from the tree control, and double click on the Opportunity and Ticket tables, those two table should now appear in the list on the right. 


Along the top of the Database Expert view you see a “Links” tab, click on this tab and answer to the negative if something prompts you for automatic linking.  Find the Activity table in the view, locate the OpportunityID in the list of fields for that table, find the Opportunity table and then find the OpportunityID in it’s list of fields, click on  one of the OpportunityID and drag across to the other OpportunityID and a line should appear connecting the tables.  Right click on the new link so it turns blue and select “Link Options” from the menu that appears. When the “Link Options” view opens, look under “Join Type” and select the “Left Outer Join” option. When finished select the “OK” button. 


Follow the same procedure for the Ticket table using the TicketID field linking it to the Activity table TicketID field.


Close the Database Expert and you are back into the Activities sub report.  I want to locate the Opportunity and Ticket areas just above the Activity Notes so I am going to right click in Group Header 2b and select “Insert Section Below” from the menu item that appears.  I will do this twice, once for the Opportunity and once for the Ticket areas of this sub report.  My plan will have the Opportunity section in Group Header 2c and Tickets in Group Header 2d.  I will set both of these sections to suppress if a record is not available. 


The formula will look similar to the one written above for Address2 field.  For Opportunity, I selected OpportunityID, and for Ticket, I used TicketID.  You can drag and drop whatever fields you want for either section from the respective tables and then take the time align the fields with the rest of the main report.  This is what the section looks like after modifications.




I worked up the History area to basically match the Activities but I did not include the associated Opportunities and Tickets.  One major difference between the original History sub report and the modified version is I added to the record selection formula to limit History records where the Type was not equal to “Database Change”. This also could easily become a parameter.


Now looking at the Opportunity sections before modifications:



With the Opportunities sub report I simply cleaned up the white space between the fields making sure they all were visible and aligned the sub report data up with the main report.  I also relocated the the sub report under the Contact area so Activities and History can be located together on the main report.  FYI, Crystal makes relocating sections easy by allowing you to drag and drop the section to the proper position on your report while in Design mode.  I noticed that that all the areas of the main report were starting to blend in together so on each sub report so I changed the shading of the section label, this brakes the many areas of this report up so you can easily read the report. 


So here is the completed report:




 Eliminating the white spaces and tightening things up in the manner described above removed over 80 pages of report using the demo database. 


Download the updated report here 


Get all of our Free SalesLogix Reports here…


 


 


 

1 Comment

  1. I just downloaded and started playing with this. As David said, “Awesome post”. One small thing I just found I thought I would mention to others is the formula @PhoneNumber references the formula @Telephone_Country. That formula should include Canada because they format the phone numbers the same way the US does.

    Reply

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!