Print Page | Close Window

Parameter Field within SQL Expression Field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17189
Printed Date: 04 May 2024 at 11:40pm


Topic: Parameter Field within SQL Expression Field
Posted By: SonoItaliano
Subject: Parameter Field within SQL Expression Field
Date Posted: 02 Aug 2012 at 2:57am
In Crystal Reports XI, is there truly no way whatsoever to refer to an input Parameter Field (ex: {?myParameter}) within a SQL Expression Field?
 
Any way to workaround this limitation?
 


Replies:
Posted By: hilfy
Date Posted: 02 Aug 2012 at 8:58am
Correct, there is no way to do that as the expression is passed to the database "as is" without being parsed by Crystal.
 
It's not hard to get around this limitation if you have fairly good SQL skills.  I would do the following:
 
1.  Create a new report (this is MUCH easier than editing an existing rpt!)
2.  Outside of Crystal, create a query that will return ALL of the data for your report, including the expression where you want to use the parameter.
3.  In your report, instead of selecting tables, create a Command.
4.  Add the SQL created in step 2 to the command.
5.  For each parameter in the query, create a parameter in the Command Editor.  NOTE:  Commands will NOT work with params that are created in the report itself!
6.  For each parameter you want to use in the query, place your cursor where you want the param, then double-click on the parameter.  NOTE:  if any of your commands are strings, you will have to put quotes around the parameter in the query.  For example: '{?MyStringParam}'
7.  Save the command.
8.  Design your report.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window