Solved

Mask on Parameter CR2008

Posted on 2016-11-07
28
32 Views
Last Modified: 2016-11-10
Is there a way to put a mask on the parameter to force the entry to be uppercase? BUT the string that will be entered is a combination of letters and numbers and no max length.
0
Comment
Question by:Nicole McDaniels
  • 10
  • 10
  • 7
28 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Not that I am aware of.

You can use UCase or UpperCase in the selection statement or when you want to use/display the parameter

mlmcc
1
 

Author Comment

by:Nicole McDaniels
Comment Utility
It takes too long to run. I was able to enter >a>a >a>a >a>a >a>a >a>a >a>a >a>a in the Edit Mask field and it works. The only problem is it displays it to the user which I wish I could turn off
mask.png
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I hadn't seen that option.  To the best of my knowledge it can't be turned off.

mlmcc
1
 
LVL 34

Expert Comment

by:James0628
Comment Utility
FWIW, I don't think that you need to repeat the ">" in the mask.  It applies to the characters that come after it (not just a single character).  So your ">a>a >a>a >a>a >a>a >a>a >a>a >a>a" mask could be written as ">aa aa aa aa aa aa aa".  The effect seems to be the same either way.

 However, I wouldn't expect that ">" to make any difference, as far as performance goes.  If you use UpperCase ({?parameter}) in the record selection formula, CR should convert the parameter value to uppercase before passing it to the server.  At least, I think that's the way it normally works (assuming that the report is reading tables directly, and that you're just using something simple like {field} = UpperCase ({?parameter}) in the record selection formula).

 I'm assuming that the field in the db contains upper and lower case characters and that you're converting that field to uppercase too.  If so, the performance hit is presumably from converting the db field to uppercase.  If that field is indexed, the conversion probably keeps the db from using the index.  If the db has a case-insensitive option (as mentioned in another of your questions), then that would probably be your best solution.  Other than that, I don't know if there's any easy way around the performance issue.

 James
1
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Agree with James.

The UCase if used strictly on the parameter should be done one time with no hit to the performance.

If you are also converting the fields from the database then there will be a hit.

As far as the mask, when added the user will be required to enter the data in uppercase.  Are all values the same length?
The user will be required to enter a string of length 14 for the mask given.  Any shorter string will require the user to reenter to add characters

mlmcc
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
When I had uppercase({field}=?lotnumber the report just ran and did a whole table scan.
I was actually able to enter just "tlc" and it accepted it and converted it to TLC. The most characters we'd ever have is between 8-10 but I added a few just in case. It says in the help the "a" is alphanumeric and not required so seemed to work.
The goal is to ensure the user enters an uppercase as our DB admin is adding the upper to the database view field. Initially the intent was to allow the report to query for upper and lower case but even with the uppercase({field}=?lotnumber it just ran and ran. So, we're forcing the field in the DB to be uppercase and forcing the user to only be able to enter uppercase
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Interesting.

If you use  >aaaaa then 5 characters are required and they are converted to uppercase
If you use >a>a>a>a>a  then you can enter any string of length up to 5.

My suggestion was to use this as the selection formula.  You put the upper case function on the field.

{YourField} = UCase({?Parameter})

mlmcc
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
I did try that :
 uppercase({BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER}) like {?Lot Number}

and the report just endlessly runs and never ends.

I also changed the mask to >aaaaaaa.... and it still had no requirement on the number of values. I thought the purpose of the "a" was that it's alphanumeric and not required? See photo..
mask2.png
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
And this tells me "the array must be subscripted" I need the "Like" to be able to use wildcards
{BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like uppercase({?Lot Number})
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
DId you set the parameter to allow multiple values?

mlmcc
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
Yes I need multiple parameters to be allowed
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
In that case the uppercase idea won't work.

mlmcc
1
 
LVL 34

Expert Comment

by:James0628
Comment Utility
We're talking about 2 different situations with the UpperCase function.

 You said that you used:

uppercase({BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER}) like {?Lot Number}

 That uses UpperCase on the db field, which probably means that the db can't use the index on that field (if there is one), and that slows down the query.

 mlmcc and I were talking about using UpperCase on the parameter, like:

{BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like UpperCase ({?Lot Number})

 That should not give you any performance hit, because CR would convert the parameter to upper case before it passed it to the db (and the field is used as is, so any index on that field can be used).  For a single value parameter, that would have the same basic effect as using the ">" mask.

 However, if the parameter allows multiple values, then you can't use UpperCase on it.  I don't think you mentioned the multiple values before.

 But there's another question here:

 Is the db field all uppercase, or does it include lowercase letters too?  The fact that you tried to use UpperCase on the db field implies that it includes lowercase letters.  If so, then having the parameter in only uppercase won't match those db values that include lowercase, unless you can set the db to ignore the case.  And if you can do that, then there's no need to convert the parameter to uppercase in the first place, because the db will match the letters either way.


 Also, you mentioned using wildcards, but if you use the "a" mask, the user won't be able to enter wildcards.  The "a" mask seems to be limited to letters and numbers.

 James
1
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Nicole McDaniels
Comment Utility
I changed it to >CCCCC thank you!!

So the issue is twofold. Initially, we wanted the user to be able to enter in upper and lower case and pull the associated lots regardless of case (I had a previous post on this for help but no luck).

So, if I enter LAV and tox, I want to see all variables (lav, LAv, LaV, TOX, tox, etc). I wasn't getting that. I would only get what I would literally type. So if I entered LAv, I'd only get LAv. (I have the checkmark for case insensitivity checked, also)
In the database, it's free entry so the user can enter all cases even though it SHOULD be uppercase only. So, the workaround was, to add upper to the field in the view thereby forcing all fields that will be queried to be UPPERCASE, AND force the Crystal parameter to only allow UPPER. That way, the view is only upper and the user can only enter upper. If that makes more sense. (We also can't set the DB to ignore case; it's a validated DB (we're pharma) so making the change to affect the whole DB won't fly)
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
Yeah, as long as converting the case in the view doesn't affect the performance, then that should take care of that part of it.  If the report was reading the tables directly, that wouldn't be an option.

 FWIW, if the parameter allows multiple values, but not ranges, then I think there is a way to get UpperCase to work.  That way you wouldn't have to use the edit mask for the parameter.

 Using your example, you would replace

{BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like {?Lot Number}

 with

(
if UBound ({?Lot Number}) > 0 then
  {BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like Split (UpperCase (Join ({?Lot Number}, ",")), ",")
else
  True
)


 The UBound check just makes sure that the parameter contains at least one value.  It may not be possible for the parameter to be empty.  I just included the check, just in case.  If the parameter is empty, the assumption is that you're not using that parameter, so the test produces a True result.  If you want the test to fail if the parameter is empty, change the True to False.

 The main part uses Join to combine the string values in the parameter into one string, separated by ","s, then converts that string to uppercase, and then splits that back into separate values.  I am assuming that the values that you enter for that parameter won't include a "," (eg. "L,v").  If they could include a ",", replace the ","s in the Join and Split functions with some character that will not be used.

 So, for example, if you entered "LaV" and "tox", those would be combined into "LaV,tox", converted to "LAV,TOX", and then split back into 2 values, "LAV" and "TOX".  CR should do all of that before sending the query to the server.

 If the parameter will never be empty, you can forget the if-else and just use

{BAX_MV_GEMS_LOT_DISPO_RPT.LOT_NUMBER} like Split (UpperCase (Join ({?Lot Number}, ",")), ",")


 James
1
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Good thought James.

Another way that works is to use a loop
Local numbervar index;
Local booleanvar found;
found := false;

For index := 1 to ubound({?prod}) do
    found := found OR ({Product.Product Name}) like UCase({?prod}[index]);

found

Open in new window


mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Would a test like that, with a loop, get passed to the server?

 James
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
Wow, thanks James! I must say now I feel a little silly. I was recreating an existing report but for our new DB and the old report had this formula and I had NO clue what it's doing. Is this similar to what yours is doing? I'm not THIS advanced!
Formula
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
James - That test probably wouldn't get passed to the database.  Your solution may since the creation using the split and join could be done before passing to the database.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Nicole-  That formula is concatenating the parameters into a single string separated by carriage returns (chr(13)) and displaying it.

The output of that formula if uppercased could be used to do the compares.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
mlmcc already covered part of this.  First of all, no, that formula is not doing what we described (note that it doesn't use the UpperCase/UCase function).  But, as mlmcc said, if you used UpperCase on the result from that formula, then it might be part of the same process.  But it's really doing a lot more than that.  What that formula does is:

 Puts the values from the parameter in an array variable named input.  FWIW, that seems pretty pointless, since you could just as easily reference the parameter values directly (eg. instead of using input [ i ], you could use {?Lot Number(s)} [ i ]).  But it's possible that the values in the input array are altered in some other formula (you can't alter the values in a parameter).

 Copies every value from the input array into a single string (the output variable), separated by carriage returns.  The output variable is used at the end of the formula, so if you put that formula on the report, you would get a list of the values in the parameter, one per line.  Note that the field format would need to have the "Can Grow" option checked, to allow for the variable number of lines (depending on how many values were entered for the parameter).

 Copies every value from the input array, with any leading and trailing spaces removed (the Trim function), into another array named LotArray.  This serves no purpose at all in this formula.  There may be another formula in the report that uses the LotArray variable.


 Also, just a reminder, but if you now feel that mlmcc or I helped you get your question(s) resolved, you might want to cancel/change your pending "close" request.

 James
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
Other options
0
 

Author Comment

by:Nicole McDaniels
Comment Utility
Thanks James, I think that worked! One other question, the formula that I shared above, maybe I'm over simplifying it but isn't it just a join with a carriage return? Similar to Join ({?Lot Number},", " ). Just seems like a lot for displaying a parameter.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Yes the join is just joining using a carriage return to put each value on its own line

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
As I explained in my last post, that's not all that that formula does.  It also copies the parameter values into 2 different array variables, and, in one case, it also removes any leading and trailing spaces from the values when it copies them.  Filling those arrays may be vital, or it may serve no purpose at all.  It depends on whether or not those array variables are used in some other formula in the report, and how they're used.

 FYI, you can also do a Join with a carriage return.  Just use Chr (13) in the Join, instead of ", ".
 
Join ({?Lot Number}, Chr (13))

 So, if those array variables aren't being used somewhere else, you could probably just replace that formula with a Join.

 James
1
 

Author Closing Comment

by:Nicole McDaniels
Comment Utility
Used formula instead of edit mask which would appear in the parameter prompts
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
For the record, mlmcc first suggested using UpperCase back in his first post.  It turned out that it wasn't quite that simple, since the parameter allows multiple values, but it's the same idea.

 IAC, I'm glad that we were finally able to get this sorted out.  :-)

 James
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

12 Experts available now in Live!

Get 1:1 Help Now