Create a Template

New Quick Start templates can be added by clicking the ‘Add New’ button on the ‘Manage Criteria Templates’ dialog in the ‘Other’ menu on the GL Wand toolbar. They can also be uploaded in bulk by using the Excel template provided in the Wands install zip file. This workbook includes tabs for each of the Quick Start Template Types and an SQL Query to load the values captured in the workbook.

Template Type

Select the appropriate Template Type from the list. Balance and Period Templates are used for the Inquiry Wizard and Report Wizard Columns are used in Report Wizard.

Criteria

Populate the relevant parameters on the Criteria tab to suit your requirement.

Tip: Both Template Name and Description are used when searching templates from the Quick Start Wizards. Ensure that both the Name and Description are detailed and contain words that users are likely to search for.

Period Template

Parameter	Description
Template Name	The Name assigned to the template. Visible in the selection lists of the Quick Start Wizards and included in searches in these lists.
Template Description	A description of what the template relates to in more detail than the name. Visible in the selection lists of the Quick Start Wizards and included in searches in these lists.
Fiscal Year & Fiscal Year Type	The value in the Fiscal Year can use one of two approaches depending on the Fiscal Year Type Field: Fiscal Year Type = Absolute: The static year (e.g. 2018) Fiscal Year Type = Relative: A dynamic value indicating the number of years to offset against the current year (e.g. -1 for prior year)
Accounting Period & Accounting Period Type	The value in the Accounting Period can use one of two approaches depending on the Accounting Period Type Field: Accounting Period Type = Absolute: The static Period (e.g. 3 to always return the 3rd period) Accounting Period Type = Relative: A dynamic value indicating the number of periods to offset against the current period (e.g. -1 for prior month)
Balance Type	Used in conjunction with the Accounting Period and Fiscal Year to determine the period of time to include in the Balance returned. PTD – Period to Date. All of the transactions posted in the specified period as at the date of running the report. QTD – Quarter to Date. All of the transactions posted in the quarter up to the specified period as at the date of running the report. YTD – Year to Date. All of the transactions posted in the Fiscal Year up to the specified period as at the date of running the report.
Sharing	Allows templates to be shared with all GL Wand users on the system (PUBLIC) or only available and visible to the user who loaded the template. Note: System Administrators will be able to view, edit and delete Private templates of all users.

Balance Template

Parameter	Description
Template Name	The Name assigned to the template. Visible in the selection lists of the Quick Start Wizards and included in searches in these lists.
Template Description	A description of what the template relates to in more detail than the name. Visible in the selection lists of the Quick Start Wizards and included in searches in these lists.
Business Unit	The Business Unit or Business Unit Tree Node as configured in PeopleSoft. The available Business Unit Trees will be displayed in this selection. To change the Business Units available to select, use the Change Business Unit function in the GL Wand toolbar. Note: When using a Business Unit Tree Node, the following criteria must match: All Business Units must share the same SETID for each of the queried ChartFields. ChartFields that have security applied must also share the same SETID. All Business Units must share a Ledger with the same name (and this must be the ledger that you want to pull a balance from). The selected ledger and Business Units should use the same Calendar. If your Business Units do not meet these criteria, you can build separate templates and add them in different columns and Sum them using Excel functions The Configuration Sheet function available in version 1.2.6 will provide useful information to assess which Business Units can be used concurrently.
Ledger	The Ledger as configured in PeopleSoft. The list of ledgers available for selection will be limited to those linked to the value selected in the Business Unit field.
Currency Type & Currency Code	Currency Type: Enter BASE or TRN all in upper case.Currency Code: The currency code as configured in PeopleSoft all in upper case e.g. “USD”. Examples: Currency Type: BASE and Currency: Wildcard (e.g. %) – Returns all balances for the specified parameters in the BASE currency of the Ledger. Currency Type: BASE and Currency: GBP – Returns the balance of all the transactions captured in GBP for the specified parameters in the BASE currency of the Ledger. Currency Type: TRN and Currency: GBP – Returns the balance of all transactions captured in GBP for the specified parameters in GBP. Currency Type: TRN and Currency: Wildcard (e.g. %) – Returns an error. Where BASE is used along with multiple Business Units, the defined Ledger must share the same Base Currency.
ChartFields	The ChartField values as configured in PeopleSoft. Enter the ChartField value here. You must enter it as a text value including any leading zeros e.g. enter ChartField value “10” as “0010” if this ChartField is configured as four characters with leading zeros in PeopleSoft. You may enter your ChartField values in a variety of flexible ways including Tree Nodes, ChartField values, ranges, wildcards, excludes etc. To include all values simply enter “%”. When creating Get Balance formulas always use “%” to denote all values as this allows more efficient SQL to be generated. Do not use a range like “000-ZZZ” as they will require unnecessary processing. The segment value parameters can be entered as: a single value representing a ChartField value e.g. “1500” might represent an account. a Tree Node represented as TREENAME:NODENAME will sum all ChartField values that roll up into the specified node. e.g. ACCTROLLUP:CURASSETS a range of values e.g. entering “1500-1600” in one of the parameters will sum all the values between 1500 and 1600 for that ChartField. a list of ChartField values e.g. entering “1500,1510,1590” in one of the ChartField parameters will sum the specific values entered. wildcard entries e.g. 1%00 will sum 1100, 1200, 1300, 1400 etc. or entering the value 1% will sum all values that start with 1. values to be included and then specific exclusions e.g. “1000-2000,~1500” will sum the values between 1000 and 2000 but will skip 1500. a combination of any of the above e.g. “1000-2000,2371,2375,2560-2580,3000,~3200,4000,~4161-4169”. In this way, you can build up your own calculation definition in the ChartField parameter. The GetBalance will only show the ChartFields that are set as active in PeopleSoft. The wizard will only show the ChartField values for the selected Business Unit and Ledger. Where Multiple Business Units or Business Unit Tree Nodes are entered, each of the queried ChartFields must share a common SETID. Any ChartFields with security applied must also share SETIDs. Running the Configuration Sheet (available in version 1.2.6) will assist with identifying the SETIDs for Business Unit and ChartField combinations. It will also advise whether ChartFields have been secured in PeopleSoft.
Budget Period	Optionally, enter the Budget Period as configured in PeopleSoft. This field will only become available when selecting a budget ledger.
Scenario	The scenario as configured in PeopleSoft. This field will only become available when selecting a budget ledger.
Multiply	Use this parameter to switch the sign of the balance by multiplying by -1. Also useful to show the number in a factor e.g. thousands by multiplying by 0.001. Note: Using this parameter, rather than equivalent Excel functionality, ensures that when the Get Balance function is returning a message, this message will be displayed in the cell. For example, if you capture your Get Balance function as =GLW_PSFT_Get_Balance(…) * -1 and the Get Balance result is Error (Period) due to an invalid period being entered, the cell will display #VALUE. If you use this parameter to multiply the balance by -1, the cell will return the Error (Period) message.
Decimal Places	Round the balance to the specified number of decimal places. Useful to use in conjunction with the Multiply parameter to show the number in a factor e.g. thousands by multiplying by 0.001 and then round to zero decimals. See the Multiply parameter for an explanation of why it is recommended to use this parameter rather than Excel functionality to round the balance.
Sharing	Allows templates to be shared with all GL Wand users on the system (PUBLIC) or only available and visible to the user who loaded the template. Note: System Administrators will be able to view, edit and delete Private templates of all users.

Report Wizard Column Template

Parameter	Description
Template Name	The Name assigned to the template. Visible in the selection lists of the Quick Start Wizards and included in searches in these lists.
Template Description	A description of what the template relates to in more detail than the name. Visible in the selection lists of the Quick Start Wizards and included in searches in these lists.
Balance Type	Used in conjunction with the Accounting Period and Fiscal Year to determine the period of time to include in the Balance returned. PTD – Period to Date. All of the transactions posted in the specified period as at the date of running the report. QTD – Quarter to Date. All of the transactions posted in the quarter up to the specified period as at the date of running the report. YTD – Year to Date. All of the transactions posted in the Fiscal Year up to the specified period as at the date of running the report.
Fiscal Year & Fiscal Year Type	The value in the Fiscal Year can use one of two approaches depending on the Fiscal Year Type Field: Fiscal Year Type = Absolute: The static year (e.g. 2018) Fiscal Year Type = Relative: A dynamic value indicating the number of years to offset against the current year (e.g. -1 for prior year)
Accounting Period & Accounting Period Type	The value in the Accounting Period can use one of two approaches depending on the Accounting Period Type Field: Accounting Period Type = Absolute: The static Period (e.g. 3 to always return the 3rd period) Accounting Period Type = Relative: A dynamic value indicating the number of periods to offset against the current period (e.g. -1 for prior month)
Ledger	The Ledger as configured in PeopleSoft. The list of ledgers available for selection will be limited to those linked to the value selected in the Business Unit field.
Currency Type & Currency Code	Currency Type: Enter BASE or TRN all in upper case. Currency Code: The currency code as configured in PeopleSoft all in upper case e.g. “USD”. Examples: Currency Type: BASE and Currency: Wildcard (e.g. %) – Returns all balances for the specified parameters in the BASE currency of the Ledger. Currency Type: BASE and Currency: GBP – Returns the balance of all the transactions captured in GBP for the specified parameters in the BASE currency of the Ledger. Currency Type: TRN and Currency: GBP – Returns the balance of all transactions captured in GBP for the specified parameters in GBP. Currency Type: TRN and Currency: Wildcard (e.g. %) – Returns an error. Where BASE is used along with multiple Business Units, the defined Ledger must share the same Base Currency.
Sharing	Allows templates to be shared with all GL Wand users on the system (PUBLIC) or only available and visible to the user who loaded the template. Note: System Administrators will be able to view, edit and delete Private templates of all users.