How to set a lower bound to a value without having to use an IF statement?

1 Answer 39 Views
Expressions Report Designer - Web
Pascal
Top achievements
Rank 1
Iron
Iron
Pascal asked on 22 Sep 2023, 02:37 PM

I have a complicated expression that I'd like to set a lower bound to. 

The value should never dip below 34. 

The only working solution I've found is:

Let f(x) be the expression used to calculate the value.

To include a lower bound I've been doing "=IF(f(x) < 34, 34, f(x))"

This works well when f(x) is simple but this explodes in length as the length of f(x) increases.

If I wanted to also include an upper bound of 50 I would need to write the expression as 

"=IF(f(x) < 34, 34 IF(f(x) > 50, 50, f(x)))"

 

I've tried the Min function but it just remains blank.

"=Min(Array(5,7))" does not return 5

 

For context here is the expression I created to calculate the proper zoom level for a static google map. 

Please note that these formulas are generated by another program I created to help me manage the complex calculations I need to do. There is no way I would ever try to manage these manually. Currently, there are 30 pins being managed and my boss is thinking about increasing this to 90.  

Floor(IF(LOG(((((Parameters.PixelHeight)/2-(Parameters.Buffer))*Parameters.Pi/64))/ABS((LOG((1+(SIN((Max(X_COORD))*(Parameters.Pi)/180)))/(1-(SIN((Max(X_COORD))*(Parameters.Pi)/180))))/LOG(EXP(1)))-(LOG((1+(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180)))/(1-(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180))))/LOG(EXP(1)))))/LOG(2) < LOG(((((Parameters.PixelWidth)/2-(Parameters.Buffer))*360/256))/(Max(Y_COORD)-((Max(Y_COORD)+Min(Y_COORD))/2)))/LOG(2), LOG(((((Parameters.PixelHeight)/2-(Parameters.Buffer))*Parameters.Pi/64))/ABS((LOG((1+(SIN((Max(X_COORD))*(Parameters.Pi)/180)))/(1-(SIN((Max(X_COORD))*(Parameters.Pi)/180))))/LOG(EXP(1)))-(LOG((1+(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180)))/(1-(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180))))/LOG(EXP(1)))))/LOG(2), LOG(((((Parameters.PixelWidth)/2-(Parameters.Buffer))*360/256))/(Max(Y_COORD)-((Max(Y_COORD)+Min(Y_COORD))/2)))/LOG(2)))

Which has to be reused when I need to determine the position of a pin in my report.

200+256*POW(2,(Floor(IF(LOG(((((Parameters.PixelHeight)/2-(Parameters.Buffer))*Parameters.Pi/64))/ABS((LOG((1+(SIN((Max(X_COORD))*(Parameters.Pi)/180)))/(1-(SIN((Max(X_COORD))*(Parameters.Pi)/180))))/LOG(EXP(1)))-(LOG((1+(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180)))/(1-(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180))))/LOG(EXP(1)))))/LOG(2) < LOG(((((Parameters.PixelWidth)/2-(Parameters.Buffer))*360/256))/(Max(Y_COORD)-((Max(Y_COORD)+Min(Y_COORD))/2)))/LOG(2), LOG(((((Parameters.PixelHeight)/2-(Parameters.Buffer))*Parameters.Pi/64))/ABS((LOG((1+(SIN((Max(X_COORD))*(Parameters.Pi)/180)))/(1-(SIN((Max(X_COORD))*(Parameters.Pi)/180))))/LOG(EXP(1)))-(LOG((1+(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180)))/(1-(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180))))/LOG(EXP(1)))))/LOG(2), LOG(((((Parameters.PixelWidth)/2-(Parameters.Buffer))*360/256))/(Max(Y_COORD)-((Max(Y_COORD)+Min(Y_COORD))/2)))/LOG(2)))))*(((Item(1,AllValues(Y_COORD)))+180)/360) - 256*POW(2,(Floor(IF(LOG(((((Parameters.PixelHeight)/2-(Parameters.Buffer))*Parameters.Pi/64))/ABS((LOG((1+(SIN((Max(X_COORD))*(Parameters.Pi)/180)))/(1-(SIN((Max(X_COORD))*(Parameters.Pi)/180))))/LOG(EXP(1)))-(LOG((1+(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180)))/(1-(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180))))/LOG(EXP(1)))))/LOG(2) < LOG(((((Parameters.PixelWidth)/2-(Parameters.Buffer))*360/256))/(Max(Y_COORD)-((Max(Y_COORD)+Min(Y_COORD))/2)))/LOG(2), LOG(((((Parameters.PixelHeight)/2-(Parameters.Buffer))*Parameters.Pi/64))/ABS((LOG((1+(SIN((Max(X_COORD))*(Parameters.Pi)/180)))/(1-(SIN((Max(X_COORD))*(Parameters.Pi)/180))))/LOG(EXP(1)))-(LOG((1+(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180)))/(1-(SIN(((Max(X_COORD)+Min(X_COORD))/2)*(Parameters.Pi)/180))))/LOG(EXP(1)))))/LOG(2), LOG(((((Parameters.PixelWidth)/2-(Parameters.Buffer))*360/256))/(Max(Y_COORD)-((Max(Y_COORD)+Min(Y_COORD))/2)))/LOG(2)))))*((((Max(Y_COORD)+Min(Y_COORD))/2)+180)/360)

 

1 Answer, 1 is accepted

Sort by
0
Dimitar
Telerik team
answered on 27 Sep 2023, 10:26 AM

Hello Pascal,

Indeed, the Min aggregate function does not seem to work when used with the Array utility function. The function instead expects an expression such as the following:  = Min(Fields.Column1).

As a way to simplify the expression, you may create a custom user function. That would allow you to use C# code for calculating the value and .NET should offer more options to make the calculations easier. Custom functions are supported in the Web Report Designer too and the way to integrate them is by following the approach for the ObjectDataSource since they are both resolved through external assemblies - ObjectDataSource Wizard Tutorial - Telerik Reporting.

This way, in the report definition, you would only need to call the custom function, passing to it all the necessary arguments that you are using in the calculations(report parameters, etc.). In the C# code, it will be easier to read the function since you can format the code and you could even use external libraries that already have the calculation methods that you need to use.

I hope this will help, please let me know if you have any additional questions.

Regards,
Dimitar
Progress Telerik

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Pascal
Top achievements
Rank 1
Iron
Iron
commented on 02 Oct 2023, 02:05 PM

Sadly I don't have access to the source code and hence can't create "custom user functions". Any suggestions for users who don't have access to the source code?
Dimitar
Telerik team
commented on 05 Oct 2023, 08:11 AM

Hello Pascal,

By 'source code', are you referring to the Telerik Reporting source code or the program that performs the calculations?

Just as you are currently calculating the values beforehand, in the external program, you can still do that and then use the calculated values in the custom user function. That would require you to build the assembly with the functions when there is a change, at least if you hard-code the values there but you could save them, for example, in a database and then the latest values can be retrieved in the custom function dynamically. This way, there would be no need to rebuild the assembly after each change. And since you are writing C# now, you could use LINQ to get the minimum value from the array of numbers - Enumerable.Min Method.

 

Tags
Expressions Report Designer - Web
Asked by
Pascal
Top achievements
Rank 1
Iron
Iron
Answers by
Dimitar
Telerik team
Share this question
or