Solved

"A string is required here" error in Formula

Posted on 2016-09-22
7
71 Views
Last Modified: 2016-09-24
Hello,

I am getting the above error when I run this formula:

{vw_reporting_biannualaudit.Drug Schedule}={?@Drug_Schedule}

and  

if  isnull({?@Drug_Schedule}) or not numerictext({?@Drug_Schedule}) then
       0
else
       tonumber({?@Drug_Schedule})

The last part of the formula (tonumber({?@Drug_Schedule}) gives the error.

I have set the @Drug_Schedule parameter to number.

If I set the @Drug_Schedule  parameter to a string it will give me the error "A number is required here" at the top of the formula for the @Drug_Schedule so either way I get an error.

Thanks for any help,

Mike
0
Comment
Question by:Michael Graham
[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
  • 3
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 41811628
Where are you trying to use this formula?

Are there 2 formulas there or is the full formula

{vw_reporting_biannualaudit.Drug Schedule}={?@Drug_Schedule}

 and  

 if  isnull({?@Drug_Schedule}) or not numerictext({?@Drug_Schedule}) then 
        0
 else
        tonumber({?@Drug_Schedule})

Open in new window


If the parameter is a number then you can't convert it to a number.
If it is a number then it can't have non-numeric text in it.

What are you trying to do with the formula?  If what I put in the text box is the full formula then you will get another error because the AND wants a Boolean.

If there are 2 formulas then change the second one to

 if  isnull({?@Drug_Schedule}) then 
        0
 else
        {?@Drug_Schedule}

Open in new window


mlmcc
0
 

Author Comment

by:Michael Graham
ID: 41811652
I am still trying to achieve the Parameter Drug Schedule to return all records when nothing is entered.  This is very easy when I set the Parameter to optional.  I am only using 1 parameter for Drug Schedule now.

We are new to automation in Crystal Reports via Business Objects.

On the dashboard the Parameter will appear at the top and nothing needs to be entered which is what I want (again if I set the parameter to optional prompt).

However, when I run the report it will prompt me to enter a value like it would in the native crystal report.  I do not have to enter a value (I can leave it blank) but I do not want this prompt to show up.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41811700
So this is the selection formula?

I believe you will need to either have no parameter or write your own viewer.

Are you running the report in the dashboard or separately?

mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Michael Graham
ID: 41811703
Yes this is the selection formula.

I am running it in the dashboard - however I am only allowed to edit it in the report.

I am not sure how to run my own viewer
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 41811879
This selection formula will get you what you need


(
Not HasValue({?@Drug_Schedule})
)
OR
(
{vw_reporting_biannualaudit.Drug Schedule} = {?@Drug_Schedule}
)

You can't get around the parameter prompt

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 250 total points
ID: 41812268
mlmcc has probably given you your best solution.

 FYI, there were a few problems with the formula in your first post.

 Assuming that {vw_reporting_biannualaudit.Drug Schedule} is a numeric field, then you can't just compare it with a string value.  That's why you got that error on the first part when the parameter was a string.  You would need to convert the parameter to a number (or the field to a string, but then you'd have to worry about the formatting of the string, to make sure that the strings match).

 If you're going to use IsNull, you need to do that before you do anything else with that field (or parameter).  CR generally does not handle null values well when evaluating formulas.  If it encounters one outside of the IsNull function, it just stops evaluating the formula at that point and produces a null result.  So, you need to use IsNull to test for a null first, and then use the field (or parameter) for other things (eg. to compare with a field) after that.  Technically, since this is a record selection formula, I guess that might not be a problem if CR is just passing those tests to the server and not actually evaluating them locally, but there's no point in taking chances.  So, the IsNull test should be first.

 Likewise, you had ( not numerictext({?@Drug_Schedule}) ), but you did that after you compared the field with the parameter.  So, if the parameter was not numeric, you'd try to compare it with the field first, get an error, and never get to the NumericText test.  If the parameter might not be numeric, you should use NumericText to check that before you compare the parameter with the field.

 Also, your if-else that produces 0 or ToNumber ({?@Drug_Schedule}) doesn't make any sense, since it produces a number by itself after the And.  Now that I think about it, I think maybe what you were going for there was something like this:

{vw_reporting_biannualaudit.Drug Schedule} =
(
if  IsNull ({?@Drug_Schedule}) or not NumericText ({?@Drug_Schedule}) then
       0
else
       ToNumber ({?@Drug_Schedule})
)

Open in new window


 However, if you want a null or non-numeric parameter to include all records, then that won't work.

 If the parameter is null or not a numeric string, it compares the Drug Schedule field with 0.  Assuming that Drug Schedule is never 0, you will get no records.
 If the parameter is a numeric string, it converts the string parameter to a number and compares the field with that value.

 If you want a null or non-numeric string parameter to include all records, then I think you could use:

(IsNull ({?@Drug_Schedule}) or not NumericText ({?@Drug_Schedule}) or
{vw_reporting_biannualaudit.Drug Schedule} = ToNumber ({?@Drug_Schedule}) )

Open in new window



 FYI, when it comes to converting a string to a number, you might also want to look at the Val function.  Val is similar to ToNumber, but it just takes any leading numeric characters and converts them to a number, and ignores anything after that.  If the leading characters are not numeric, it doesn't give you an error.  You just get 0.  But if the field/parameter could be null, you'd still need to use IsNull to test for that first.

 James
0
 

Author Comment

by:Michael Graham
ID: 41814228
Thanks everyone I finally figured it out - I had to keep working on the formulas you gave me.  You have taught me a lot while I am just starting out in Crystal.  I appreciate all the help.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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