Assign SQL field to variable in Crystal Reports

I need to assign a field to a Crystal Reports Variable varSRPAWLaborRate.  The following SQL SELECT statement returns the required correct data:

SELECT        LABRAT_13
FROM            SFC_Work_Center
WHERE        (WRKCTR_13 = 'SRPAW')

I'm using CR 8.5 and need to run calculations using the variable.

What am I missing?  TIA.

Tim
gibneytAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Is that a SQL Expression or the query for the report?

Where is the variable?
DO you have a formula that declares it?
Just assign the value in there

mlmcc
gibneytAuthor Commented:
That is a SQL statement from SSMS.  I tried creating a formula in Formula Editor with the SELECT statement and get an error.  I am missing some little nugget to get this to work.


Tim.
gibneytAuthor Commented:
In CR Formula Editor I have:

numberVar varSRPAWLaborRate:= SELECT {SFC_Work_Center.LABRAT_13}
FROM SFC_Work_Center
WHERE (WRKCTR_13 = 'SRPAW');

An error is thrown on check  and puts the cursor back to the middle line just before SFC_...

<error>
Crystal Reports
!The remaining text does not appear to be part of the formula.
OK
</error>
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

vastoCommented:
You cannot do this kind of assignments. Crystal report supports just one datasource. You can create a subreport and get the variable there or you can use a SQLexpression. Is table SFC_Work_Center used inside the main report ?
mlmccCommented:
SQL Expression is another option in the FIELD EXPLORER.

Your SQL is close and so long as it returns a single value you could use it.

It just needs ( ) around the whole thing and the ; removed unless that is SSMS syntax.

You formula would then be

numberVar varSRPAWLaborRate:=  {%ExpressionName};

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gibneytAuthor Commented:
mlmcc,

That is it!  Thanks so much.  Now to use the calculated number in the main report.

vasto, you were onto the SQL Expression so partial credit to you but mlmcc provided what would have taken me hours to figure out.  Thanks just the same.

Tim
gibneytAuthor Commented:
mlmcc,

That is it!  Thanks so much.  Now to use the calculated number in the main report.

vasto, you were onto the SQL Expression so partial credit to you but mlmcc provided what would have taken me hours to figure out.  Thanks just the same.

Tim
vastoCommented:
Tim, It is true that I mentioned SQLExpression, but I cannot consider this answer as helpful.  mlmcc deserves the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.