Solved

Crystal Reports 9 passing parameters to SQL Expressions

Posted on 2008-10-29
11
8,869 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

18 Experts available now in Live!

Get 1:1 Help Now