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?
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
0
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.
0
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>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 ?
0
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
0

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
0
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
0
vastoCommented:
Tim, It is true that I mentioned SQLExpression, but I cannot consider this answer as helpful.  mlmcc deserves the points.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.