Updating the SalesLogix Forecast by Account Manager Crystal Report


Well I made it through two very challenging weeks of Annual Training and I am ready to restart our efforts on fixing and adjusting the standard Crystal Reports that come with an implementation of Sage SalesLogix .   I wrote a blog post on January 6th of this year.  Here is the link if you want to revisit.  This is what I wrote on the “Forecast by Account Manager” report.

Start Excerpt ……………………….

Next is the Forecast by Account Manager, this report uses the condition filter fields of AccountManagerID and Estimated Close.  When you run the report you are prompted to answer two report parameters.  

There are a couple of issues with the parameters, the first parameter asks you if you want to see the statistics for each Account, it should actually read for each User.  You will see in the screen shot that the data is User based, not Account.  Next the second parameter asks if you want to display the graph, it displays the graph with either the “Yes” or “No” selection.  This issue exists because the suppress formula is for the report footer B which contains nothing for the report. The graph is actually located in Group Header 1 and that is where the suppress formula should written to.

There are two other items that could be adjusted to make the report a little tighter.  The Est. Close Date field could be formatted to display just the date.  Also, where the Account is displayed in header of group 2, we could set that section of the report to underlay the detail section which would bring the Account name in line with the Opportunity data for that line.  Here is the screen shot:

End Excerpt …………………………..

These are all pretty minor issues.  Because of comments I have received during recent phone calls I realize lately I have been negligent on detail so I will try a little harder to make sure these posts meet their intent of instructing.  I will start this blog by reiterating some of things I have written in previous posts.

In order to make adjustments to Crystal Reports in SalesLogix (SLX) you need to have access to a machine with the SLX Architect and Buisness Objects 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 Forecast by Account Manager report under the Opportunity family and select the Check Out icon using the tool bar icon on the top of the view.  Then select the Launch Crystal Report System icon on the tool bar located all the way to the right.

The next thing I do is select the key combination of CTRL R which should run the report.  This automatically establishes the connection to the database so I do not have to create one and then allows me to immediately see the effects of my change after I make it.  Now if you look at the Crystal report interface you will see two tabs right under the report name, “Design” is where you make modifications and “Preview” runs the report with data.  You can make modification in the Preview tab but when the change is made the report automatically refreshes the data.  In some larger reports I have seen Crystal crash because I made to many changes to quickly.  Trust me this is an issue if you have not saved in a while.  So I have gotten into the habit of making changes under the design tab only and of course saving a lot.

To fix the first issue we simply need to adjust the “Prompt Text” property of the “View Statistics” parameter field which can be founded in Crystals Field Explorer.  If the Field Explorer is not visible then you should go to the Crystal menu bar and select “View” and then Field Explore from the list that appears.  This should open the Field Explorer on the right side of the Crystal Interface.  Look for the label Parameter and click on the plus sign, when tree opens right click on the appropriate label and select “Edit” from the menu that appears.  To adjust this parameter I added the word “Manager” to the end of the statement and capitalized the word “Account”. 

The next issue is caused by the parameter being applied to Report Footer b which is an inactive section of the report. To fix this I am going to right click on the gray area just to the left of the report and select “Section Expert” from the menu that appears.  Highlight the section labeled  “Report Footer b” from the list on the left.  On the right you will see the “Suppress” check box with a button labeled by X-2 with a pencil underneath in red font, this button opens the Formula Workshop for the selected section suppression formula. Click on this button, highlight and Cut the existing formula from this section then save and close the Formula Workshop.  Now select the “Group Header #1” from the list on the left and select the button across from the Suppress Check box and paste the formula into the Formula Workshop.  Now Save and Close the Formula Workshop and go ahead and test the report.  I noticed right away the Account Manager and Labels for the report now also disappear when I selected “No” to the parameter, so I added a new sections to the report by right clicking on the Group Header 1 and selecting “Insert New Section Below” from the list that appears.  I dragged the fields and labels into this new section leaving only the graph in the area that is suppressed by this parameter.

To adjust the Close Date to display just the date instead of the date and time simply right click on the field and select “Format Field” from the list that appears.  You can choose whichever style of date you wish too, I use the 03/01/1999 style the most.  Once your style is selected you can close the Format Editor and see the result.

I am going to rescind the last issue I mention in the blog but I am going to make some major adjustments. 

First of all I noticed in the database I am working with that the Days Open field is just returning a 0 for each record.  Thinking about this a little I decided to to write a new formula but I want to warn you this might need to be adjusted based on your Opportunity Status Values your company uses.  Once again we need to go to the Field Explorer, right click on the listing for “Formula Fields” and select “New” from the list that appears.  Label the formula “DaysOpen” and select OK, when the Formula Workshop opens you are ready to create your new formula.  Below is mine:

if {OPPORTUNITY.STATUS} = “Open” then DateDiff(“d”,{OPPORTUNITY.CREATEDATE},CurrentDate)

This one basically says if the Opportunity Status is “Open” then figure the number of days the Opportunity has been open by determining the date difference between the OPPORTUNITY.CREATEDATE and the CurrentDate.  If anything else then use the OPPORTUNITY.DATEOPENED and the OPPORTUNITY.ACTUALCLOSE of the Opportunity to determine the number of days the Opportunity was open.

With the formula created, I dragged it into the report replacing the original field and formated the number to display without the decimal point.  I also updated the summary fields in the Group Footer 1 and Report Footer to use this new field instead of the original.  

With some focus still on formulas I updated the F_Close_Date formula to read:


Since this formula field is already included in the report all I will need to do is align the field with the others as I make adjustments.  But I added a label with a star in it next to this date field. If the Opportunity Status is anything but “Open” the star label is suppressed.  This allows me to tell the user that the date they are seeing is the Estimated Close if the status = “Open”

I also created a new formula called Forecast to help replace the “T” value meant to indicate whether the Opportunity has been included on the forecast or not.  That formula is:

If {OPPORTUNITY.ADDTOFORECAST} = “T” then “Forecasted”
else “Not Forecasted”

Once created I replaced the field that displayed the “T” value with this new formula field.

I also scrolled through the list of Opportunity fields and added the Description field to the report.  From left to right I now added labels and aligned fields as necessary across the report.  With all of the mods completed I think the enhancements made this report a better one: Here is a screen shot without the graph. 

If I were going to implement this report into my system I would define and create the rules that define how far back to collect records for the report.  Currently the report displayes all Opportunities in the system.

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here…

Please – Keep Smiling.



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!