Multiple String Parameter displayed with both ID and Description

W D
W D used Ask the Experts™
on
Hi there,

I'm using the formula  join({?Providers},',')  to display a multiple value parameter list in my report. I like this formula; it's quite slick. This formula lists the ID's only from the multiple value parameter list. How do I list the Description/text value that is associated with these ID's?

Best regards,
WDelaney
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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"?
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I don't believe you can unless you include the description field in the report and build the list yourself.

mlmcc
W DData Analyst

Author

Commented:
I have Crystal Reports 2008 SP3 but I can upgrade to SP5. I'll try that first..
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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.
W DData Analyst

Author

Commented:
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.
W DData Analyst

Author

Commented:
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.
W DData Analyst

Author

Commented:
So, I really have SP2 not SP3. I'll have to do what mlmcc suggests.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

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

Author

Commented:
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!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I agree you should upgrade.

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

mlmcc
W DData Analyst

Author

Commented:
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).
W DData Analyst

Author

Commented:
I hunted around the EE site and I came across this inquiry:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27959759.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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
@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
W DData Analyst

Author

Commented:
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?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
W DData Analyst

Author

Commented:
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...
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

What database?

mlmcc
W DData Analyst

Author

Commented:
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, '')

Open in new window

W DData Analyst

Author

Commented:
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
W DData Analyst

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial