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)

WHERE
"Opportunity_Product"."ProductID" = "Product"."Productid"    (Notice how the additional table is joined in)
and
"Opportunity_Product"."Opportunityid" = "Opportunity"."Opportunityid"
and
"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
   Retrieving Items from a Picklist via Javascript
Saleslogix is very light on how to use client side processes to do things in the Saleslogix web client.&n
Posted on Aug 29, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix Xbar update coming soon
Scheduled to be released 8/29/2014 Features in Update •Installation improvements •Enterp
Posted on Aug 21, 2014 by Scott Weber to SalesLogix Product Blog
 
   How do you add a parameter for File Attachment Size and Timeout in Saleslogix 8.1?
Question: Is it possible to set a parameter for file attachment size and timeout in Salelogix? If
Posted on Aug 07, 2014 by Dale Richter to SalesLogix Questions & Answers
 
   Fixing Duplicate Saleslogix FieldIndex Values in the SECTABLEDEFS
I had to fix a client's SECTABLEDEFS table that contained duplicate FieldIndex values.  FieldInd
Posted on Aug 07, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix XBar Installation
When installing the new XBar application I found the instructions a little vague so I thought I would pas
Posted on Jul 18, 2014 by Mark Duthoy to SalesLogix Support
 
Comments

 

Ahmed said:

Thanks

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.......

msdn.microsoft.com/.../ms226240%28v=vs.90%29.aspx

mindstick.com/.../eb554c35-0236-49ca-a7ce-c2eb573e27e8

March 14, 2012 10:58 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 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