Crystal Reporting in SalesLogix Using SQL Expressions

I am going to take a break on improving the Crystal Reports that come with a standard implementation of Sage SalesLogix and discuss something I hope may be helpful in your efforts. 

Currently I am working on a report for a client that is proving to be quite challenging.  The scenario is a By User report that provides Opportunity level data within a date range.  Because of the clients data structure and current business rules I had to use the base report to display the system User information and collect any parameters,  Then I used a sub report to display the opportunity data.  The client wants several listing of one to many data from the Opportunity (such Activity and History records) displayed along with some roll ups of data from the Opportunity Product table.  Because Crystal does not allow sup reports inside of a sub report, I had to go outside my safe zone and use some other Crystal functionality.  For the roll ups of Opportunity Product info I used a SQL Expression field to display the requested data. 

Obviously, I did not know where to start so I plugged in the words “SQL Expression Fields Crystal” into Internet Explorer and there were plenty of sites to lead me to the answers I needed.  This is the link to the one I used the most

I created the SQL Expression field by right clicking on the words “SQL Expression Fields” in Crystals Field Explorer.  I named my first field TotProductQty and the SQL Expression Editor opened up. 

Here is the formula I used to come up with a total number of products for the Opportunity.

SELECT Sum(“Opportunity_Product”.”QUANTITY”)
FROM “Opportunity_Product”
WHERE “Opportunity_Product”.”Opportunityid” = “Opportunity”.”Opportunityid”

Lets break this a part line be line:

First Notice the Left and Right parenthesis at the top and bottom of the expression. From my reading on the subject I found out the SQL Expression must be surrounded by parenthesis so Crystal knows this is a sub query to the main SQL Query pulling data for the report.  

SELECT Sum(“Opportunity_Product”.”QUANTITY”) 

A SQL Expression Field can only return one value, here I want the Sum of the OpportunityProduct.Quantity.  Notice the double quotation around the words “Opportunity_Product”, this is because when Crystal created the SQL Query it aliased the OpportunityProduct table to Opportunity_Product.  The reading told me if the table is aliased then we need to put double quotes on either side of the table. The field is always double quoted.

FROM “Opportunity_Product”

The FROM statement looks a little different here also, we are just using the alias table name. There is no definition of the join between tables and notice we did not even use the Opportunity table at all in the FROM clause. 

WHERE “Opportunity_Product”.”Opportunityid” = “Opportunity”.”Opportunityid”

The WHERE clause is where the join to the main SQL Query and the Opportunity table is made.  Because the Crystal aliased Opportunity table to Opportunity we again needed to use the double quotation marks.

This next SQL Expression shows how to use more then one table and how to limit your query to certain records. 

SELECT Sum(“Opportunity_Product”.”Quantity”)

FROM “Product”,Opportunity_Product”                                       
(Two Table are listed)

“Opportunity_Product”.”ProductID” = “Product”.”Productid”    (Notice how the additional table is joined in)
“Opportunity_Product”.”Opportunityid” = “Opportunity”.”Opportunityid”
“Product”.”Family” in (‘YourValue’)
                                           (Here is the record limiting criteria)

If you add the expression and something is wrong with it, Crystal will tell you right away.  If the expression is good to go it will save and close.  Because the query will come up empty if no data is returned, I referenced the SQL Expression field in a formula so I could define what to display.  The expression looked something like this:

If IsNull(TotProductQty) then 0 else TotProductQty

It was fun learning something new and it really resolved one of my challenges with this report.  I hoped this post helps someone else. 

Next, I hope to show you how I was able to over come the requirement for multiple lists of one to many types of data with out using the ever so reliable sub report. Unitl the, Keep Smiling [:)]

1 Comment

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) news and product updates!

You have Successfully Subscribed!