Solved

"A string is required here" error in Formula

Posted on 2016-09-22
7
50 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 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