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

[DellSC · Accepted solution]

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

[Former Member · Accepted solution]

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

[DellSC · Accepted solution]

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

-Dell

[DellSC · Accepted solution]

Also, try changing your condition to this:

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

-Dell

[Former Member · Accepted solution]

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?

[DellSC · Accepted solution]

I assume that the second command is for the parameter, correct?

Does this second command have any parameters?  Are you using the fields from this command anywhere in the report other than in the parameter?

-Dell

[Former Member · Accepted solution]

Yes, the second command is to generate a list of choices for the parameter.

No, it has no parameters.

The only place it is used is in the dynamic parameter for the list of ordered_by names for the first command.

I am happy to send you any of this if you would like to take a look.

thanks,

Sean

[DellSC · Accepted solution]

Can you save the report with data and attach it here?  Turn on "Save data with report" and save it, rename the file to change the file extension to ".txt", go to the "Advanced Editor" here and attach the file.

-Dell

[Former Member · Accepted solution]

I have attached the file, but since this will not run it says the data was not complete.

vcipa_open_orders_test.txt.zip

[DellSC · Accepted solution]

Please post the exact version number for Crystal - you can get this from Help>>About.  Also, what version of SQL Server are you using?  What version of the client are you using to connect to SQL Server?

In addition, I would do a couple of changes to the SQL to make it run more efficiently by putting some of the conditions from the where clause into the joins.  I've highlighted the changes in bold.

Open_orders

DECLARE @jd_start_date VARCHAR(10),
        @jd_end_date   VARCHAR(10)

SELECT @jd_start_date = CONVERT(VARCHAR(10), {?begin_date}, 112),
       @jd_end_date = CONVERT(VARCHAR(10), {?end_date}, 112)

SELECT
  pr.practice_name,
  ISNULL(pm.description,o.orderedBy) AS Ordered_By,
  p.person_nbr,
  p.last_name,
  p.first_name,
  o.actClass,
  o.actStatus AS Order_Status,
  CASE LEN(ISNULL(o.orderedDate,''))
     WHEN 0 THEN ''
     ELSE SUBSTRING(o.orderedDate,5,2) + '/' + RIGHT(o.orderedDate,2) + '/' + LEFT(o.orderedDate,4)                                  
  END AS Ordered,
  o.actTextDisplay AS Order_Description,
  CAST(LEFT(ISNULL(o.actComments,''), 8000) AS VARCHAR(8000)) AS Order_Comments,
  CASE LEN(ISNULL(o.txt_auth_obtained_date,''))
     WHEN 0 THEN ''
     ELSE SUBSTRING(o.txt_auth_obtained_date,5,2) + '/' + RIGHT(o.txt_auth_obtained_date,2) + '/' + LEFT(o.txt_auth_obtained_date,4)                                  
  END AS Auth_Date,
  ISNULL(o.txt_auth_number,'') AS Auth_Num,
  CASE LEN(ISNULL(o.actEffectiveDate,''))
     WHEN 0 THEN ''
     ELSE SUBSTRING(o.actEffectiveDate,5,2) + '/' + RIGHT(o.actEffectiveDate,2) + '/' + LEFT(o.actEffectiveDate,4)                                  
  END AS Effective_Date,
  CASE LEN(ISNULL(o.actExpirationDate,''))
     WHEN 0 THEN ''
     ELSE SUBSTRING(o.actExpirationDate,5,2) + '/' + RIGHT(o.actExpirationDate,2) + '/' + LEFT(o.actExpirationDate,4)                                  
  END AS Expiration_Date
FROM order_ o
  JOIN person p
    ON o.person_id = p.person_id
      AND ISNULL(p.expired_ind,'N') = 'N'
  JOIN practice pr
     ON o.practice_id = pr.practice_id
  LEFT JOIN lab_nor ln
     ON o.order_module_order_num = ln.order_num
     AND ln.ngn_status != 'Cancelled'
     AND ln.completed_ind != 'Y'
  LEFT JOIN provider_mstr pm
     ON o.orderedByKey = CAST(pm.provider_id AS VARCHAR(36))
WHERE ISNULL(o.actMood,'ORD') != 'RMD' AND o.actClass != 'IMM'
  AND (o.actStatus NOT IN ('cancelled','completed','result received','deleted')
    OR ln.order_num IS NOT NULL)
  AND o.orderedDate >= @jd_start_date AND o.orderedDate <= @jd_end_date
  AND ( '*ALL' IN {?order_by_list} OR ISNULL(pm.description,o.orderedBy) IN {?order_by_list})
ORDER BY 1, 2, 3, 4, 5, 6

Ordered_by

SELECT '*ALL' AS ordered_by

UNION

SELECT DISTINCT ISNULL(pm.description,o.orderedBy) AS ordered_by

FROM order_ o

JOIN person p

  ON o.person_id = p.person_id

    and IsNull(p.expired_ind, 'N') = 'N'

LEFT JOIN lab_nor ln

  ON o.order_module_order_num = ln.order_num

    AND ln.ngn_status != 'Cancelled'

    AND ln.completed_ind != 'Y'

LEFT JOIN provider_mstr pm

  ON o.orderedByKey = CAST(pm.provider_id AS VARCHAR(36))

WHERE ISNULL(o.actMood,'ORD') != 'RMD' AND o.actClass != 'IMM'

  AND (o.actStatus NOT IN ('cancelled','completed','result received','deleted')

    OR ln.order_num IS NOT NULL)

  AND ISNULL(pm.description,o.orderedBy) != ''

ORDER BY ordered_by

I would run both updated queries in SSMS to make sure that they're working correctly (you'll have to replace the Crystal params in the Open_Orders query with values in order to do this).

Other than that, I'm not seeing any issues in the report itself.

-Dell

[Former Member · Accepted solution]

Crystal Reports 2008 - version 12.0.0.683 SQL Server 2008 R2 - version 10.50.4321.0 SQL Server Native Client 10.0 Thank you for the sql tips, good stuff

[Former Member · Accepted solution]

So, I completed the sql changes you recommended and now it is working without error!!!

Not sure why, but thank you so much.

Is there a reason why it keeps asking me for the password each time I refresh the data to test?

[DellSC · Accepted solution]

Awesome!  I'm glad it's working!

I'm not sure why it would be asking you for a password every time.  Are you publishing this to Crystal Reports Server or BusinessObjects?  Or are you just running it from Crystal?

-Dell

[Former Member · Accepted solution]

Right now I am running it in Crystal reports, but eventually I will be loading it thru a another application that is built to open crystal reports.

Now all I have to do is go back and add the other parameter, which I hope will not be as hard as this LOL. I am going to keep this discussion open a little longer just in case.

thanks again for your time and help with this.

Sean

[DellSC · Accepted solution]

Oh, and you're on an old version of 2008.  You should be able to go here to download newer service packs:  Downloads for SAP Crystal Reports and others

I think you'll need to upgrade to SP4 first and from there to SP7.  You shouldn't have to install any service packs other than those two.

-Dell

[Former Member · Accepted solution]

I am downloading those to SPs now.  I was thinking about something, which brings up a question. Can crystal handle a join from a command to a table?  Is this ok or not advised?

[DellSC · Accepted solution]

If you read my blog on commands, you'll see that it's possible but not advised unless you're dealing with very small data sets.  When you join a command to anything (table, another command, etc.) Crystal will pull all of the data into memory and do the join there, which can cause significant slowness in the report.

-Dell

[Former Member · Accepted solution]

ok, so not to cause me more trouble, but I have to ask.  Can I do a cascading dynamic parameter using the stand alone commands and pass values from the first to second and then to third to shorten the lists and make them more specific to the data that is being reported on?  if so, do you have a reference on how I can do that.  I really do not want to break what I got, but right now the user is having to look thru a list of all ordered_by providers when they really only need to choose from 10 or so.  It would break down to 3 parameters/lists:

1. practice_name

2. ordered_by

3. order_type

thanks again, this has all been very helpful

Sean

[Former Member · Accepted solution]

Ok, so I downloaded SP04 and SP07, but when I ran SP 4 I get the following message:

Crystal Reports 2008 SP4 Update can not install because the version of the product on the system is too low.

So, I downloaded SP02, and when I ran SP 2 I get the following message:

This patch only applies to Crystal Reports 2008 SP1. Setup will now exit.

So, I downloaded SP03, and when I ran SP 3 I get same message I got when I ran SP4.

What am I doing wrong and should I contact Crystal Reports?

Very sorry for all the questions, but you seem to have a lot of answers.

Thanks,

Sean

[Former Member · Accepted solution]

I guess I was not even on SP1, once I found it and downloaded it, everything started working

[Former Member · Accepted solution]

Ok, so I am now on SP7 and I have all of my parameters and the report is working.  YAY!

I do have an issue with parameters that I was curious how to solve.

I have the following commands: (first 3 are for parameter listing)

practices - list of practices for the user to select to filter using practice_id

ordered_by - list of provider names that have placed an order

order_types - list of types of orders

open_orders - the main report command

my problem is this:

I use the selections from practices, ordered_by and order types to filter the data down in the open_orders.

both the ordered_by and order_types, display all information for all practices.

I want to be able to filter the ordered_by and order_types down to only the items that pertain to the practices selected, but I do not see how to pass a parameter into a command unless you define it in the command and I do think you can have the same parameter name in multiple commands.

Any ideas?

thanks,

Sean

[DellSC · Accepted solution]

In order to cascade the prompts, you would have to create a single command that contains all of the data for the three prompts - that way it can filter down a single data set based on the prompt selections.

-Dell

[Former Member · Accepted solution]

agreed, but I guess I was trying to see if there was a way to get more than 1 parameter out of a cascading set.  So, if I have 3 columns in my command practice_name, ordered_by and order_type.

when the user chooses those values I would now have the practice_name parameter, ordered by parameter and order type parameter.  right now it looks like all I can do is get the last item only.

[DellSC · Accepted solution]

When you create the parameter, you'll put each field in the "Value", so it will be practice_name at the top, then ordered_by, the order_type.  You can then configure each of them separately to get the correct prompt text.  Create a parameter for each value by clicking under the "Parameters" column.

You should now be able to use all three of them.

-Dell

[Former Member · Accepted solution]

Sorry, you lost me at:

"You can then configure each of them separately to get the correct prompt text. Create a parameter for each separetly to get the correct prompt text."

I need to use these parameters in the main command for the report, so it has all three already in it and if I add this one as the forth, how do I use just it and populate the other parameters and not prompt the user for all 4 parameters?

Also, is there an example I can look at?

thanks,

Sean

[DellSC · Accepted solution]

Sorry, I didn't track that we were working with this for a command (I'm trying to do too many things at the same time today ).

It's been a while since I've done this and I don't have time right now to dig in and do some testing with it.  You might be better off posting this as a new topic as we've answered your initial question and this topic is getting quite long.  This will also bring it to the attention of someone who may have more time than I do right now to work with you on it.

-Dell

[Former Member]

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.

[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?