Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Multivalue Parameters in Header

Posted on 2013-12-18
10
Medium Priority
?
347 Views
Last Modified: 2014-01-07
Good Day all,

Ok I have tried everything I know to make this work but have failed.  Question I am requested to show each parameter in a header of the report that has been created meaning if in the Parameter that is set to string and multivalue and there is a range the user is allowed to pick.

Ex. 90654 to 90658 and 90660 to 90660 the end result in the header shows 90654 to 90660.

I need crystal to break apart to show 90654,90655,90656,90657,90658,90660 in the header.

I tried the following:
NumberVar Counter;
StringVar CPTs;
For Counter := 1 to UBound ({?CPT})
Do
(
CPTs := CPTs &
CStr({?CPT} [Counter],0,"") & ","
);
"CPT #s: " & Left(CPTs, Length(CPTs) -2)

Open in new window


But that fails with error message stating a number,currency amount,boolean,date,time,date-time,or string is required here.

If "" In {?CPT} Then 
    "All CPTs"
Else
     Join({?CPT}, ", ")

Open in new window

a string array is required here.

If I do these same statements with state for instance, it shows in the header with the desired results.

Where am I going wrong with the numeric value. I have used Cstr and ToText to change to a string but still nothing is working.

Any help Thanks much!
0
Comment
Question by:Sqlspider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 39727226
CPT is apparently a Numeric parameter. JOIN only works on string arrays so your second method won't work.

What is highlighted in your first formula.

Can you show the parameter screen for the CPT parameter?

Are you trying to allow multiple values and multiple ranges?

mlmcc
0
 

Author Comment

by:Sqlspider
ID: 39727258
mlmcc yes you're correct the parameter is Numeric.

Send-to-Experts-Exchange.ppt

Yes I need it to allow multiple values and multiple ranges  but when the report displays I need this:

Ex. 90654 to 90658 and 90660  the end result in the header shows 90654 to 90660.

I need crystal to break apart to show 90654,90655,90656,90657,90658,90660 in the header regardless of range or multivalues added.

Thanks
mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39727307
I don't know how to handle multiple ranges without some trial and error.  I will see what I can get to work.

What version of Crystal?
I don't recognize those parameter screens.

mlmcc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Sqlspider
ID: 39727349
I am almost ashamed to say but it is Crystal 8.5. This group does not want to get off this version due to the CIO as much as I have tried to get him to change.  

mlmcc any help would be great I have tried all types of things and just can not get this to work.
0
 
LVL 18

Expert Comment

by:vasto
ID: 39727592
How they run the reports ?
0
 

Author Comment

by:Sqlspider
ID: 39727662
The reports are ran by an executable that I push out on the server for the users to run. So they enter there parameters and the reports pull back the information needed showing in either the header or in the report footer the parameters that they selected.

SqlSpider
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1500 total points
ID: 39728845
I haven't used CR 8.5, so I can only hope that the formula below will work.

 One of the formulas in your first post included an empty string as an "All" option, but if the parameter is numeric, I assume that the "All" option is 0 (zero), not an empty string.  I included that in the formula below.

 This works for your example, but it does not sort the values.  If the formula works, then a sort could be added (the values could be saved in an array and then sorted).

 James

Local NumberVar i;
Local NumberVar j;
Local StringVar param_values;

if 0 in {?CPT} then
  "CPT #s: All CPTs"
else
(
  for i := 1 to UBound ({?CPT}) do
    for j := Minimum ({?CPT}[ i ]) to Maximum ({?CPT}[ i ]) do
      param_values := param_values + "," + CStr (j, "#");
  "CPT #s: " + Mid (param_values, 2)
)

Open in new window

0
 

Author Comment

by:Sqlspider
ID: 39729127
James I was feeling good with this and had hoped that this was going to work but,

I get the following message after entering in the parameters.

The formula section appears for where I placed your coding with the following: A number range is required here.  

Once you click ok the blinking cursor blinks next to the following line.

if 0 in |{?CPT} then

I was for certain this would have worked. I am playing around with the code now to see what else I can try with what you have written. I feel like its close but I am missing something so obvious.  

SqlSpider
0
 

Author Comment

by:Sqlspider
ID: 39729521
James,

Perfect on the parameters showing thanks, but now the report will not filter on the parameters that I have chosen to have crystal pull back the expected results.  Here is what is in the Select Expert section formula:

({SQL Table}={?CPT} or {?CPT}='')  This is giving me all CPTs in that SQL Table and not just the ones that are now showing in the Parameters
0
 
LVL 35

Expert Comment

by:James0628
ID: 39731590
FWIW, the 0 IN {?CPT} test worked fine for me, but I had CPT as a number parameter.  Based on your last post, it looks like CPT is not a number parameter in your report.  That would probably explain the error that you got.

 As for your selection formula, obviously anything that you do in a formula that just displays the parameter values is not going to have any effect on the record selection, so the question is, what did you change while working on the display formula?  I assume that you changed something about the parameter.  Maybe changed it from number to string?

 You're probably getting the "all" option (empty string) when you don't really want it.

 Do you have an empty string included in the parameter values by default or something?  {?CPT} = "" will be true if there is an empty string anywhere in the parameter values.

 James
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

610 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