Multiple String Parameter displayed with both ID and Description

If you're using Crystal Reports 2008 SP5 or above then you can simply set an option in a parameter to display either the value or description.  If you're using any version lower then you have to hard code all possible values into a pretty complex formula.  So, the question is "what version are you using"?

I have Crystal Reports 2008 SP3 but I can upgrade to SP5. I'll try that first..

FYI - if you're connected to a BusinessObjects Enterprise or Crystal Reports Server system, I wouldn't upgrade unless you verify your server environment is already at SP5 - it's important you keep all connected products at the same service pack level.

Thanks, rhinok - you're most helpful - I just happened to be talking to our server admin regarding our server environment and yup, he just confirmed that our server environment is at SP5.

as a side note, our server admin also stated "Just remember the SP5 is our Business Objects version – the version of Crystal you are using on your desktop is dependent on what you have downloaded and updated.  In Crystal go to Help/About crystal reports.   The first number (should be 12) is crystal 12 – ie Crystal 2008.  Then next number is the SP the and the third is the fix pack.  For example, my version 12.2.0.290 which means I am on Crystal 2008, SP 2 with no fix pack.  The version would need to be 12.5.x.yyyy to be a SP5 Crystal reports 2008 desktop application.

So, I really have SP2 not SP3. I'll have to do what mlmcc suggests.

Actually, I would recommend upgrading. You need to be at the same version and service pack level as your server environment.

yup, I agree rhinok, about the upgrade. It'll take me a few days to do that with my work pc. For now, though, I need your guy's assistance with an acceptable solution, if that's ok. If I didn't have SP5, I'd go with you and mlmcc's solution regarding building a manual list. If I do have SP5, then I assume that I can set the parameter option. Is it ok if I accept multiple solutions? Any thoughts you may have for this guidance would be much appreciated!

I agree you should upgrade.

Does the new option allow you to display both the value and the description in the report?

mlmcc

mlmcc, I don't think so. I checked out this hyperlink:
http://help.sap.com/businessobject/product_guides/boexir31SP5/en/xi31_sp5_whats_new_en.pdf
on page 15 of the document it states:
Display parameter descriptions instead of parameter values in the report:
In the "Edit Parameter" dialog box, under "Value Options", you can now
choose to use "Show Value" or "Show Description" in the "Show Value or
Description" option.
I actually do have this option to "Show Value" or "Show Description" in my Edit Parameter/Value Options/Show Value or Description. When I choose Show Description, the Values (the IDs) display in the Join formula not the Description (the names associated with the IDs).

I hunted around the EE site and I came across this inquiry:
https://www.experts-exchange.com/questions/27959759/Displaying-multiple-parameter-fields.html
Looks like if I do go the manual list route, this gives pretty clear directions on how to do it..

I haven't used CR 2008, but FWIW, the option that rhinok seemed to be describing sounds odd to me.  Unless CR 2008 has more than one way to reference a parameter, changing the parameter to "show" the description instead of the value would mean that when you used the parameter in a record selection formula, you'd get the description and not the value.  That doesn't make much sense.

 Even if it did work somehow, it sounds like you're still choosing one or the other, so you'd still need to do something else if you want to see both.

 FWIW, CR 10 has the same kind of display option, but all it does is change what the user sees when they're selecting from a list of default parameter values.  You can set it so they see the value and the description, or just the description.  I think the CR 2008 option may be the same kind of thing and only affects what the user sees when they're entering the parameter values.  But, like I said, I haven't used CR 2008, so I could be wrong.

 James

That question does have a pretty good example of what the manual formula would be.  As rhinok stated it is not pretty and is manually intensive since if values change or get added, you have to change the formula.

mlmcc

@james - the cr2008 sp5 option is different.  It only affects what's displayed on the page, not what happens in record selection criteria.  It's designed to address this specific issue and to reinforce using IDs as values, not descriptions.

Interesting.  Thanks for the info.   But is there an option to display both the value and the description?

 James

Good question, James0628! I'm curious, too, to know if there is an option to display both.
Hey, I just thought of something: what if I redesign my parameter value to be a concatenation of the id and the description?

You would have to create the field in the table or use a command as the source for the parameter

mlmcc

One question would be how to get those values in the parameter.  I don't know where your parameter values are coming from.  Are they just preset default values that you entered manually in the report, or is this a dynamic parameter?

 And then your parameter values won't match your data field.  Say the original parameter values were "1" for "Company Abc" and "2" for "Company Def".  Now the parameter values are something like "1 Company Abc" and "2 Company Def", which won't match the "1" and "2" in your data.

 You could extract the original value from the parameter in your record selection formula, and if the parameter only had 1 value, that might be OK.  For example, if the value was always 1 character long, you could use:

{field} = Left ({?param}, 1)

 I think there's a good chance that that test would be passed to the server.

 But since your parameter allows multiple values, you would probably have to use a loop - Basically, something like the test above, but in a loop that checks each parameter value - and I really don't know if that would be passed to the server or not.  My first instinct is that it would not be, which would mean that all records (within any other constraints you may have) would have to be sent to the report, and then the report would use that parameter to filter those records.  If you don't have that many records, that might be OK.  Otherwise, you may have to add the combined value and description to your data somehow, so that the parameter test can be passed to the server.

 James

Yeesh! sounds like a lot of work James. Our IT dept at work stated that I can have SP3 installed but nothing greater than that; I'd be the only one with SP5. I think I'll complete my manual list and then go with that...

Could a SQL Expression be used to loop through the parameters and retrieve the descriptions?

What database?

mlmcc

Hi guys,

Apologies for the delay in getting back to you. I finally had time to complete my manual formula for displaying the multiple parameter values with both the ID (Value) and Description. I'm still waiting on our IT dept to install CR SP5 for me; until they do, I'll use/maintain this manual formula - it's a great workaround for now. Attached is my code snippet for the formula.

Once I get Crystal Reports SP5 installed, I promise to come back to this question and let you know how it goes..
Formula-for-displaying-multiple-.txt

FWIW, when doing tests for a lot of different values, like the if-else in your formula, I like to use Select-Case.  I just find it easier to read and maintain.  But that's just a personal preference.  FWIW, you could rewrite your if-else as:

Select {?Providers}[i]
 Case "907" :
  "CLINIC, EC PROV [907]"
 Case "975" :
  "SCANNING, PROV [975]"
 Case "686946" :
  "DR B LFCH [686946]"
.
.
.
.
.
 Case "719465" :
  "RESIDENT, REFERRAL [719465]"
 Default :
  ToText({?Providers}[i], 0, '')

Select allOpen in new window

ok, guys, I'm being prompted to close my question. The cr2008 sp5 option can't be tested because our IT dept won't do the upgrade, at least for now. The manual solution works perfectly fine for me. Any thoughts on solution acceptance? Basically, I chose mlmcc's solution of doing it manually through a formula.
Oh, and thanks, James0628, for the Select Case example - it's nice and clean!

I'll take the easy way out and defer to mlmcc on this, since you're apparently using his solution to another question, which you found on your own.

 James

thanks, James! I will accept mlmcc's solution for my issue.