SunSystems 5 Administration Session 10 – Query Manager
35. Tasks in DAM
35.7. Data Access Manager
Data Access Manager is used to configure user definable queries.
There are two sections for this:
• Context
! Defines the basis of the inquiry
− i.e. Ledger Line or Purchase Order Line or Sales Order Line.
! Defines actions that can be run if this context is selected
− i.e. Ledger Line (Account Allocation, Split Posting, Print Account Documents, etc) Sales Order Line (Generate Sales Invoice/Credits, De-allocate Sales Order Lines etc)
• Query Definition
! Defines the Context that will be used for this query
! Defines the Selection Criteria and how selections will work
! Defines sorting and grouping
! Defines subtotalling and number of decimal places for amount values
To run Data Access Manager go to Start, Programs, Systems Union, Query Manager, Data Access Manager
You will then be asked to log in to Data Access Manager:
Once you have logged into Data Access Manager the following window will be displayed:
Query definitions are split into three areas within the Task panel:
• Actions
• Contexts
• Query Definitions
35.7.1. Actions
Actions are the reports and processes that can be run from within a query.
Systems Union have supplied all the standard actions you need (that are currently supported) therefore you should not need to create or amend an action.
Actions may be associated with one or more context.
Using the actions option, you can:
• Delete an action (this is a server option, not a client option)
• Rename an Action
• Create a new action (linked to a report or cobol program – third party developers)
• Amend an Action
If you right click on an action the system will display the properties associated with the action.
You cannot change a base object for an action that is used by an existing context.
To change an existing base object you must first remove it from any context that it is associated with. For a full list of base objects please see the next section on creating a new object.
To amend an action double click the action that you wish to change and the following window will be displayed:
Change the details as required and click OK.
Creating an Action
To create a new action, double click actions
The following window will then be displayed:
The create action window will then be displayed:
Insert Action Name and Description and Program to run (the file should be located in the SunSystems directory).
Clicking refresh after execution will ensure that data that is redisplayed after an action shows the latest values (but is dependent on the initial selection criteria).
Please refer to the following examples:
Example One
A query definition for Account Allocation is run.
The selection criteria is:
Account Range Period Range
Transaction Date Range
Allocation Marker = Not Allocated
Transactions are selected and allocated. Those transactions that have been allocated will not be redisplayed on the output results form as they are no longer
‘Not Allocated’
Example Two
A query definition for Account Allocation is run.
The selection criteria is:
Account Range Period Range
Transaction Date Range
Transactions are selected and allocated. Those transactions that have been allocated will be redisplayed.
Select the base object that you wish to base this action on using the button
You can select base objects from either the hierarchy or from the total list. In the example below we have selected from hierarchy.
The most frequently used base objects are listed below against the function they will be used with:
Base Object Typical Actions
Financial Inquiry Ledger Line No Action
Account Allocation Ledger Line • Account Allocation
• Split Transaction Generate Payments Ledger Line
Sales Order Inquiry Sales Order Line No Action
Sales Order Picking Sales Order Line Generate and Print Sales Picking List
Sales Order Despatch Sales Order Line Generate and Print Sales Dispatch Note
Sales Order Invoicing Sales Order Line Generate and Print Sales Invoice Purchase Order Inquiry Purchase Order Line No Action
Purchase Order Print Purchase Order Line Print Purchase Orders Movement Order
Inquiry
Movement Order Line No Action
Enter the Program to Run. This is the Cobol program name in SunSystems of the program that needs to run this action. You cannot search for this program name therefore you will need to know that name to enter it.
Your action will now be created.
Deleting Actions
Actions can be deleted although it is not advisable as all actions that may need to be used have been created.
You cannot delete an action that is already in use i.e. is used by a context/query definition.
To delete an action:
Double click Actions
Select the action you wish to delete and select the Delete button.
35.7.2. Creating a Context
The Context defines the function that the inquiry or processing will be used for and the Actions that will be available when a user selects the Query Definition/Context.
A single Context can be used for several different Query Definitions.
In this example the Context that has been configured is called ‘Finance Transaction 1’
A Context can either be Common (available for all Business Units) or created specifically for a Business Unit.
To create a common context double click on Common Contexts and the following window will be displayed:
Click New
To create a business unit specific context double click on Contexts for Business Unit ‘XXX’ and select New.
The Create Context window will then be displayed:
Select the required base object.
A Context can have only one Base Object.
Select Actions
The base object that you select will determine the actions that are available on the Select Actions form. Queries that will be used simply for inquiry purposes do not require actions. Queries that are used to select transactions for further processing will have actions attached to them. Some examples of query definitions that use actions are shown in the table below:
Query Definition
Context Base Object Action
Account
Sales Order Line • De-allocate Sales Order Sales Picking Sales Picking Sales Order Line • Generate and
Print Sales Picking List
The system will display a summary of your selections
Some Actions allow the user to go from selection criteria to action processing directly, bypassing the output results form. Many of the Order Fulfilment processes such as Print Purchase Orders, Print Picking Lists, Print Dispatch Notes and Print Sales Invoices are examples of where straight through processing would be used.
Click Finish
The Context you have just created will then be displayed in the list
35.7.3. Query Definitions
A Query Definition can either be Common (available for all Business Units) or created specifically for a Business Unit.
To create a common Query Definition double click on Common Query Definitions and the following window will be displayed:
Configuring a Query Definition
• Query Definition
! Defines the Context that will be used for this query
! Defines the Selection Criteria and how selections will work
! Defines sorting and grouping
! Defines subtotalling and number of decimal places for amount values.
This session demonstrates how to configure a Query Definition that uses the context created in the previous session ‘Finance Transaction 1’. The selection criteria is:
Is Between Not Specified Not Specified Y Transaction
Date
Is Between Not Specified Not Specified Y
Once you have selected New, the Create Query Definition window will be displayed:
The following window will then be displayed:
Data Hierarchy Selection Pane Selection Edit Pane
Selection criteria is added by dragging a data item and dropping it onto the Query Definition Name
The editable operator values are shown in the selection edit pane The default ‘is equal to’ but it can be edited by clicking on it:
The options are:
is equal to is greater than is less than is not equal to is not greater than is not less than is between includes starts with
In the example above the range ‘is between’ has been selected. This offers a from and to selection. Default values can be entered for the from and to selections by clicking on the fields.
The default value is entered here and you can click the Allow Override option to allow users to amend selection at runtime. Ensure you click the Green Tick to save selections.
Once you have completed your selection criteria the statement is displayed.
Additional selection criteria can then be made. Once you have finished your selection statement click next.
Selected Output Fields
Once the selection criteria has been executed, selected data will be passed to a display form. The Selected Output Fields form is used to define the data fields and the order in which they will be displayed. Output is always presented in grid format.
Data Hierarchy Output Results Selection Pane Choose the field that you wish to display from the Data Hierarchy and click the right arrow to move the selection into the Output Results Selection Pane.
Continue to make selections until you have added all of the data fields required.
You can use the Up and Down buttons to promote or demote previous selections.
Once your output results have been completed select Next.
The Sorting and Grouping options will then be displayed:
The columns can be sorted in ascending order by clicking on the column, or sorted by descending order by clicking on the column again. Users can change the sort order when they run the query
By dragging column headings up to the group area, default grouping can be specified for the query when it is run. Users can ungroup or change grouping when they run the query.
Formatting Numbers
If you have selected output fields that are numeric, the format numbers form will display those fields and allow you to:
Define the number of decimal places (minimum 0, maximum 9) (Default is 2)
Define if negative values are shown in brackets (Default is unticked) Determine if subtotal are supplied for this value (Default is ticked)
Once the number formatting has been completed click Next.
Confirm Your Choices
A summary of your selections for your Query Definition are then displayed:
Select Finish to complete Query Definition or back to amend configuration.
Your Query Definition will then be complete.
Your query definition will then be displayed in the Task pane:
Remember to save changes to the application server before you exit from Data Access Manager or your Context/Query Definition will not be saved.
Once you have created a context you can run it from Navigation Manager (in PK1 the shortcut is QM) Query Manager allows you to select available queries from a list (per the earlier demonstration.) You can also use Navigation Manager to run individual queries as menu options.