We help IT Professionals succeed at work.

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
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Author

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.

Author

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>
Top Expert 2011
Commented:
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 ?
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
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

Author

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

Author

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
Top Expert 2011

Commented:
Tim, It is true that I mentioned SQLExpression, but I cannot consider this answer as helpful.  mlmcc deserves the points.