Calculation Screen

Calculation Features
Calculation Types
Creating Calculations

Calculation Features

• Calculations as Sequence keys and Record Selection fields — Calculations have been designed to take full advantage of SQL. Nearly every calculation is available to use as a sequence field or record selection field.
• Sequence/Record Selection Availability — The Work-with Calculation screen shows which calculations are available for Sequence/Record Selection within your application.
• On-screen Calculator — A calculator is displayed on screen to easily enter numbers and operators.
• Cursor Sensitivity — Place your mouse where you would like your field to go or highlight text and select a field. Rather than placing the text at the end of your expression, your value will automatically be inserted at the location of your cursor.
• Data Type selection — Selecting your data type will automatically filter out attribute options so that you can only choose from valid options.
• Easy to add SQL functions — After selecting that you wish to include an SQL function, a list of available options and the proper syntax is shown for you.
• Edit Codes — Edit Codes have been designed to show you the appropriate output based on the attributes of your calculation, rather than having to remember every IBM Edit Code.
• Error Messages — In the occurrence of bad syntax, the calculation screen will provide a specific error message to help you resolve the issue.
• SQL Edit Checks — m-Power will evaluate your SQL syntax. If there are any errors they will be caught before compile, rather than at runtime.
• System Values — Your calculation can include system values (System Date, Time, User, etc…) with ease.
• External Objects — External objects are easy to add. Simply select that you wish to call an object, define attributes and click Add.
• MTD/YTD — Useful for creating time-based calculations, based on a true-date field. Options include Year to Date, Month to Date, and X number of Days.

Note: Hover over your fields to see the field name and field attributes.

Calculation Types

1. Database
2. System Value
3. Parameter
4. Application
5. External Object
6. MTD/YTD

Note: Once a calculation has been added, you are not able to change this option for the given calculation.

1. Database

This is the default option for all new calculations. This option allows you to add almost any kind of calculation. Numeric, and SQL functions are called through this option. Under the expression text area is a list of fields already defined to this application. For more information, simply hover your cursor over the field descriptions to show the field name and field attributes. Here are common example of how expressions are utilized:

Utilizing the Add, Subtract, Multiply, or divide operations with numeric fields

Numeric calculations involve any kind of arithmetic logic. Most common values would be similar to this:

&FIELD1 + &FIELD2     Two fields

&FIELD1 / 72     Fields, in conjunction with Static Numbers

Of course, you can use + (Addition), – (Subtraction), * (Multiplication), and % (Division).

Note: You can utilize the on-screen calculator to enter non-field information (such as numbers or numerical operators) or use your keyboard.

Note: If you inadvertently press the "Clear" button on the onscreen coordinator, typing CTRL + Z will restore what you typed, barring you did not make any additional changes.

Creating literal fields

Alpha calculations are very useful for displaying literal text. To enter literal text, be sure to wrap any literal text within " ". For instance, if I wanted the word Hello as my calculation for each record, I would add the following as my Calculation Expression: "Hello"

Note: Alpha calculations are unable to serve as record selections/Sequence (key) fields.

For use within If/Else logic

If/Else logic is vital for times when your output depends upon something else. You can test any field against another field, a literal value, or blank/null. For operand LS – in the list, you should enter each list value separated by a blank. If you cannot fit all your list values on one line you must enter an additional list statement separated by an OR. For operand RG – range, you must enter two values separated by a blank.

Note: To test for blanks in a field use the value *BLANK.

Note: Calculations that contain If/Else logic are unable to serve as record selections/Sequence (key) fields.

To call SQL Expressions

This option allows you to call SQL expressions for your specific database. Click the Functions tab and your listed fields will be replaced with a list of pre-defined SQL functions. Hover over any of the listed SQL functions to see what the function does and the appropriate syntax. Clicking on the function will place the default syntax in the calculation for you. From here, click the "Fields" tab to be shown a list of given fields. Lastly, add the fields to the SQL syntax to complete your SQL Expression.

For instance, if I wanted to concatenate field ABC & field XYZ, I would click the "CONCAT" function. This will place the correct syntax into my calculation. Next, I click the fields tab to see a list of the fields I can use. Double-click "field1" and click field "ABC". This will replace "field1" with "&ABC". Repeat the process for the second concatenated field.

Note: The examples provided are examples of the functions in their simplest form. You can add additional syntax as needed (For instance, in the example listed above, I could concatenate a third field if necessary, even though it is not listed within the default syntax).

Note: mrc recommends utilizing SQL functions over Java functions whenever possible as SQL functions allow you the added benefit of the calculation allowing sequencing and/or record selection due to the fact that SQL functions are performed directly within the SQL statement.

Note: This list of available expressions can be modified to add/remove SQL functions. This file is located in \m-power\proddata\conf\mrcSqlFns.xml

The above screen shot shows the Calc Type has been changed to "SQL Expression". Notice also that the Data Type has been set to Alpha, eliminating the "Decimal option". Also, notice the list of SQL Functions listed where the Fields normally are located. Click the "Fields" tab to go back to the Fields listing.

Note: You can easily replace the default syntax of the SQL expression, by highlighting the field you wish to replace, then clicking the field you want to replace it with. m-Power will automatically replace the highlighted text with the field you selected.

2. System Value

System Values allow you to enter "common" information into your application. For example, this option allows you to enter the Current Date, Time, User, Session ID, Data Dictionary, and/or Program Name. Specify a field Description, then click the System value you wish to include. The field attributes, and default calculation expression will be entered for you. Click "Accept" to create your calculation.

In the above screenshot, you can see that I have selected "System Value" as my Calc Type & have also specified a Calculation Description. As soon as I clicked the "Current Time" button, the Data Type, Length, and Decimal were set for me automatically.

Note: It is no longer necessary to add *TIME, *DATE, etc. to the application description.

3. App Parameter

Application Parameters serve as a way for you to pass data from one application to another. This method is especially useful if you are trying to pass a data field from one application to another application where no field exists to accept that parameter. An App Parameter can be created to match the attributes of that field. To do this select "App Parameter" and specify the length you wish your App Parameter to be. Lastly, specify a Description and click "Accept". After your Application Parameter is created, go back to your original application and pass the necessary field to the appropriate calculation name of your Application Parameter. More information about Application Parameters can be found here.

In the screenshot listed above, you can see that I have specified "App Parameter" as my Calc Type. I can customize the application by Description, Data Type, Lenth, & Decimal. The calculation expression is determined for me.

4. Application

This option allows you to call Java Functions. However, mrc recommends that you always use Database calculations as this option allows the ability for the calculation to be a sequence field and/or a record selection field.

5. External Object

This option allows you to call external Java/RPG/CL programs from within your calculation. When called from a calculation, your external object will be called for each row that is printed. Usually, customer's should choose this option (rather than calling an external object from the Application Options screen) when their object needs to return a value. When that happens, creating a calculation allows you to place that returned value into the calculation itself. Select "External Object" as the Calc Type, specify the necessary Data Type, Field Length, & Field Decimal. Click the "Choose external object" button to advance to a secondary screen that will allow you to select the object, the object location, and any object parameters.

5. MTD/YTD Calculations

Available in Report and Summary templates, this calculation feature allows you to create time-based calculations that allow you to compare data in date ranges. Rather than having to create complicated conditions to capture specific time periods, simply instruct m-Power what time frame you would like to examine and your application will automatically determine the proper values. This feature support is flexible: It allows you to specify a yearly start day (defaulted to January 1st), and a default month start date (defaulted to the 1st) in the event your organization starts the year and/or month on a non-traditional day. After selecting one of the four "Date Range Type" options, select the "Apply to Field." This is the field that will computed. In "Date Field," select your true-date field. If no field exist, your application contains no true-dates. Please revisit the "Data Selection" screen to pick one. Alternatively, you can use a previous calculation to convert an existing date into a true date. Finally, in the remaining option, select how far back you would like this calculation to compute. If you would like to compare this year's value to last year's value, create two calculations. In the "Number of Years Back" option for the first calculation, select 0. For the second calculation, specify a 1 here (for one year back).

In the screenshot listed above, you can see that I have specified a YTD calculation, looking for the Sales Amount, based on my Invoice Date for the previous year.

6. Application

This option allows you to call Java Functions. However, mrc recommends that you always use Database calculations as this option allows the ability for the calculation to be a sequence field and/or a record selection field.

Creating Calculations

In the above screenshot, I have specified External Object as my Calc Type, I have specified a Description and set a Data Type. From here, I simply will click "Choose an external object". The next screen will allow me to pick the actual object needed.

Data Type — This option allows you to control whether your field will be: Alpha, Numeric, Date, Time, or Timestamp. When selecting a data type, the Length & Decimal attributes will automatically be filtered for you to only show you valid options. For instance, when choosing a numeric calculation, the "blank" option of Decimal length will be removed as numeric calculations must have a decimal length. Or, when choosing alpha calculation, the decimal dropdown will become disabled as alpha calculations do not have a decimal.

Note: Be sure to specify the data type before creating your calculation expression.

Format — The Format option allows you to control how your numeric (or DATE) output will appear. Utilize the "call out" icon to see how your data will appear with the given field attributes and format selected.

Note: The format option is only valid for numeric calculations & Date calculations. Additionally, the "call out" icon is only available for numeric calculations.

Note: The calculation screen is designed to work with current m-Power templates. However, existing Web 1.0 (Classic Templates) applications will continue to work under the following restrictions:

• Non-modified applications will continue to work.
• You may recompile an application, so long as you make no changes to calculations.
• You can make changes to calculations, but only "Application" calculations and "External Object" calculations can be modified/created.
• Please consider utilizing current m-Power templates as it offers quicker, more robust web applications.

Note: Some calculations are not allowed to serve as Record Selections or Key (Sequence Fields). Those that cannot are listed below for your reference:

• Application Calculations
• External Object Calculations
• Any calculations that refer to an External Object or Application calculation

Note: This screen requires the use of Internet Explorer to obtain its full functionality.