Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Expression Fields - how to use it

Status
Not open for further replies.

dadazs

Technical User
Oct 29, 2010
37
GB
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
 
It is a lot like a SQL query in which you can only return a single value.

Here is a good article on it.


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



 
(
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
 
Thanks lbass.

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

 
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
 
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
 
(
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
 
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 and the Crystal Clear newsletter archives at
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top