Design Studio Formulas and Functions

Download Design Studio Guide

Formatting GL Segment Fields from Blank to '0000' in Journal Entries Report

1. Run the Journal Entries Report with valid parameters.

   

2. Click Interactive View in the top right corner of the Design Studio report preview after the report loads.

   

3. Navigate to Resource View on the left side of the report. Scroll to the bottom of the section, expand Dynamic Resources, then expand User Defined Functions, and click Add Function.

   A User Defined Function editor window opens.

   

4. In the Function Editor window, enter an appropriate name for the function and paste the following code in the text editor:

   arguments: string segment; if(IsNull(segment)) { return '0000'; } else { return segment;
   }

   This function returns a specified string (in this case, '0000') when the input string is NULL or blank; otherwise, it returns the string as is. You can replace '0000' with any value enclosed in single quotes. This function allows you to pass any database field (such as GL segments) and apply the logic to replace blank values.

   

5. Click the error check button in the top left corner above the editor to validate the function. Ensure it is error-free, then click OK.

   

6. Create a new formula by navigating to the bottom of the Resource View section, expanding Dynamic Resources, then expanding Formulas, and clicking Add Formula. A Formula editor window opens.

   

7. In the Formula Editor window, enter an appropriate name for the formula and specify how you want to use it (for example, Detail or Aggregation). Paste the following formula in the text editor:

   @ModifySegment(<DB Field>)

   Replace <DB Field> with the actual database field you want to modify. Use the '@' symbol to reference both the field and the function. For example:

   @ModifySegment(@Segment1)

   

   Perform an error check and click OK when complete. Create additional formulas for other fields as needed:

   @ModifySegment(@Segment2)
   @ModifySegment(@Segment3)

8. Drag and drop the new formula field into your report as needed. For example, you can replace the Segment1 column with the updated formula field to display '0000' instead of NULL or blank values.

   

Adding Concat Formula

1. Run a report in the UI.

   

2. After executing the report, click Interactive View.
   Scroll down to Dynamic Resources under the Business View section. Click Formulas, then click Add Formula.

   

3. The Formula Editor window opens. Write a formula based on your requirements.

   For example, to concatenate Account Code and Account Description:

   • Under the Fields section, double-click Account Code and Account Description.
   • Insert a '+' sign between the two data points for concatenation and add ' - ' in double quotes.

   

4. Click the checkmark symbol to verify the formula for errors. If No error found appears, the formula is syntactically correct. Click OK to close the Formula Error Check window.

   

5. Change the formula name as needed. The Use As field is blank by default. Click the dropdown and select Group.
   Click OK to close the Formula Editor window.

   

6. Your formula appears in the Formulas section under Dynamic Resources.

   

7. Drag and drop the newly created formula to the crosstab. You can change the row name in the Label section or select the Auto Map Field Name option.

   

8. Drag and drop the existing Account Description row out of the crosstab.
9. Right-click the formula name and select Autofit

   

   .
10. The final output displays the dynamic formula in the crosstab.

    

Adding a New End of Month Date Formula

1. Run the report with valid parameters in the UI.

   

2. Click Interactive View in the top right corner of the Design Studio report preview after the report loads. Then click the Resource View icon.

   

3. In Resource View, navigate to the bottom of the section, expand Dynamic Resources, expand Formulas, and click Add Formula.

   

4. In the Formula Editor window, enter an appropriate name for the formula (for example, "End-Of-Month") and specify how you want to use it (for example, Detail or Aggregation). Paste the following formula in the text editor:

   FirstDayOfMonth(ToDate(DateAdd("M",1,ToDateTime(@"Ledger Date")))) - 1

   This formula calculates the end of month date for any reference date. In this example, "Ledger Date" is used to round to the associated month end date based on the Gregorian calendar.

   

5. Click the check icon on the left side of the text editor window to verify that the formula is error-free. Click OK to close the Formula Editor window.

   

6. Drag and drop the newly created formula from the Formula section to the desired position in the report.

   

   Note: To format the date, right-click a data cell in the new formula column and select Properties. Make the changes as needed.