Solved

Crystal Reports 9 passing parameters to SQL Expressions

Posted on 2008-10-29
11
8,918 Views
Last Modified: 2013-11-15
I have a sql expression that works but the filtering criteria is all "hardcoded".  Is there any way to pass parameters from the main report to the sql expression?  If so could someone show me an example.  I want to replace the hard coded items in the "where clause".
select sum(cp.assess_value)

from pa_stud_cpty_assessment cp, pa_student st

where cp.assess_dte BETWEEN to_Date('01/08/2008', 'DD/MM/YYYY') 

AND to_Date('28/10/2008','DD/MM/YYYY')

AND cp.stud_id = st.stud_id

AND st.dmn_id = '603 ACS'

AND cp.cpty_id = 'CRC.WD.SCD.AA'

--ORDER BY cp.stud_id

Group by st.dmn_id, cp.cpty_id

Open in new window

0
Comment
Question by:Mike Johnson
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22833297
I'm assuming you are referring to the Add Command option.
In the Add command dialog box you have the option to create the parameters you need. You don't create them as parameter fields in the normal way.
In the sql text panel you position the cursor wher you want to use the parameter and double click it in the parameter list (in the Add command dialog.)
0
 

Author Comment

by:Mike Johnson
ID: 22840062
no actually... i was referring to a "sql expression".  The above example is my sql expression with hardcoded filters, I was just wondering if I could reference paramerters, or fields from my main report (main query)?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22854262
You should be able to use a database field in place of the hardcoded values.  This would include parameters supplied by the user.

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 22855664
Parameters can't be passed to SQL Expressions.  SQL Expression fields are essentially SQL subqueries that are added to the SELECT clause generated by Crystal Reports.  They're evaluated in a pass prior to the Crystal Parameters.

It might be possible, however, to link a parameter in a main report to a SQL Expression field in sub report, but doing so would be next to worthless since a subreport could just do the work of the SQL Expression.

In general, if you need to pass a parameter into a SQL Expression, then you probably need to re-evaluate how you're pulling data and consider using a SQL Command, View or Stored Procedure as the basis for your report.

I've attached a presentation on SQL Expressions  I gave at Business Objects Insight a couple of years ago.
The-Power-and-Possibilities-of-S.pdf
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 22855680
That being said, while you can't pass parameters, you can pass actual report fields in.  In essence, you'd create a correlated subquery.  My presentation gives an example on Slide 30.  As you can see, it's a pretty simple query and I've linked a field from the query ("Customer ID") to a detail field in the report ("Customer"."Customer ID").  I'll I had to do was double-click a field in the field explorer within the editor to pull it into the query.

So, if you limit the records in your report based upon parameters, then link report fields to the query, you've implicitly passed the parameters into the SQL Expression.  An example, based on your code, would be.

Unfortunately, if your SQL Expression is an uncorrelated subquery (meaning it can stand alone as a query and isn't directly related to the data in your report) then this approach won't work.
0
 
LVL 1

Expert Comment

by:Emily0724
ID: 27612628
rhinok:  This is EXACTLY what I want to do (what you show on slide 30), but it is not working.  I keep getting a syntax error that my field is an "invalid identifier",  I have tried all different ways of referencing the field using brackets, quotes, parens, anything I could think of, but it doesn't like it.  Any ideas what I might be doing wrong?

Here is the expression....

(select a.mill_code
from mill_personnel a, personnel b
where a.personnel_id = b.personnel_id
and b.sign_on_name = 'JEJONES'
and a.mill_code = "INVOICE"."MILL_CODE")

invoice.mill_code is a field from the Oracle database and is also on the report.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 27624600
Hi Emily,

Unfortunately, you've run into one of the database-specific limitations of SQL Expression fields.  When you create a SQL Expression against an Oracle database, you can't correlate the expression by passing in a value from the main report like you can with SQL Server and Access.  I wish I had a better answer for you.  Depending on what you need the overall report to do, you could build a SQL Command as the basis for the report instead of tables and a SQL Expression field.  It'll take a little bit of work, but you'll be able to do what you need.

FYI - it's been a few years since I gave that presentation.  Here are some examples of database-specific issues I've run into:

SQL Server:
  • If using a whole SELECT statement, you must encapsulate it in parentheses
  • You can correlate data in the main report with the SQL Expression, but you have to have the right syntax for the correlated fields.  Adding them directly from the field tree is the easiest way to ensure you get the syntax right
Oracle:
  • Doesn't allow you to correlate data in the main report with the SQL Expression.  Major bummer - ticks me off every time I have a client who can benefit from it, but I can't use it...
DB2/UDB:
  • Of these three databases, by far the friendliest to work with.
  • Doesn't require parentheses - doesn't even require a complete SELECT statement - can simply type in SQL as if you were in the database query analyzer. As an example, you can simply type in a CASE statement or DB2-specific keywords on the fly.
  • Not picky about format of correlated fields.  Just have to have the table and field name (table.field), quotes or not.
~Kurt
0
 
LVL 1

Expert Comment

by:Emily0724
ID: 27633883
Yes, that is a Major Bummer!!  But thanks for the info....
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using If, else, then with SQL assistant or Crystal Reports on Joins 6 66
VB RDLC Report 1 94
Ignore parameter if no value entered 22 46
Crystal Reports 9 and Subreports 3 50
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now