Solved

Multiple String Parameter displayed with both ID and Description

Posted on 2012-12-27
26
379 Views
Last Modified: 2013-01-22
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
0
Comment
Question by:wdelaney05
  • 12
  • 5
  • 5
  • +1
26 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 38724249
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"?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 38724250
I don't believe you can unless you include the description field in the report and build the list yourself.

mlmcc
0
 

Author Comment

by:wdelaney05
ID: 38724324
I have Crystal Reports 2008 SP3 but I can upgrade to SP5. I'll try that first..
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 38724332
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.
0
 

Author Comment

by:wdelaney05
ID: 38724711
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.
0
 

Author Comment

by:wdelaney05
ID: 38725033
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.
0
 

Author Comment

by:wdelaney05
ID: 38725211
So, I really have SP2 not SP3. I'll have to do what mlmcc suggests.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 38725276
Actually, I would recommend upgrading. You need to be at the same version and service pack level as your server environment.
0
 

Author Comment

by:wdelaney05
ID: 38725605
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!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38725720
I agree you should upgrade.

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

mlmcc
0
 

Author Comment

by:wdelaney05
ID: 38725974
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).
0
 

Author Comment

by:wdelaney05
ID: 38725990
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..
0
 
LVL 34

Expert Comment

by:James0628
ID: 38726299
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 100

Expert Comment

by:mlmcc
ID: 38726955
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 38727401
@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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 38727607
Interesting.  Thanks for the info.   But is there an option to display both the value and the description?

 James
0
 

Author Comment

by:wdelaney05
ID: 38728367
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?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38728636
You would have to create the field in the table or use a command as the source for the parameter

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 38728890
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
0
 

Author Comment

by:wdelaney05
ID: 38729478
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...
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38729638
Could a SQL Expression be used to loop through the parameters and retrieve the descriptions?

What database?

mlmcc
0
 

Author Comment

by:wdelaney05
ID: 38741838
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 38743129
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

0
 

Author Comment

by:wdelaney05
ID: 38793915
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!
0
 
LVL 34

Expert Comment

by:James0628
ID: 38796142
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
0
 

Author Comment

by:wdelaney05
ID: 38806449
thanks, James! I will accept mlmcc's solution for my issue.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now