Help with parameters when using a sql command based report in Crystal Reports 2008.

[Former Member]

I have not used crystal reports since version 8.5 and now I am back at it.  I am using Crystal Reports 2008 and am not using it in SAP.  I have created a report or open orders using SQL and have 2 parameters that I am passing in for a date range that is working great. I need 2 more parameters, but not sure how to get them to work. I am wanting the user to be able to choose from the ordered_by column and they need to be able to choose more than one, but also with the option of leaving it blank for all or selecting 'ALL' to get all.  I have the same issue with the order_type, once again would like to list the order types to choose from and allow the user to select more than one or leave it blank for all or select 'ALL' to get all. I do not want a static list, I would prefer to show a list of only the values that are within the date range that was selected.  I have tried building dynamic lists pointing to the ordered_by column in the sql command, but all I get is a single blank line to manually type something in.  I tried building this same report using the tables instead of sql, but the report runs pretty slow and still having trouble with the dynamic lists.I have looked online for documentation, videos and not really finding anything like what I am dealing with.  I have looked at the document "How to work with Crystal Reports in SAP Business One", but once again I am not using it in SAP, so a lot of the items that looked like they would help are not possible. Any and all help would be greatly appreciated (especially visuals, screenshots, videos, etc.)

Thank you in advance for your time and help,

Sean

[abhilash_kumar]

Hi Sean,

1) Make sure you've created the Order_By prompt in the Main Command Object and used it in its where clause. Also, make sure that is has been set to 'Allow multiple values'.

The where clause would look like this:

where ({?Order_By_prompt} = 'ALL' OR table.order_by IN '{?Order_by}'

2) Next, create another Command Object that looks something like this:

Select table.order_by from table

UNION

Select "ALL" from table

3) DO NOT Join this command object with the other Command Object

4) Expand the Field Explorer > Right-click the order_by prompt > Edit > Under 'List of Values' choose 'Dynamic'. Under 'Values' choose the 'Order_by' column from the command object you created in step 2.

5) Repeat steps 2 through 4 for Order_Type.

You cannot unfortunately, filter these two prompts based on another 'Date' prompt. The workaround involves using Business View Manager and which comes with the SAP BO Server suite (I don't think you have an SAP BO environment anyway).

Hope this helps.

-Abhilash

[Former Member]

Abhilash,

I am following you instructions except for the first part. the where clause has 2 prompts ({?Order_By_prompt} and {?Order_by}), but you only mention creating 1 prompt .

What am I missing?

Thanks,

Sean

[abhilash_kumar]

I just mentioned one however, you'll need to add both prompts to the where clause with the same logic.

-Abhilash

[Former Member]

I am only talking about the order_by right now.  In your example the where clause is written like this:  where ({?Order_by_prompt} = 'ALL' OR table.order_by IN '{?Order_by}')

I am asking did you mean to have {?Order_by_prompt} and {?Order_by}?

Also, I think the first prompt needs single quotes around it like the second parameter.

Not trying to be difficult, just a little confused.

thanks,

Sean

[abhilash_kumar]

I'm sorry, what I typed really looks confusing!

Both {?Order_by_prompt} and {?Order_by} and meant to be the same prompt (or whatever you've named it).

And yes, you should try using single quotes around it too.

-Abhilash

[Former Member]

Abhilash,

So I went into my existing sql command and add the following to my existing where clause:

AND ('{?ordered_by}' = '*ALL' OR o.ordered_by IN '{?ordered_by}')

I added a prompt {?ordered_by} as a string and checked the box for multiple values.

But when I click OK to save and close the command window, I get the following error:

Database Connection Error: '42000:[Microsoft][SQL Server Native Client 10.0][SQL Serer]Incorrect syntax near the keyword 'ALL'. [Database Vendor Code: 156 ]'

[abhilash_kumar]

Try:

AND ({?ordered_by} = '*ALL' OR o.ordered_by IN '{?ordered_by}')

-Abhilash

[Former Member]

Now I get a different error message: Failed to retrieve data from database. Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '('. [Database Vendor Code: 102 ]

[Former Member]

That error is coming directly from the database client. Unmodified by CR. E.g.; check youd DB documentation or google 'Vendor Code: 102'

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[abhilash_kumar]

How about:

AND ({?ordered_by} = '*ALL' OR o.ordered_by IN ('{?ordered_by}'))

-Abhilash

[Former Member]

Same error message and I tied added single quotes back to the first parameter and it gives the same error message on that too. I am on SQL Server 2008 R2

[Former Member]

Looked at these numbers in SQL Server.  It does not make sense to me for the syntax we are using.

102- Incorrect syntax near '%.*ls'.

256 - The data type int is invalid for the substring function.  Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

[Former Member]

ok, so I have been doing some testing, trying different things and I just figured out something.  The error 256 mentioning the "Incorrect syntax near keyword 'ALL'". I could not see anything wrong with the syntax and I was wondering if I was passing 'ALL' into the parameter to test if that was the issue. So, I changed the value to the word Test and now I get "Incorrect syntax near keyword 'Test'". Is the format of the data inside the parameter incorrect and it is causing a syntax error?