Skip to Content
avatar image
Former Member

how to pass a parameter to the report?

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 .

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • May 29, 2017 at 03:07 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • May 29, 2017 at 03:38 PM

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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • May 29, 2017 at 03:50 PM

    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')

    Add comment
    10|10000 characters needed characters exceeded

  • May 29, 2017 at 04:01 PM

    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')

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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 ?

  • May 29, 2017 at 04:28 PM

    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')

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 29, 2017 at 04:40 PM

    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 ?

    Add comment
    10|10000 characters needed characters exceeded