Solved

"A string is required here" error in Formula

Posted on 2016-09-22
7
37 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Michael Graham
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
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…

744 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

17 Experts available now in Live!

Get 1:1 Help Now