Formula Fields
Formula fields are managed from Formula Fields screen. Once a calculated field is created, it is placed on Fields List screen. You can then drag and drop it on the report like any other field.
- On the Desktop Studio screen, click button from the toolbar.
OR - On the Desktop Studio screen, from Tools menu, click Formula Fields. The Formula Fields screen appears with a list of calculated fields (formula fields) designed for the report.
Figure 1:Formula Fields screen
You can perform the following actions:
Action | Description |
Add | To add a formula field on the report |
Modify | To modify the selected formula field |
Delete | To delete the selected formula field |
OK | Saves all the changes made to the formula field |
Cancel | Close screen without making changes to the report |
Up and Down arrow buttons | Select a formula field row and click Up button or Down button to move the selected formula field up or down. When you want to use a formula field within another field, you may prefer to move the “field being used” up to indicate its dependency on the field being created and the one in which it is used |
To add/design a new formula field
Follow the below steps to create formula fields:
On the Formula Fields screen,
Figure 2: Formula Fields screen
- Click Add The Formula Fields Expression screen appears.
Figure 3: Formula Field Expression screen
- In Name, specify a unique name for the formula field being designed.
- In Return drop-down box, select a return type such as number, character, Boolean, and date corresponding to the specified name.
- In Expression, specify the formula followed by an ‘=’ sign and specify expression for the field.
Note: Report will not run if there is any syntax error in the formula.
- Click Compile button to check the formula for syntax errors.
- Click OK to save the created format. The newly designed formula field appears in the list on the Formula Fields.
To edit/modify a formula field
- On the Formula Fields screen, select the formula field that you want to edit.
- Click Modify The Formula Field Expression screen appears.
- Edit/modify the required details.
- Click OK to save the changes.
To delete a formula field
- On the Formula Fields screen, select the formula field that you want to delete.
- Click Delete
- Click OK to save the changes.
General syntax
You can make simple to complex formula for a formula field. General syntax for a formula field is:
FormulaName = formula
Follow JavaScript syntax to create a formula. To create formula, you can use field names and define variables. A formula may have if construct as well as nested if construct. You can use logical operators too. If want to add more than one statements in formula, use semicolon ‘;’ as separator between two statements.
Example:
- NewForm1 = var a = 5 ; b = 3 ; if (a!=b) { f = a } {NewForm1=f}
- TotalAmount = var total ; if (unitprice < 10 ) {total = unitprice*quantity} else {total = unitprice} {TotalAmount = total}
Styles (Formats)
You need to create a style/format before it can be used. All the activities regarding setting up different formats are carried out from Styles screen.
- On the Desktop Studio screen, click button on the toolbar.
OR - On the Desktop Studio screen, from Format menu, click Styles. The Styles screen appears.
Figure 4: Styles screen
You can perform the following actions:
Action | Comments |
Add | To add a style/format on the report |
Modify | To modify the selected style/format |
Delete | To delete the selected style/format |
OK | Saves all the changes made to the style/format |
Cancel | Close screen without making changes to the report |
Up and Down arrow buttons | Select a style row and click Up button or Down button to move the selected style/format up or down. |
To create/add a new style/format
On the Styles screen,
- Click Add button. The New Style screen appears.
Figure 5: New Style screen
- In Name, specify a unique name for the style/format being created.
- Select styles settings such as font, font style, size, back color, fore color, under line, strikethrough on Styles
- Select Borders settings such as line style, color, shadow on Borders
- Click OK to save the created style/format. The newly created style/format appears in the list on the Styles
To modify a style/format
- On Styles screen, select the style/format that you want to modify.
- Click Modify The Modify Style screen appears.
- Edit/modify the required details.
- Click OK to save the changes.
To delete a style/format
- On Styles screen, click the format that you want to delete.
- Click Delete The message, “You are trying to delete the selected item. Are you sure?” appears.
- Click OK to delete the style/format.
Global Styles
Styles set up here can be imported in any of the reports. Once it is imported, it will be available in respective open report through Styles drop-down list. To navigate to Global Styles screen:
- From Tools menu, click Options. The Options screen appears.
- Under Global Styles area, under Custom Design subtab, select Manage Styles check box.
- Click OK.
To create a global style
- On Global Styles screen, under Custom Design subtab, click Manage Styles The Styles screen appears.
- Click Add The New Style screen appears.
- Specify style settings such as font, font style, size, back color, fore color, under line, and strikethrough.
- Click OK to save the global style.
To modify a global style
- On Global Styles screen, select the style that needs to be modified.
- Click Modify The Modify Style screen appears.
- Edit/modify the required details.
- Click OK to save the changes.
Important: To get effect of modified styles if they are used (imported) in any of the reports, click Auto update where used check box before clicking Manage Styles button.
To delete a global style
- On Global Styles screen, select the style that needs to be deleted.
- Click Delete The message, “You are trying to delete the selected item. Are you sure?” appears.
- Click Yes to confirm and delete the global style.
Conditions
Conditions are used in conditional formatting and notifications. You need to create a condition before it can be used. All the activities regarding setting up different conditions are carried out from Conditions screen.
- On the Desktop Studio screen, click button on the toolbar.
OR - On the Desktop Studio screen, from Tools menu, click Conditions. The Conditions screen appears.
Figure 6: Conditions screen
You can perform the following actions:
Action | Comments |
Add | To add a condition on the report |
Modify | To modify the selected condition |
Delete | To delete the selected condition |
Import | To import an existing condition |
Export | To export a newly created condition |
OK | Saves all the changes made to the condition |
Cancel | Close screen without making changes to the report |
To create a new condition
On the Conditions screen,
- Click Add The New Condition screen appears.
Figure 7: New Condition screen
- In Name, specify a unique name for the condition being designed.
- In Expression, specify the formula followed by an ‘=’ sign and specify expression for the field. Refer to the section ‘Expressions’ below.
- Click Compile button to check the formula for syntax errors.
- Click OK to save the save the created condition. The newly designed condition appears in the list on the Conditions
To modify a condition
- On Conditions screen, select the condition that you want to modify.
- Click Modify The Modify Condition Expression screen appears.
- Edit/modify the required details.
- Click OK to save the changes.
To delete a condition
- On Conditions screen, click the condition that you want to delete.
- Click Delete The message, “You are trying to delete the selected item. Are you sure?” appears.
- Click OK to delete the condition.
Expressions
Following are the expressions:
Use this operator | To indicate this |
&& | And |
|| | Or |
! | Not |
== | Equal to |
!= | Not equal to |
< | Less than |
> | Greater than |
<= | Less than equal to |
>= | Greater than equal to |
|| | Or |
! | Not |
- To use a text for checking, enclose the text within single quotes, for example: E_Name==’Pinto’.
- To use a date for checking, specify the date in yyyy-mm-dd format and enclose it within single quotes, for example: JoiningDate==’2004-05-30’.
- Numbers can be used as it is, for example, Sales>5000.
If a field of one type is checked with that of another type, then the conditional format will not be applied.
Examples:
if ( empno > 7500) {return true} else {return false}
if ( Sal > 7500) {return true} else {return false}
if (CustNo == ‘Elsa’){return true}
if (ename == ‘Elsa’){return true}
if (empno > 7500 || ename != ‘SMITH’ ) {return true}
if ((sal > 1400 || empno >7500) && (comm > 2)) return true
Conditional Formatting
Using conditional formatting, you can apply formatting based on the outcome of pre-set conditions (set at design time) and checked at run time. Conditional formatting is applied at runtime on a field or a label control as shown in the examples below:
- Display the record in Bold if result of a student is ‘Fail’.
- Display the figure in red and in braces if the profit figure is ‘Negative’.
- Display the sales value in light blue background if the sales target is met, in yellow if the target is missed by up to 25 %, and in red if it is missed by more than 25%.
Before applying conditional formatting, you should set the required conditions and required formats.
To apply formats to controls
Conditional formats are applied to a control or a label using Conditional Formatting screen. Before opening the Conditional Formatting screen, select the control on which the format is to be applied.
To access Conditional Formatting screen,
- On the Desktop Studio screen, click button from the toolbar.
OR - On the Desktop Studio screen, from Format menu, click Conditional Formatting. The Conditional Formatting screen appears.
Figure 8: Conditional Formatting screen
You can perform the following actions:
Action | Comments |
Add | To create/add a new conditional format |
Delete | To delete the selected conditional format |
Import | To import the pre-existing conditional formatting |
Export | To export the newly created conditional formatting |
Conditions | To open Conditions screen to create a new condition |
Formats | To open Styles screen to create a new style/format |
OK | Save all the changes made to the condition |
Cancel | Close screen without making changes to the report |
To apply/create conditional formatting
On the Conditional Formatting screen,
- Click Add The Conditional Formatting screen appears.
- Select a condition from On Condition drop-down list, for which you want to apply a format.
- Select the format from Apply Style drop-down list that you want to apply for the selected condition of the control.
Note: When you apply styles to a control from style drop down box, respective properties of the control will not automatically change (in property pane).
- After applying formatting preferences for all the conditions, click OK button to save the created conditional formatting. The newly designed conditional formatting appears in the list on the Conditions Formatting.
Notifications
Notify feature provides report users proactive information when a pre-set condition satisfies on report data. For example, Intellicus can notify users by sending an alert during report generation when the total sale has exceeded over a set amount.
Notifications for a report are created during report design. When a report having notifications is uploaded to the portal, all the notifications set for that report along with that set for other reports are listed on Notification List page of the portal. Users can subscribe to a notification and set alerts that they want to receive. Alerts sent to users are viewed on the Alert Viewer page of portal.
To access Notification List screen,
- On the Desktop Studio screen, click button from the toolbar.
OR - On the Desktop Studio screen, from Format menu, click Notifications. The Notification screen appears.
Figure 9: Notifications screen
You can perform the following actions:
Action | Comments |
Add | To add a new notification |
Modify | To modify a notification |
Delete | To delete a notification |
Conditions | To open Conditions screen to create a new condition |
OK | Save all the changes made to the notification |
Cancel | Close screen without making changes to the report |
To add a notification
You can add a notification from Notifications screen. Notification details set on this screen appears on portal’s notification page as a standard notification message.
On the Notifications screen,
- Click Add The New Notification screen appears.
Figure 10: Notification Details (New Notification) dialog box
- In Name, specify a unique name for the notification being added.
- In Condition, Select the condition that would be evaluated. If the condition set here evaluates to true at the time of report generation, an alert is generated.
- In Title, specify a text that will appear as subject on the email.
- In Short Description, specify a text that will appear as a message on email (first line in message) and as a message on IM.
- In Description, specify a text that will appear as message on email. This text will not appear on IM.
Note: Context-Help is available on Title, Short Description and Description. Use context help to auto-populate field names, parameter names in these fields.
- Click OK to save the save the created notification. The newly added notification appears in the list on the Notifications
Note: To include values as a part of message, add them in the message enclosed in <% and %>. For example, Profit increased by <%ProPer%>. Where, ProPer is a field.
To modify a notification
- On the Notifications screen, select the notification that you want to modify. The Modify Notification screen appears.
- Edit/modify the required details.
- Click OK to save the changes.
To delete a notification
- On the Notifications screen, select the notification that you want to delete.
- Click Delete The message, “You are trying to delete the selected item. Are you sure?” appears.
- Click OK to save the changes.