Question Re: Help with parameters when using a sql command based report in Crystal Reports 2008. in Technology Q&A

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

Former Member — Wed, 09 Sep 2015 14:27:43 GMT

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

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

DellSC — Wed, 09 Sep 2015 17:05:55 GMT

You can't build a dynamic parameter for a command based on the command itself. You won't get any data. For information about how to build dynamic parameters when using a command, see my blog post here:

You may not be able to pre-filter the order_type by the dates, but you should be able to get the "All" value in there using a separate command for the parameter values - I generally use "*All" to make sure that it's at the top of the list. The only way I can think of for making sure that the data is filtered by dates requires either Crystal Reports Server or BusinessObjects and parameters created through the Business View Manager.

-Dell

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

abhilash_kumar — Wed, 09 Sep 2015 17:07:06 GMT

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

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

Former Member — Wed, 09 Sep 2015 17:32:43 GMT

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

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

abhilash_kumar — Wed, 09 Sep 2015 17:57:36 GMT

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

-Abhilash

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

Former Member — Wed, 09 Sep 2015 18:21:43 GMT

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

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

abhilash_kumar — Wed, 09 Sep 2015 18:47:47 GMT

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

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

Former Member — Wed, 09 Sep 2015 18:50:06 GMT

Dell,

Ok, so I looked at your "Best Practices When Using Commands with Crystal Reports" (very helpful). when I looked at what you had and what Abhilash had, they appear to be the same basic idea. 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 ]'

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

Former Member — Wed, 09 Sep 2015 18:52:00 GMT

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

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

abhilash_kumar — Wed, 09 Sep 2015 18:55:44 GMT

Try:

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

-Abhilash

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

Former Member — Wed, 09 Sep 2015 19:03:46 GMT

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 ]

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

Former Member — Wed, 09 Sep 2015 19:14:28 GMT

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

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

abhilash_kumar — Wed, 09 Sep 2015 19:17:02 GMT

How about:

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

-Abhilash

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

Former Member — Wed, 09 Sep 2015 19:57:36 GMT

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

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

Former Member — Wed, 09 Sep 2015 20:33:11 GMT

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.

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

Former Member — Wed, 09 Sep 2015 21:46:48 GMT

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?

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

Former Member — Thu, 10 Sep 2015 05:51:30 GMT

I could never get this to work without errors, so I changed to using a sub report. As long as I only have 1 item selected it works. But, when I select more than one item I get the following error:

Failed to retrieve data from the database.

Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. [Database Vendor Code: 141 ]

No matter how I try to configure this report, I cannot figure out how to make this work.

I am desperate for some help.

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

DellSC — Thu, 10 Sep 2015 13:29:26 GMT

Don't put quotes around the parameter. That's only for single-value string parameters.

-Dell

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

DellSC — Thu, 10 Sep 2015 13:42:49 GMT

Also, try changing your condition to this:

AND ('*All' in {?Order_By} OR o.order_by in {?Order_By})

-Dell

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

Former Member — Thu, 10 Sep 2015 14:34:16 GMT

Ok, this is great, for the first time ever it is working when I manually enter the data into the parameter for the order by.

But, when I create the second command object and I refresh my data to test, I get the parameter popup and it looks great. I add my dates and then I select my ordered_by names and click ok. I get the following error: Connection is busy with results for another command.

Any ideas?