Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

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 http://www.cogniza.com/blog/?p=26

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 Smile

What's This?
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.

Related Content
   How do you set the Base Directory when Deploying your Web Client on Infor CRM 8.1?
Question:How do I make sure my Base Directory is set correctly on Infor CRM ? Recently I had a custo
Posted on Mar 06, 2015 by Dale Richter to Infor CRM Questions & Answers
   How do I fix a corrupt Picklist in Infor CRM version 8.1?
I had a user ask how to fix a corrupt picklist. The picklist did not show any values when trying to e
Posted on Feb 06, 2015 by Dale Richter to Infor CRM Questions & Answers
   Creating a Searchable & Filterable ComboBox in Infor CRM (Saleslogix) Web Client
I've had a few posts lately covering the ComboBox control in the Infor CRM (Saleslogix) Web Client. T
Posted on Feb 05, 2015 by Ryan Farley to Ryan Farley's Blog
   Limiting the Height of the Infor CRM (Saleslogix) ComboBox Popup
In my last post, I discussed how the Infor CRM (Saleslogix) ComboBox is a Dijit Select form widget. This
Posted on Jan 29, 2015 by Ryan Farley to Ryan Farley's Blog
   How to Disable the ComboBox Control in the Infor CRM (Saleslogix) Web Client
There's a problem with the ComboBox control in the Infor CRM (Saleslogix) Web Client. It won't di
Posted on Jan 22, 2015 by Ryan Farley to Ryan Farley's Blog


Ahmed said:


June 10, 2009 2:39 AM

Yasa Kusuma said:

I found the sample query did not work on SLX 7.5 and Crystal XI. Instead, this works


SELECT Sum("Opportunity_Product"."QUANTITY")

FROM "Opportunity_Product"

WHERE "Opportunity_Product"."Opportunityid" = "Opportunity"."Opportunityid"


April 13, 2011 7:11 AM

Yasa Kusuma said:

Sorry, I meant to say...


SELECT Sum("Opportunity_Product"."QUANTITY")

FROM "Opportunity_Product"

WHERE "Opportunityid" = "Opportunity"."Opportunityid"


April 15, 2011 11:38 AM

Rajesh Singh said:

Check this links too its also having nice post with wonderful explanation on SqlExpression Field in Crystal Report.......



March 14, 2012 10:58 AM

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register