| |
24. User SQL and Business Score Card
1) Concept
2) User SQL
3) Adhoc Reports
4) Business Score Card
5) Business Dashboard
Goto Top
24.1 Concept
A business 'Score Card' or 'Dashboard' is a business intelligence tool by which management can quickly review a business's Key Performance Indicators (KPI) to measure how well a business is performing and to identify problem areas as they arise.
These key business indicators are usually composed of 2 elements;
a) target result
b) actual result
Although, this is not strictly necessary when reporting items like; 'Top 10 Customers'.
KPIs vary from business to business. In some business's the time taken between order receipt and final invoicing is critical, whereas for others the level of stock held may be of primary concern. In consultancy business's, Employee Usage Rates will be of major concern.
When a KPI starts to go awry, the quicker it is identified and corrected, the less damage will be done. Therefore, in a 'real-time' data application like Acceptum, having a 'Score Card' readily available to managers, enhances overall business performance and general monitoring ability. As this data can be refreshed on a minute by minute basis, the current position is always known.
A 'Score Card' is composed of numerous KPIs with each obtained from either:
a) a SQL statement interrogating Acceptum, or
b) from a computation from 2 or more values returned by such SQL statements.
The SQL statements used by 'Score Cards' are held under menu option Utilities|Systems|User SQL.
These SQL statements can draw upon any data held within Acceptum.
Although Acceptum provides a lot of standard reports and menu options to review its data; it recognises that organisations have their own requirements not met by standard solutions.
To cover this shortfall, Acceptum provides a means for users to write their own SQL Queries and use the data extracted to create their own reports or export it using the normal menu options provided.
This option provides a 'Report Writer' tool for those organisations with SQL experience.
This training will look at building 'User SQL' statements for use in Score Cards and Adhoc Reports, but it assumes that attendees know and understand the format and structure of valid SQL statements.
Goto Top
24.2 User SQL
Select menu option Utilities|Systems|User SQL to open the Score Card SQL List window.
Click the tool bar 'Add new Record' icon to insert a record and enter a 'SQL Name'.
Press F3 to open the Score Card SQL detail window to enter the SQL Query text.
On adding a record, the 'SQL Statement' is completed with a basic SQL query.
Use the 'SQL Statement Outlines' to select another template for your 'SQL Statement'.
To help construct a 'SQL Statement' use 'Tables and Views' to select a table or view. On selection of a table or view the 'Table/View and attributes' is populated.
Multiple row selection can be performed with the Ctrl key.
After selecting the required attributes, position your cursor in the 'SQL Statement' at the position you wish to insert the attribute list; or highlight existing text to be replaced.
Then click '>' to place the selected attributes into the 'SQL Statement', replacing any selected text.
Click 'Test SQL' to check that the SQL statement is valid.
When the SQL statement is not valid, an error message is received.
When the SQL statement is valid, a message is given stating the number of records selected.
SQL statements can reference specific Acceptum global variables to assist in the creation of generic statements, these are:
- ?gccomp = the currently selected company code
- ?gcfyear = the currently selected fiscal year
- ?gcactbudget = the budget that is currently set as Active for the selected company and year
- ?gcccurr = the selected companies currency code
The '?' is important and should always be entered at the start of a global variable.
When you have entered a valid SQL statement, click 'Run SQL' to open 'View Score Card SQL Result' window to view the extracted data.
This is the data result that will appear in the 'Results' tab of the 'Score Card' window when Ledger|Score Card is used.
Notice that the Columns names are the field names in the SQL.
Goto Top
24.3 Adhoc Reports
From the 'View Score Card SQL Result' window you can now select the normal Export menu options to export the data to another application for report formating or analysis.
To create a report for use in Acceptum from the 'View Score Card SQL Result' window, press F11, to open the 'User SQL Reporting' window for Print Preview.
You now have two options to create your report; 'Create Quick Report' or 'Create' report.
Create Quick Report
On clicking 'Create Quick Report', you are requested to name the report for identification and future use. Once given a name, Acceptum creates the report and places it at the end of the list of reports.
You can now run or modify the report.
Select the report and click 'Modify' to change the report layout.
You will see the SQL field names and column headings appear as a basic report, which can be modified to your own requirements. However, the format and layout of the report is poor.
Create Report
First select 'User SQL Template' report in the list of reports; then click 'Create'. You are requested to enter a unique name for the report. Once given a name, Acceptum creates the report and places it in the list of reports.
Unlike 'Create Quick Report' you cannot immediately run the report; you must 'Modify' it first.
On clicking 'Modify', you will see standard Acceptum report layout. You must now change the data source of the few fields included and add additional fields; modifying the report to your own requirements.
The advantage of the 'Template Report' is that the Fonts, Layout, etc. are in standard format.
The disadvantage is that you have to add/change SQL report fields.
Use whichever method suits your ability and requirements.
Once a report has been named, it can only be changed by altering the file names in the Reports folder and changing the entry under 'User SQL' in Utilities|Systems|Report Lists. Use this latter menu option to change the ordering of reports in the given list of reports.
Goto Top
24.4 Business Score Card
Select menu option Ledger|Score Card to open the Score Card List window.
Click the tool bar 'Add new Record' icon to insert a record and enter an 'Id'.
Press F3 to open the Score Card detail window and enter descriptive 'Text'.
From the drop-down list, select the 'SQL Name' to be used.
Enter a mathematical expression using Score Card row 'Ids', constants and mathematical operators to obtained computed values. As an example the Variance between Budget and Actual Revenue would be '[ACTREV] - [BUDREV]'. With ACTREV being the 'Id' for Actual Revenue and BUDREV the 'Id' for Budget Revenue. The '[' and ']' MUST be placed around 'Ids' to identify them as such.
Click 'Extract Results' to update 'Results' for each 'Id'. The order of processing is that all SQL statements are executed first, then all the Row Formula are calculated in ascending row 'Id' order.
Click 'Notes' tab to enter user notes to describe the KPI and explain the latest result.
Use Print F12 option to produce a hardcopy of the Score Card for senior management review.
Do NOT enter 'SQL Name' and 'Row Formula' on the same 'Id' record.
Use Graph Row text when the data is to be used in a multi-column graph in the Dashboard. The data to appear on the same 'row' must bear the same 'Row text' to work correctly.
Goto Top
24.5 Business Dashboard
Select menu option Ledger | Dashboard to open the Dashboard window. Any previously established graphs will be displayed. The data reflected by the graphs can be updated at any time by clicking 'Extract Results'.
The format and data supporting a graph can be seen by double clicking it.
The graphs used here are very similar to those used in MS Excel and Word. To print a graph just double click it and then copy it to the Clipboeard with Ctl+C and paste it into Excel/Word with Ctl+V, ready to be printed from those programs.
Six graphs can be built for a user. Each user has their own graphs in order for them to monitor the Key Performance Indicators that are important for their management role.
The data for the graphs is retrieved from the Score Card data records, which in turn are built from the User SQL statements that extract information from the database. Prudent setting of the SQL and Score Card data may be required to eliminate negative values.
Click 'Build Graphs' to show the attributes that support the creation of a graph.
First set the 'Graph No', from 1 to 6.
Then set the title, graph type, sub type, and the column 1/2/3 labels and data sources.
When setting 2 or more columns, ensure that each column has the same number of selected data sources.
Having set the graph attributes - click SAVE. This MUST be done for each Graph No used.
Delete will remove the currently selected Graph No.
Cancel will return to showing the graphs, without extracting new data.
Show Graphs will return to showing the graphs, extracting new data in the process.
A data source like Top 3 Customers will only work when the extracted data is in the format of:
Description '|' Amount
Desription being the name of the customer/supplier.
'|' being a field separator.
Amount being the extract value to be used in the graph.
Practise the graph types with data summaries under Excel when having difficulty establishing settings.
| |