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
   Can I Bind an Attachment to a Specific Activity in Infor CRM version 7.54?
Recently I had a user ask if they could bind an attachment to a specific Activity within Infor CRM??
Posted on Jul 07, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (formerly SalesLogix) Administrator Locked Out
I thought I had seen all the issues affecting the datalink but this was a new one. We could not login t
Posted on Jun 18, 2015 by Mark Duthoy to SalesLogix Support
   How do I remove users from my Infor CRM system (formerly SalesLogix)?
I had an Administrator ask me for the steps they needed to take in order to remove a user from the Infor
Posted on May 08, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (Formerly Saleslogix) v8.1 update 03 and 05- Uncaught Error Scheduling Contact Processes
 In the Infor CRM (formerly Saleslogix) web client, under the Contact area you have the ability to s
Posted on May 07, 2015 by Kris Halsrud to Kris Halsrud's Blog
   Problem Showing All Area, Category, and Issue Values in Infor CRM (Saleslogix) Mobile And How to Fix
There is an issue with the Infor CRM (Saleslogix) Mobile client where, if you have a large list of Area,
Posted on Apr 24, 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