Adjusting the Sage SalesLogix Current Activities Crystal Report

Today we start fixing or adjusting the Activity Level reports that come with a standard Implementation of Sage SalesLogix. 


On November 25th 2008 in a post labeled SalesLogix Activity Level Reports, I wrote the following on the “Current Activities” Crystal report:


The Current Activities report is the only report out of the three that provides an insight into what your Users are scheduling in the system.  When running this report you will be prompted for the date range you want to report on, this filters the report based on ACTIVITY.ORIGINALDATE.  The condition filters for the User and Date Range are ACTIVITY.USERID and ACTIVITY.STARTDATE.  (Read up on the definition of condition filters in these posts SalesLogix Reports Area and SalesLogix Account Level Reports )


Here is a screen shot of the report.


My suggested improvements for this report:




  1. Set parameters for the Activity start date allowing the ability to change between ascending or descending.


  2. Group by the User.


  3. Provide the ability to include the notes for the Activity. 


  4. Add parameters to choose the Activity types you want to see. 


  5. You could also add roll-up counts for each activity type in this report.

I still agree with this assessment so lets get started.


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 Current Activities report under the Activities 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.  For this report I have removed the User_Activity table, all the other tables associated tables were providing data to the report.    Since I want to add User information to this report I will add in the UserInfo table relating it to the Activity table by the UserID field.  Make sure to set the Left Join.  


Lets look at the Ascedning and Descending item first.  We are going to add a parameter field to this report.  In the tree control under the Crystal Field Explorer, find the group of [?] Parameter Fields, right click and select “New..” from the list.  The Create New Parameter form will Open.  We will Name this parameter “Ascending/Descending”, the Type will be “String”, under the Value section add “Ascending” and “Descending” to the list,  under Value Options section I added the Prompt Text of “Sort By Start date in what order?”, then set the Prompt with Description only to “True”, Default to “Ascending”, and Allow custom values to “False”. 


Looking at the groups I found that Group1 is set to sort by Activity.StartDate.  I right clicked on Group Header #1 and selected “Change Group” from the menu and the Change Group Options form opened.  In the middle of the screen you will see a check box with the label “Use a Formula as Group Sort Order” with a button to the rightwith a X-2 on it.  Check this box to enable the button, select the button to open the Formula Workshop form.  Paste this formula into formula box.


If {?Ascending/Descending} = “Ascending” then crAscendingOrder
else if {?Ascending/Descending} = “Ascending” then crDescendingOrder


For number 2 on the list of suggested improvements we need to add another group.  Go to the Insert menu at the top of the Crystal interface and select “Group” from the list.  When the Insert Group form opens select “USERINFO.USERNAME” from the list and close the form.  Back in the report you will see Group #3 has been entered.  Click and hold on Group Header #3 and drag the header to the position of Group Header #1. 


For number 3 we need to a place to add the Notes field.  Right click on Group Header #3 and select the Insert Section Below menu item.  Next I am going to add a label with the value of “Notes:” to this new section along with the Notes field from the Activity table.  Set both fields fonts sizes to 8 and drag the Notes field into the label field.  Next we need to create a paramter field that will allow the user to display notes or not.  Go back in the Field Explorer, find the group of [?] Parameter Fields, right click and select New.. from the list.  The Create New Parameter form will Open.  We will Name this parameter “IncludeNotes”, 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 “Include Activity Notes?”, then set the Prompt with Description only to “True”, Default to “Yes”, and Allow custom values to “False”.  Now we need to add a formula to the Section Expert for Group Header #3b.  Right click on the Group Header #3b and select “Section Expert” from the list.  Find the supress check box and  look for the formula button directly across from the check box.  When the  Formula Workshop opens, paste this formula in:


 {?IncludeNotes} = “No” or (isnull({ACTIVITY.NOTES}) or {ACTIVITY.NOTES}=””)


The last thing we need to do is make sure the Notes field can grow by right clicking on the label field and select Format Text, on the Common tab you will find the “Can Grow” check box. 


The next item on the list provides the ability to choose the Activity types you want to see.  This gets a little complicated so hang on.  Let’s start with the parameter.  In the Field Explorer, find the group of [?] Parameter Fields again, right click and select “New..” from the list.  The Create New Parameter form will Open.  We will Name this parameter “Display Types”, the Type will be “String”, under the Value section add “Meeting”, “To-Do”, “Phone Call”, “Personal Activity”, “Literature Request” and “All” to the list,  under Value Options section I added the Prompt Text of “What Activities Types you want displayed?”, then set the Prompt with Description only to “True”, Default to “All”, Allow custom values to “False”, and Allow Multiple Values to “True”.  Now we need to create two sections, the first goes right under Group Header #3a and the last will go under now Group Header #3c.  Copy the field in from Group Header #3a into #3b and then copy the new Notes field in Group Header #3c into #3d.  Then we will want to do is create a formula field that will help us define which of these group 3 sections will display in the report whne the Types are chosen.  In the Field Explorers tree control you will find the list of Formula fields.  Right click and select “New..” from the list.  Label this formula “Show”.  Copy this formula into the Formula Workshop:


if {?Display Types} = “All” Then 1
else
2


 


Next we are going to add the following formulas as suppresion rules for each of the Group 3 sections:


#3a


{@Show} = 1


or


Not({PICKLIST.TEXT} in {?Display Types})


#3b 


{@Show} = 2


Update #3c with


{@Show} = 1


or


Not({PICKLIST.TEXT} in {?Display Types})


#3d


{@Show} = 2


My final improvement for this report focuses on providing the reader the totals by Activity Type.  Because there are a total of 5 types of Activities I will focus on Totaling up Meetings only hoping you will be able to figure out the rest based on this example.  First thing we want to do is create a formula field.  In the Field Explorers tree control you will find the list of Formula fields.  Right click and select “New..” from the list.  Label this formula “Meeting”.  Copy this formula into the Formula Workshop:


if {PICKLIST.TEXT} = “Meeting” then 1
else
0


Next we want to insert a summary on the footer of report and Group #1 using this formula field.  Use the Insert menu and select Summary from the list.  Find the Meeting formula field we just created in the Choose the field to summarize: drop down and then select the value “Sum” within the Calculate this summary: field.  Then choose the summary location, in this case create a summary in the Group#1 Footer and then the Report Footer.  Label these fields as neccassary.  You may also want to establish a parameter that hides these totals.


In my report I spent some time with alignment and trying to make the report a little more readable using shading.  Here is the modified report.



If you want to down load a copy of this report follow this link. 


Get all of our Free SalesLogix Reports here…


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!