Skip to Content
0

how to pass a parameter to the report?

May 29, 2017 at 02:30 PM

49

avatar image

I have a problem in Crystal reports 11 , Because it has limitation to showing the information in dynamic reports and I have a huge list of customer , I want to write a query and ask the first letter of the customer name . If for example, it starts with A , just show the A customers . this is my query :

select distinct bill_to_code from TLORDER where left(BILL_TO_CODE ,1 ) = ? And CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

but when I copy this to crystal reports and create a parameter , I get an error

" Failed to retrieve data from the database"

I should mention that I have another sql query in this report .

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Vitaly Izmaylov
May 29, 2017 at 03:07 PM
0

I usually test the query without parameter. Check if the following query works:

select distinct bill_to_code from TLORDER where left(BILL_TO_CODE ,1 ) = "A" And CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

and if it does, then replace the "A" with parameter created in Command object.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

I did it but I got an error message :

Failed to retrieve data from the database

0
Vitaly Izmaylov
May 29, 2017 at 03:38 PM
0

First make sure the query works outside of Crystal Reports and narrow it down what part of the Where clause is failing.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

I ran it in Toad and it worked . There is no error . I entered "D" and it gave me all the customers which starts with D

0
Vitaly Izmaylov
May 29, 2017 at 03:50 PM
0

Try single quote in Crystal Reports:

select distinct bill_to_code from TLORDER where left(BILL_TO_CODE ,1 ) = 'D' And CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

Show 1 Share
10 |10000 characters needed characters left characters exceeded

I used single quote but same error

0
Vitaly Izmaylov
May 29, 2017 at 04:01 PM
0

does the following work in CR:

select distinct bill_to_code from TLORDER where CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

Show 1 Share
10 |10000 characters needed characters left characters exceeded

I solved the problem , I should added the schema before table name .Now this query works

select distinct bill_to_code from TLORDER where left(BILL_TO_CODE ,1 ) = 'D' And CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

But still I can't use ? instead of D

How can I pass parameter ?

0
Vitaly Izmaylov
May 29, 2017 at 04:28 PM
0

ok, there should be a parameter name for example: StartCharacter

then the query should look like:

select distinct bill_to_code from TLORDER where left(BILL_TO_CODE ,1 ) = '?StartCharacter' And CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

Share
10 |10000 characters needed characters left characters exceeded
Niloufar Elahi May 29, 2017 at 04:40 PM
0

I used this query in CR : ( in dynamic report)

select distinct bill_to_code from TMWIN.TLORDER where left(BILL_TO_CODE ,1 ) = 'D' And CURRENT_STATUS <> 'CANCL' AND LEFT(BILL_NUMBER , 1 ) NOT IN ('M','U','T','K')

but it shows just 7 customers which start with D , when I run this query in Toad it gives me 34 customer

do you know how can I solve the problem ?

Share
10 |10000 characters needed characters left characters exceeded