Using parameters in an SQL Expression
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=991
Printed Date: 01 May 2024 at 2:37pm
Topic: Using parameters in an SQL Expression
Posted By: GreyDog
Subject: Using parameters in an SQL Expression
Date Posted: 11 Jul 2007 at 7:35am
|
Hi,
I noticed in one of the recent posts Brian said
You can use parameters to pass different values to the WHERE clause, but not actually change the conditions in it.
http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=983 - http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=983
Does anyone have any examples of how to do this?
I've got an SQL Expression field which works fine when I hard code a value, but if i try and use a parameter i get an error that says
Error in compiling SQL Expression:
Failed to retrieve data from the database.
Details: ADO Error Code : 0x80040e10
Source: Microsoft OLE DB Provider for SQL Server
Description: No value given for one or more required parameters..
The code i'm using in the SQL Expression is
(Select "CONTRACT_REF_SEG_DESC"."CS_DESCRIPTION"
FROM "CONTRACT_REF_SEG_DESC" WHERE LEFT("INVOICE_LINES"."IL_CUSTOMER_REF",(CHARINDEX('/',"INVOICE_LINES"."IL_CUSTOMER_REF",0)-1)) = "CONTRACT_REF_SEG_DESC"."CS_CODE"
AND "CONTRACT_REF_SEG_DESC"."CS_ACCOUNT"={?AccountCode} ) Like I say if i replace the {?AccountCode} with an account name it works.
Is it possible to do this? If so any ideas where i'm going wrong?
I'm using MS SQL and CR XI.
Thanks.
|
Replies:
Posted By: hilfy
Date Posted: 11 Jul 2007 at 12:16pm
|
SQL Expressions are for small bits that can be put into an existing Select statement. For example, I have a stored function that formats user names based on a key field in the database so I can put this in a SQL Expression: FormatUserLNameFirst({EMPLOYEE.EMPLOYEE_KEY})
For a full select statement like you have, you need to make it a "Command Text". To do this, go to your connection in the Database Expert and see if the option to "Add Command" is available (Crystal doesn't allow this for all database types...). If it is, put your SQL in there and use the expert to create the parameter.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: GreyDog
Date Posted: 12 Jul 2007 at 8:36am
thanks
|
Posted By: kwsanders
Date Posted: 25 Jul 2007 at 7:07pm
I read this thread today and went off and created a couple of commands in my database... all the time not thinking about the result that would give me when I went to test the report against a different database of the same schema. It doesn't have the commands.  Oh well... back to the drawing board. |
Posted By: BrianBischof
Date Posted: 25 Jul 2007 at 9:23pm
|
If you create the Command objects inside your report, then they don't need to be on the database. They are simply SQL statements that CR sends to the database. It will works with any compatible database that has the same table structures.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: kwsanders
Date Posted: 26 Jul 2007 at 7:08am
|
When you say to create the commands in my report, are you referring to using SQL Expressions? If so, then I guess I am doing something wrong that I need to try and figure out. I am using Crystal 10 Advanced Full (10.0.0.533). I need to create a report that is based on a single table. The report has three columns. Information for the current month, the previous month, and a summarization of year-to-date. The first two columns are easy. The third column is where I am getting tripped up and it is probably to do with my record selection formula. I am not getting proper numbers for the roll-up on the year-to-date values. One thing that I forgot to mention is that I need to determine a value based on a particular parameter. I didn't think you can use parameters in SQL Expressions. |
Posted By: GreyDog
Date Posted: 26 Jul 2007 at 7:34am
|
Hi,
SQL commands are set up in the Database Expert.
Click on the plus next to the Database name in your connection, it's the first option under it, if it's available for your database.
If you drag 'Add Command' to the 'Selected Tables' pane on the right it opens a window for your SQL. Just make sure the SQL is enclosed in brackets or else it will complain.
SQL Expressions are different. I think they are for minor SQL things, like SQL functions. Not entirely sure as i'm new to adding SQL into a report myself. If you add a 'command' i think the SQL Expression option is removed.
As for the last column in your report, how is the table set out?
I've had to do a similar thing in reports, I had a separate field for each month and used a Crystal formula field to add them up after doing a 'select case' on the current month.
Sorry if that's made it more confusing, I normally ask questions rather than answer them as you can see why!
 |
Posted By: kwsanders
Date Posted: 26 Jul 2007 at 7:36pm
|
I have to retype this based on memory... just lost everything I had typed. Anyway... I thought that SQL commands on the Database Expert screen are on the database and do not migrate with the report. Is that not right? At least it is not what I am seeing here. I have to be able to dynamically change the datasource on the report at runtime. As for my final column, it is a summarized view of the year-to-date data. I need to be able to pull in a list of records based on a range of numbers. From that dataset, I needed to calculate some summary totals across several values. The big problem that I am having trouble understanding with Crystal Reports is how the records get loaded and calculated when the report is executed. I had setup a formula to pick off a date value from a record if its ID field had the same value as the first value in my parameter range. When the report runs, however, I see an empty field. If I browse the data on that formula field, I see two values... a blank line and the second is the date that I expected. I had setup the formula as follows: dateTimeVar myDate; IF {MYTABLE.ID} = {?TEST_ID} THEN myDate := {MYTABLE.SOME_DATE}; Any help someone could give me would be great. I ordered a book from Buy.com recently for Crystal 10, but they sent me the book for Crystal 9. I got an RMA, but I am waiting for them to send the correct book. I need to go out and purchase Brian's book. I had seen it a while back, but I did not pick it up at that time. |
Posted By: BrianBischof
Date Posted: 26 Jul 2007 at 11:01pm
|
The data source connection is separate from the Command object. You can create a command object and change the data source dynamically. As long as the tables structures are the same in both databases, your report will run fine. Re your second question, I'm not sure how to answer because my brain is fried right now. Sorry. If you see a blank line when you browse the data, then that is why the record id is an empty field. You have to check your SQL to make sure it returns valid data (for the formula to work). ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: hilfy
Date Posted: 27 Jul 2007 at 9:01am
|
Add this line at the end of your formula:
MyDate
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: ve2yu
Date Posted: 03 Nov 2008 at 11:22am
|
Question: I have a parameter being brought into a crystal report XI from a .net code. The parameter is MyCategory.
I have created a command with the following
select * from inventory where category = '{?myCategory}'
this works fine but I would like to do the following so I can use the report for partial or full.
if '{?mycategory}' = "Complete List" then select * from inventory
else
select * from inventory where category = '{?myCategory}'
cannot seem to get it to work in the command what syntax do I have wrong?
------------- Clifford Sutton |
Posted By: hilfy
Date Posted: 04 Nov 2008 at 9:53am
|
You can't use an IF statement in SQL. I responded to you on this issue in another thread. The solution is:
Select * from inventory
where ({?mycategory} = 'Complete List')
or ({category} = {?mycategory}
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: nani2020nani
Date Posted: 13 May 2009 at 11:45pm
|
If you add a 'command' i think the SQL Expression field is vanished
y is it so??/
if v add a sql command...v cant go fr sql expression field na...waz d reason behind that??
plz help me out...repli as soon as possible frnds...tnx in advance ------------- avsr |
Posted By: hilfy
Date Posted: 14 May 2009 at 5:37am
|
You can't do a Select in a SQL Expression, so you have to use a command. Yes, it's one or the other - you can't have both SQL Expressions and Commands in the same 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 |
Posted By: nani2020nani
Date Posted: 14 May 2009 at 7:10pm
|
v can select using sql expression na select emp_id from employees where empid=__
this is an sql expression returning single field rite !!
and can u plz tell abt ...enforce from and enforce to options ....plz repli as soon as possible !! ------------- avsr |
Posted By: nani2020nani
Date Posted: 14 May 2009 at 8:20pm
|
if suppose i have 2 tables jobs and emp having 2 common fields whic are linked....fields:jobid and empid... table linking is like...from emp to jobs
then using enforce from..v can select only 'to table' fields rite ...
but im not getting the values....
using enforce to
(select empname from employees where emp.jobid=jobs.jobid )
using enforce from
(select jobdesc from jobs where emp.jobid=jobs.jobid)
is this two queries rite..??...
but for me...oly if i select fields from to table i.e; jobs table den oly its displaying values...or else nothing is being displayed
so plz help me out....
------------- avsr |
Posted By: 4past12
Date Posted: 11 Jun 2010 at 6:39am
|
You can easily get this to work.
The problem is CR parameters are a lot like the C++ preprocesor's token text replacement. It just takes whatever value is inside the parameter and places it in the expression without any regard to data type (anyway, that' the best way I can explain it).
So, if your SQL expression is expecting a string, you must quote the CR parameter. Your SQL query should look like the following:
(Select "CONTRACT_REF_SEG_DESC"."CS_DESCRIPTION"
FROM "CONTRACT_REF_SEG_DESC" WHERE LEFT("INVOICE_LINES"."IL_CUSTOMER_REF",(CHARINDEX('/',"INVOICE_LINES"."IL_CUSTOMER_REF",0)-1)) = "CONTRACT_REF_SEG_DESC"."CS_CODE"
AND "CONTRACT_REF_SEG_DESC"."CS_ACCOUNT"="{?AccountCode}" ) Notice that I put quotation marks around the {?AccountCode} parameter. This should now work.
------------- Regards, Craig S. |
Posted By: 4past12
Date Posted: 11 Jun 2010 at 6:45am
|
Try this:
IF '{?mycategory}' = "Complete List"
BEGIN
SELECT * FROM inventory
END
ELSE
BEGIN
SELECT * FROM inventory WHERE category = '{myCategroy}'
END
This will work as a CR Database Command object since it is all SQL. Also, if you are allowing custom values for the {?mycategory} parameter, you may also want to do the following check on it before running the query:
IF '{?mycategory}' != ''
BEGIN
-- Do some stuff.
END
Note that I checked for the empty string. Parameters are always converted to a string type when replaced into the SQL command (at least, that's been my experience...but I could be wrong on that). So you should not use this code, as it will never be true:
IF {?mycategory} IS NULL
BEGIN
-- Do some stuff
END
HTH.
------------- Regards, Craig S. |
Posted By: RumeshChanchal
Date Posted: 15 Sep 2011 at 1:21am
|
Hi Author, You may check out the following links........ http://mindstick.com/Articles/125cbf8f-f1e5-44a7-b3ca-41450a3fbd18/?Dynamically%20pass%20parameter%20value%20in%20Crystal%20Report - http://mindstick.com/Articles/125cbf8f-f1e5-44a7-b3ca-41450a3fbd18/?Dynamically%20pass%20parameter%20value%20in%20Crystal%20Report http://mindstick.com/Articles/92797a6e-467f-4876-8212-dd8a6c2e3dcc/?Parameter%20Field%20in%20Crystal%20Report - http://mindstick.com/Articles/92797a6e-467f-4876-8212-dd8a6c2e3dcc/?Parameter%20Field%20in%20Crystal%20Report  Thanks !!! ------------- rumeshchanchal |