The drill through function allows you to access the accounting system that supplies the information from the applications KTOSAL/I-KTOSAL and ICKTOSAL/I-ICKTOSAL. The prerequisite for this is that you are able to create a JDBC link to the accounting system. The function is triggered with the help of the Context/Action menu under the menu item "Drill Through".
The key values for Period, Type of Data, Company, Business Unit, Chart of Accounts and Account number of the line currently marked will be forwarded to the drill through application and displayed there in the selection area. These values can be used as selection criteria for an SQL query that has been stored. This type of SQL query is to be stored in a way that suits the respective accounting system. The JDBC connection data for every SQL query need to be assigned as well. If a correct configuration has already been stored and activated for the respective query, a password request will appear and the values that have been selected from the database of origin will be displayed, once you have registered. Pressing the button <Continue> allows you to return to the initial application.
An active, but also additional interactive configurations can be stored for every type of data. The current version includes the data types KTOSAL and ICKTOSAL for the respective applications mentioned above. Configurations for the current type of data can be managed in the drill through application via the action menu item "Drill Through Configuration". The application SQLQCONF offers an overriding configuration possibility. In any case, however, you must have access rights for the application SQLQCONF in order to be able to perform configuration.
Existing configurations will be displayed in the application SQLQCONF. New ones can be created using the "Star button", but a clear identifier has to be entered first and confirmed by pressing <Return>. JDBC connections that have already been stored can be assigned. Please use the administrative menu by pressing the button next to it that shows a "right arrow" if none of these exist or need to be changed. If it hasn’t been done yet, the type of data has to be set. Here, you will be able to select from an existing implementation group. It is thus possible to implement the parameters from the application before querying the accounting system in accordance with the requirements defined in UMSOBJ. If you would like to use a configuration for this type of data, you must activate it by using the respective button. Here, only one is allowed to be active at the same time. The previous one must be deactivated first when making a change. Configurations can be deleted by pressing the "delete button"adjacent to it.
Maintenance of the JDBC connections takes place in the same way as the SQL configurations mentioned above. Here, too, a clear identifier has to be issued first. The URL of the connection and the Java class of the driver depends on the database system of the accounting system. The database user to be used also needs to be stored.
Technical Note:
Access to the external database takes place via JDBC. The user must see to it that an appropriate JDBC driver for the respective database system is available. The path to the JDBC driver JAR file needs to be added as a value to the entry additionalLibraries= in the ini file (idlgui.ini) in the section [GENERAL] with the standard client and the single-user version. When using the KONSIS AppServer, this entry must be added to the entry wrapper.app.parameter.4= in the configuration file of the server wrapper.conf in the section "Application parameters"
A limit can be placed on the number of lines that the external database supplies. You should make use of this possibility, especially in cases where an error message indicates that not enough memory is available. The entry for this can be found in the configuration file mentioned above in the section [ENVIRONMENT] and is called DRILL_THROUGH_MAXROWS. The standard setting is on an unlimited number of lines (0).
The username is stored in the IDL.KONSIS.FORECAST database for registration, not the password, however. You must enter the password before starting a query.
An example for an accounting system on an MS SQL Server database is:
URL: jdbc:jtds:sqlserver://fasserver;DatabaseName=fasdb Driver class: net.sourceforge.jtds.jdbc.Driver Database user: accounting_user
The following parameters can be used in the SQL query and the text will be replaced by the values from the selection area before it is performed. If indicated, these values will be executed. The IDL.KONSIS.FORECAST specific value "*" in the selection area for NULL values will be replaced by a respective "null" in the SQL query. The date format for the period will always be 'DD.MM.YYYY'.
A sample query could look like this:
Select * from ACCOUNTING_TAB where COMPANY = '%GES%' and G_AREA = '%UBR%' and ACCOUNT = '%KTO%'