Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Evaluating formula in Crystal Reports

Posted on 2013-06-29
Medium Priority
710 Views
I have a formula in Crystal that I would like to convert to a numeric value.

The formula is of the form "1024*2 + 2400*1.5 - 150"

It is a string that originally was "H*2 + W*1.5 - 150", I was able to convert to the first string but now need to convert to a numeric value for further calculations.

Cheers
0
Question by:PeterCoole
[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
• 4
• 4
• 3
• +1

LVL 77

Expert Comment

ID: 39286823
Can you explain why the 'formula' is a string.
Why can't you just create a true formula that includes the relevant fields ?
0

LVL 101

Expert Comment

ID: 39286928
If you want the numeric version

1024 * 2  +  2400 * 1.5  -  150

With the variables or fields

{HeightField} * 2  +  {WidthField} * 1.5  -  150

mlmcc
0

Author Comment

ID: 39287634
The formula are entered by the user and the h and w values are variables. There are actually 40 or more variable. The sample I gave was just one of hundreds possible calculations
0

LVL 77

Expert Comment

ID: 39287951
"The formula are entered by the user ..."

How do they do that?
How do you ensure that they enter a valid formula?

This sounds more like something that should be done in Excel.
0

LVL 101

Expert Comment

ID: 39288108
Crystal is not set up to handle that easily.  You will have to write  parser to read through the string and determine what values to substitute into the formula.  It can be done but the code is not going to be easy to write.

Are they putting in database field names?

mlmcc
0

Author Comment

ID: 39288139
Our application is storing the values in the database (as strings), and they are pre parsed at that time. At the time of using the formula's then the replacement values are substituted and we would like to calculate the values.

Replacing the H, W, A .. Z, H1 ... H9, W1...W9, A1 ... Z9 is the easy bit, converting the "Numeric string" to the number is tough bit.

Might have to get the formula translated and the value passed through to the report at the time of reporting. I was hoping to do it all in Crystal.
0

LVL 101

Expert Comment

ID: 39288548
So what is it Crystal has to do?

Will you be passing the 4*2 + 4*5  .... form or the one with variable in it?

Do you have standard formulas that are used all the time?

mlmcc
0

Author Comment

ID: 39288951
The database fields have formula such as:
"H*2 + W*3 - 150"
"H*W/2 +220"

The H & W can actually be up to about 40 different variables A ... Z, H1 ... H9, W1 ... W9

Before the user runs the report they will then assign values to the vaiables, H = 2200, W= 1250, H1 = 800 etc, but just for those variables that are defined in the formaul set they want to use at the time.

In Crystal I have been able to substitute the values into the formula so i end up with
"2200*2 + 1250*3 - 150"  (or what ever it is)

What I am having dificulty with is how to now convert this string numeric calculation into an actual numeric result. In this case it shoudl result in 8000.

We had the application in an old dBase (Clipper) program that was able to do macro substitution and so was able to get the result easily. But this is not so easy within Crystal.

Thanks
0

LVL 77

Assisted Solution

peter57r earned 375 total points
ID: 39289229
I think you are going to struggle to find a way to do this.
Newer (like .NET) languages don't have such a feature as the one you found in Clipper.

Access VBA has an Eval() function, which also does what you require,  but again this goes back to a period nearly 30 years ago. Excel has  the same thing as Evaluate().

You can see here the complexities of doing it in a .net language..
http://social.msdn.microsoft.com/Forums/vstudio/en-US/7f62b87d-a35c-4074-a0f0-84a9dd7ff0a5/convert-string-to-formula

I don't know whether you would be able to find (or create, I guess) an add-in to do this sort of thing.  It's outside my scope, I'm afraid and I don't know how you would start.
0

LVL 101

Expert Comment

ID: 39289890
What are trying to allow the users to do?

Do you have a fixed number of formulas or are the users entering formulas they that fit their analysis?

mlmcc
0

LVL 35

Accepted Solution

James0628 earned 375 total points
ID: 39292464
In addition to the basic problem of parsing the string to extract the values and operators, it seems that you're observing operator precedence (multiplication and division before addition and subtraction), which adds a whole new level of complexity.  I'm not going to say that it would be impossible to do that in CR, but "improbable" might be a good description.  I think you'd have to do something like implement something like a stack (an array), where you could store the values until you had all of the operators, so you knew which operations to perform first.

If there is known set of formats that the formulas will always follow ("H*2 + W*3 - 150", "H*W/2 +220", etc.), then that might simplify things.  If the report can determine the "format" for a formula, then it knows how to use the values, without having to parse the string, looking for operators and trying to figure out which ones to use first.  But if the formulas could take any form, then I doubt that trying to evaluate them in CR would be practical.

James
0

Author Closing Comment

ID: 39293176
Thanks Guys, you have at least confirmed my suspicions. This is not going to be easy, I will try to get the programmer to modify the program that calls my report so they can do the calculations and save them into a temp table that i can link. What shame :-(

One though I had was, could I use an SQL statement to generate the required values, so during the Query process send in the formula as one of the fields and get it to generate the calculation as part of the resulting data set.

Any thoughts?
0

LVL 35

Expert Comment

ID: 39294001
> ... could I use an SQL statement to generate the required values ...

What are you trying to "generate" in the SQL?

If you're just trying to use SQL code to evaluate the formulas in the strings, then you've got the same problem, and unless you've got some kind of function that will do it (like the ones that Peter mentioned earlier), my first instinct is that it would be easier in CR.

OTOH, if the components that go into the formula are available (something like a list of the operands and operators), then you might be able to do the calculation based on those (rather than trying to interpret the string).

James
0

## Featured Post

Question has a verified solution.

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

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 â€¦
Microsoft Visual FoxPro (short VFP) is a programming language with itâ€™s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msdâ€¦
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â€¦
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper cornâ€¦
###### Suggested Courses
Course of the Month11 days, 4 hours left to enroll