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.
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.
(
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.
(
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.
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})
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.