Minor Adjustments to the SalesLogix Potential Sales Opportunities Crystal Report

As I reviewed this report in the blog post labeled SalesLogix Opportunity Reports (Part 2), I noted that there was really nothing to do and I almost skipped working this report. I decided to take another look using the 7.5.1 version of SalesLogix to see if the Sage had done anything to improve the report.  I found the report had not been updated but I noticed a couple of things I could do to slim this report down a bit.  This is what I wrote about todays report on January 13th of 2009. 

Start Excerpt…….

On to the Potential Sales Opportunities report.  The data displayed in this report is very basic Opportunity level stuff.    Nothing really major comes to mind as I ran the report.  I do notice that the date field in the detail is formatted correctly.

What is really cool, the report allows the user to choose one of three sub reports that provide different flavors of roll up.  This is done using the On Demand sub report option.  I liked the way this is done, very very clean.  Nice report!

End Excerpt………

So the first thing I noticed on my second review of this report is that we are seeing a lot of Closed Opportunities which really does not match what this report seems to be about.  The words “Potential Sales Opportunities” means to me that theseshould be open Opportunities that are still being worked.  So to improve this report I will add a record selection formula that will only allow open Opportunities to be included in this report. 

Also, the report is currently sorted by OPPORTUNITY.CLOSEPROBABILITY, then by OPPORTUNITY.ESTIMATEDCLOSE date, then finally by OPPORTUNITY.STAGE.  I thought it might be good to add one more group to this report that would sort by OPPORTUNITY.ACCOUNTMANAGER.  Maybe even add a parameter to the report that allows us to choose which way we want to sort this report, the way it is now or by Account Manager.

Lets get started. 

To add the record selection formula to this report I went to the Crystal Menu Report, then Selection Formulas, then Record.  When the Record Selection Formula Editor opens we are ready to add the formula to restrict the Opportunity records.  The field that contains the data we will use to filter is the OPPORTUNITY.STATUS field, this field is not currently being used in the report so we will not find it under the Report fields list.  To find this field, open Field Tree using the listing that uses the name of your reports database server followed by the connection type, it might look like TEST82 (OLE(ADO)).  Once the tree is open select the plus sign next to the word “Opportunity”, from the list that opens find the word “Status”, double click on this field and you will see the field appear in the formula window below.  Next add the equal symbol (=), now the word Open (“Open”)

Formula should look something like this:


This same formula needs to be applied to each of the three sub reports that are located in the report footer section.

Now lets add the parameter that allows the user to select the sort by for this report.  You should see the tree control for the Field Explorer on the right side of the Crystal Interface,   If you cannot see this then you can Navigate to the View Crystal Menu and select Field Explorer from the list that appears.  In the Field Explorer tree control you should find an entry for Parameter Fields, right click on this entry and select New from the list that appears, you are now ready to create this new parameter.  The Name for this parameter “SortBy”, the Type will be “String”, under the Value section add “Account Manager” and “Opportunity” 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 “Account Manager”, and Allow custom values to “False”.

Next we have to have reference to the Account Manager for this Opportunity.  This really became quite a bigger issue then I expected.  If you are a constant reader of this blog you will know that in some of my earlier post I attacked the over assignment of tables to a report.  Meaning there are an extraordinary amount of table that are:

   1.) Not used in the report

   2.) Not linked to the Key tables

These conditions exist from trying to use existing reports as an easy way of starting a new report.  While searching to ensure the OPPORTUNITY.ACCOUNTMANAGERID was properly referenced I discovered the condition described and set out to correct the issue. 

To remove the table right click on the word “Database Fields” in the Field Explorer tree control, and select Database Expert from the list appears.  I removed the following tables:


Once all of the unused tables were removed I changed the already attached USERINFO table name to ACCOUNT_ACCOUNTMANAGER and added another USERINFO table, relabeled the new table to OPPORTUNITY_ACCOUNTMANAGER, made a left outer join from the  OPPORTUNITY.ACCOUNTMANAGERID to OPPORTUNITY_ACCOUNTMANAGER.ID  This allows us to use the User name of the assign Opportunity Account Manager in our new group.

Now we need a formula field that changes grouping as decided upon the Sort By parameter.  Back in the Field Explorer tree control find the entry for “Formula Fields”, right click on the entry and select New from the list that appears, name the formula “SortBy”  When the Formula Editor opens add this formula:

if {?SortBy} = “Account Manager” then {OPPORTUNITY_ACCOUNTMANAGER.USERNAME}
else  “”

This formula basically states, if the parameter SortBy = Account Manager then the formula will return the Opportunity Account Manager User Name, if not then the formula returns nothing.

Now we are ready to create the group that will sort based up our parameter selection.  Find the Insert Crystal Menu and select Group from the list that appears.  When the Insert Group interface opens, select the SortBy formula field we just created in the first drop down.  I like the sort by of ascending so I will close this interface by selecting OK.  The group automatically sets up in the report as Group Header 4.  We need to move this to the Group Header 1 position. to do this I ensure I am on the Design Tab so I can look to the right side of the Crystal interface and see the different group headers.  Find Group Header 4 and left click and hold on this group.  Now slowly drag this group up toward the Group Header 1 position.

To finish we will want to eliminate some white space that is created when the Sort By of Opportunity is selected.  Right click on Group Header 1 and select Section Expert from the list that opens.  Find the word Suppress that displays on the right side of the newly open interface, across from the suppress check box you will see a button with an X-2, this button should be of blue font meaning that there is not formula created for the suppression of this group.  Select this button and copy this formula into the Format Formula interface that opens:

{?SortBy} <> “Account Manager”

When you close the Format Formula interface the font on the button will now be red. 

I also added a label to express to the reader how the report is sorted, I located this right under the report title.

Testing the report it seems to be reacting as expected based upon my selections.  Although minor I think these changes vastly improve this reports performance and usability.


Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here…

I hope all had a great long weekend, I know it was hard coming back to work. 

Take care and 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!