SQL Expression Fields - how to use it

[dadazs]

Hello Tek-Tips

I have been using Crystal Reports for about a year now; however, I have never used SQL Expression Fields.

It just sounds so interesting. Please advice what & how they do.

Thanks

 

[Seth21]

It is a lot like a SQL query in which you can only return a single value.

Here is a good article on it.

http://www.tek-tips.com/faqs.cfm?fid=1377

The problem I had with it that really restricted its functionality for me was that I didn't know how to use the output of the SQL expression in a formula. It can probably be done though.

You can graph the results of a SQL expression though.

Also, if you use a SQL Command to filter your incoming database . . . you can not use a SQL expression. Although the work-around is creating a 'view' in your database that contains only the fields you want. You can then link to that 'view' as if it were a single table.

 

[lbass]

The FAQ is a bit outdated. I suggest doing an advance search on SQL expression here in Tek-Tips. There are many benefits to using them--optimizing passing to the SQL query, creating summaries that can be used like fields (without the limitations of conventional summaries), creating subqueries, etc. Each execution of a SQL expression can return only one value, but you can set up a SQL expression so that it contains faux groups, so that it can return one value per group.

-LB

 

[dadazs]

I want to use expression below in sql field and use it in each Sitename Heading:

select sum([Holiday Pay] )
from WagesSpent
group by Sitename

I found out it cannot be done becaue sql expression field can display single value. Is that correct. I am using Crystal XI.



In general, I am creating a report for payroll department.
I report on budget and actual spending. However, I need to add year to date budget/spending for each period.

Subreport is the only way I know to achieve my goal. Is it the the only way to go.

Many Thanks

 

[lbass]

(
select sum(Holiday Pay)
from WagesSpent A
where A.Sitename = WagesSpent.Sitename
)

What version of CR are you using, and what datasource? The punctuation used in the expression depends upon the datasource, and the setup depends upon version, too. The above omits punctuation and also assumes that Sitename is in the WagesSpent table--which may not be the case.

You must always place subqueries like the above inside parens in the SQL expression editor.

-LB

 

[dadazs]

Thanks lbass.

I am using Crystal Reports X1 and SQL Server that I connect to via ODBC.

 

[lbass]

Is the SiteName in the WagesSpent table? If it is in a different table, what is it, and on what field are the tables joined?

If you double click on a field in the field list in the SQL Expression editor, how does it appear?

-LB

 

[dadazs]

Select Statement:

SELECT "Fortnightly_Wages"."KEY3", "Fortnightly_Wages"."COMPANY", "Fortnightly_Wages"."UTOTALWBUD", "WagesSpent"."Wage Total", "WagesSpent"."payWeek", "Fortnightly_Wages"."CurrentDC", "WagesSpent"."Deep Clean", "Fortnightly_Wages"."HOliday Budget"

FROM "ACCESSGM"."dbo"."WagesSpent" "WagesSpent" INNER JOIN "ACCESSGM"."dbo"."Fortnightly_Wages" "Fortnightly_Wages"

ON "WagesSpent"."Site Code"="Fortnightly_Wages"."UTARGET_A"

WHERE "WagesSpent"."payWeek"=50
ORDER BY "Fortnightly_Wages"."KEY3"



Tables are linked on:
ON "WagesSpent"."Site Code"="Fortnightly_Wages"."UTARGET_A

 

[lbass]

(
Select("WageTotal")
From "WagesSpent" A
where A."Site Code" = "WagesSpent".Site Code" and
A."payWeek" = 50
)

I'm not sure what fields you are using for holiday pay. Also you seem to be using underscores and spaces inconsistently in field names in your sample, so if you should review the above to make sure it reflects the way your fields are identified in the database.

-LB

 

[crystalkiwibruce]

SQL Expressions are cool. I've used them a lot to make my reports really race along.

You can use a SQL expression in a formula with {%SQL Exp Name}. The % indicates a SQL expression (just like {?ParameterName} or {@formula} and {#RunningTotal})

Have a look at my blog at

http://www.crystalkiwi.com/blog.htm

and the Crystal Clear newsletter archives at

http://www.chelseatech.co.nz/pubs.htm