WEIRD!!!! Crystal Reports coming back without data, works fine in Query Analizer

• William Hall Dillon-327038

  SSC Eights!

  Points: 990

  More actions

  June 1, 2006 at 1:23 pm

  #172727

  Hi All:

  I'm new to Crystal Reports, anyway here it goes:

  I wrote a new report using Crystal 8.5. It prompts for a few things:

  Client ID

  Vendor ID

  Begin Date

  End Date.

  When I run it, no data displays. However, If I tke the SQL that is build and run it directly on the SQL Server in Query Analizer, the data is displayed. The changes that I made was to the Client ID input to support an "All" function.

  If it helps, I'm going to cut and copy the Formula - See below

  {Daily_Transactions.TransactionDate} in {?Begin Date} to {?End Date} and

  (If "All" in {?Client ID} then

  {Daily_Transactions.ClientID} = "%%"

  else

  {Daily_Transactions.ClientID} = {?Client ID}) and

  {Daily_Transactions.VendorID} = {?Vendor ID}

  Thanks for ant help on this

  Bill

• S Hodkinson

  SSCertifiable

  Points: 7529

  More actions

  June 2, 2006 at 3:47 am

  #641373

  Have you created an 'ALL' value in {?ClientID}.  We use the following procedure in Crystal

  1.         Open Field Explorer.

   

  2.         Right click on Parameter Fields then select New.

   

  3.         Enter the prompt name and select Set Default Values.

  -           Type the 'reserved value' ALL (ie to enable you to select all records)   Then the > (button)

  -           Select the field name where your other Client IDs are held

   

  4.         Select OK, OK, Close (to create the parameter).

   

  5.         Select Report, Edit Selection Formula then Record.

   

  6.         In Record Selection Formula Editor type the following formula.

   

  If {?Parameter Name} <> “ALL” then {Field Name} = {?Parameter Name} else {Field Name} = {Field Name}

   

  This formula is saying that if you do not choose “ALL” then it will choose whatever you put in the prompt.  Otherwise it will choose everything.

   

  7.         Save and Close the editor.

   

  Let me know it it helps

  Madame Artois