My COINS BI Full User Guide
COINS%20BI%20Full%20User%20Guide
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 600 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- 1Contents
- 2Database Overview
- 3OA Query Language
- 4OA Calculation Syntax
- 5OA and BI Utilities
- 6End User Reporting
- 6.1OA Report Writer
- 6.1.1Key features
- 6.1.2Key Functionality:
- 6.1.3Granting Access to Report Writer and Report Runner
- 6.1.4Report Data Sources
- 6.1.5 Report Data Source Definitions
- 6.1.6Report Writer - Creating a New Report
- 6.1.7Selecting the fields to print on the report
- 6.1.8Sorting, Sub-totalling, Page Breaks and Summary Levels
- 6.1.9Previewing or exporting the data
- 6.1.10Running a Report
- 6.1.11Publishing Report Writer Reports
- 6.1.12Read Only (Calculated) Fields
- 6.2Semantic Layer Exercises - Overview
- 6.2.1Exercise - A Simple Listing Report
- 6.2.2Using the Dataset in a Report Writer Report
- 6.2.3Using the Dataset in an OA Designer Report
- 6.2.4Exercise – Adding Calculated Fields to a Collection
- 6.2.5Exercise - Adding Fields to a Collection Table
- 6.2.6Exercise – Adding fields to a Collection
- 6.2.7Exercise - Summaries
- 6.3Report Builder
- 6.3.1Initial Setup
- 6.3.2Report Builder
- 6.3.2.1Canvas
- 6.3.2.2Button and Options Bar
- 6.3.2.3Opening an Existing Report
- 6.3.2.4Creating a New Report
- 6.3.2.5Data Source
- 6.3.2.6Adding Fields
- 6.3.2.7Groups
- 6.3.2.8Filters
- 6.3.2.9Run Report
- 6.3.2.10Refining the Report - Fields
- 6.3.2.11Field Positioning
- 6.3.2.12Text,Count,Calculations
- 6.3.2.13Dataset Field Update
- 6.3.2.14Adding Fields to Datasets
- 6.3.2.15Report Builder List
- 6.4Dynamic Queries
- 6.1OA Report Writer
- 7OA Designer - Overview
- 7.1Designer key features
- 7.2Functions and Sections
- 7.3Function Naming
- 7.4Function Parameters
- 7.5URL Parameters
- 7.6Forms Service Procedures and Report Selection Generates
- 7.7Page Designer
- 7.7.1Page Header
- 7.7.2Page Header Tab
- 7.7.3Page Body Tab
- 7.7.4Page Script Tab
- 7.7.5Page Forms Tab
- 7.7.5.1Adding a Page Form
- 7.7.5.2Page Forms - Add
- 7.7.5.3Page Forms - Body
- 7.7.5.4Page Forms - Body Detail
- 7.7.5.5Page Forms - Body Selector
- 7.7.5.6Page Forms - Body Span
- 7.7.5.7Page Forms - Body Update
- 7.7.5.8Page Forms - Context
- 7.7.5.9Page Forms - Detail
- 7.7.5.10Page Forms - Footer
- 7.7.5.11Page Forms - Header
- 7.7.5.12Page Forms - Multiple Update
- 7.7.5.13Page Forms - Record Header
- 7.7.5.14Page Forms - Totals
- 7.7.5.15Page Forms - Update
- 7.7.6Page Fields Tab
- 7.7.7Named Filters
- 7.7.8Browse Filters
- 7.8Report Designer
- 7.9Data Display Colour Ranges
- 7.10Alternate Line Shading on Forms and Reports
- 7.11Queries on Purchase Orders
- 7.12Calculations Overview
- 7.13Datasets - Overview
- 8Workflow Overview
- 8.1Business Benefits
- 8.2The COINS Workflow toolkit
- 8.3Example Workflow
- 8.4Workflow Groups
- 8.5Workflow Roles
- 8.6Workflow Responsibilities
- 8.7User Workflow Responsibilities
- 8.8Workflow Delegation
- 8.9Workflow Templates
- 8.10Workflow Deployment to Companies
- 8.11Workflow Monitor
- 8.12Workflow Summary
- 8.13Workflow User Variables
- 8.14Workflow Parameters (System Parameters)
- 8.15Database Triggers
- 8.1614 Import/Export Workflow Template
- 8.1715 Launching Workflow
- 8.18Using a Choose Action
- 8.1917 Method to force the closure of Workflow:
- 9Introduction or Executive Summary
- 10Building COINS Desktops - Overview
- 10.1Desktop Structure
- 10.1.1Creating the Functions (Manually)
- 10.1.2Add the Desktop to a user profile
- 10.1.3Desktop Section Functions
- 10.1.4Uploading Desktop Images
- 10.1.5Tile Rows and Columns
- 10.1.6Add the Desktop to a user profile
- 10.1.7Alternative Desktops
- 10.1.8Desktop Import/Export
- 10.1.9Smart Tiles
- 10.1.10Info Tiles
- 10.1.11Using Calculation Programs to prepare data for the Desktop
- 10.1.12Desktop Tile Values
- 10.1.13Tile Options
- 10.1.14Tables on Tiles
- 10.1.15Charts on Tiles
- 10.1Desktop Structure
31-May-2017 Construction Industry Solutions Page 1 of 600
COINS BI Toolset User Guide
Prepared by: Documentation Team
Prepared for: Learning Resources
Module: Business Intelligence
Date: 2016
Document Ref:
Version: xx.xx
Construction Industry Solutions Ltd.
11 St. Laurence Way
SL1 2EA
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 2 of 600
Copyright 2016 Construction Industry Solutions Ltd.. All rights reserved.
Information in this document is subject to change without notice. The software
described in this document is furnished under a license agreement or nondisclosure
agreement. The software may be used or copied only in accordance with the terms of
those agreements. No part of this publication may be reproduced, stored in a retrieval
system, or transmitted in any form or any means electronic or mechanical, including
photocopying and recording for any purpose other than the purchaser's personal use
without the written permission of Construction Industry Solutions Ltd..
Construction Industry Solutions Ltd.
11 St. Laurence Way
SL1 2EA
THIS USER GUIDE WAS CREATED USING MADCAP FLARE.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 3 of 600
1 Contents
1 Contents 3
2 Database Overview 21
2.1 Relational Database Model 22
2.2 Tables and Modules 23
2.2.1 Database Structure 24
2.2.2 Company Specific Modules 25
2.2.3 Process Specific Modules : 25
2.3 Summary and Detail Tables 27
2.4 Open Items 28
2.5 Indexes 29
2.6 Record Service Procedures 30
2.7 Read Only Fields 33
2.7.1 Exposing RO Fields 35
2.8 Create and Maintain Lookups 37
3 OA Query Language 39
3.1 FOR EACH 40
3.2 WHERE 41
3.3 Joining Tables 43
3.3.1 EACH 43
3.3.2 FIRST 43
3.3.3 LAST 43
3.3.4 OF 44
3.4 Curly Braces 45
3.5 Outer-Join 46
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 4 of 600
3.6 Buffers 48
3.7 Calculation Programs 49
3.7.1 Creating Calculation Programs 49
3.7.2 Calling Calculation Programs 49
4 OA Calculation Syntax 51
4.1 Finding the Period Number from a Date 52
4.2 syuquery aggregate 53
4.3 Performing Calculations on Numerical Values 54
4.3.1 A Simple Count 55
4.3.2 String$ 56
4.3.3 IF 57
4.3.4 RANGE 60
4.3.5 Limit 62
4.3.6 Max 63
4.3.7 Min 64
4.3.8 Sum 65
4.3.9 non-zero 65
4.3.10entry 66
4.3.11truncate 67
4.3.12round 68
4.4 String Calculations 69
4.4.1 INTEGER 70
4.4.2 DECIMAL 71
4.4.3 INLIST 72
4.4.4 CAN-DO 73
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 5 of 600
4.4.5 MASK 74
4.4.6 ENTRY$ 75
4.4.7 RIGHT$ 76
4.4.8 LEFT$ 77
4.4.9 SUBSTRING$ 78
4.4.10LENGTH$ 79
4.4.11REPLACE$ 80
4.4.12CAPS$ 81
4.4.13TRIM$ 82
4.4.14RIGHT-TRIM$ 84
4.4.15INDEX 85
4.4.16R-INDEX 86
4.4.17NUM-ENTRIES 87
4.4.18LOOKUP 88
4.5 Time & Date Calculations 89
4.5.1 date 90
4.5.2 datestring 91
4.5.3 weekday 92
4.5.4 day 93
4.5.5 month 94
4.5.6 year 95
4.5.7 time 96
4.5.8 Weekdays 97
5 OA and BI Utilities 98
5.1 Database Enquiry 99
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 6 of 600
5.2 Access to the Database Enquiry and Tables 101
5.3 Query Editor 103
5.3.1 Search and Replace 106
5.3.2 Exporting from the Query Editor 108
5.3.3 Saving Queries in Query Editor (10.27) 108
5.3.4 Creating Datasets from the Query Editor 110
5.4 Calculation Editor 111
5.5 Object Enquiry 112
6 End User Reporting 114
6.1 OA Report Writer 115
6.1.1 Key features 115
6.1.2 Key Functionality: 115
6.1.3 Granting Access to Report Writer and Report Runner 116
6.1.3.1 Function Security 117
Report Runner 118
6.1.4 Report Data Sources 120
6.1.5 Report Data Source Definitions 121
6.1.6 Report Writer - Creating a New Report 123
6.1.7 Selecting the fields to print on the report 127
6.1.7.1 New Reports 127
6.1.8 Sorting, Sub-totalling, Page Breaks and Summary Levels 127
6.1.9 Previewing or exporting the data 128
6.1.10Running a Report 128
6.1.11Publishing Report Writer Reports 130
6.1.11.1Publishing a report to Report Runner 131
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 7 of 600
6.1.11.2Convert to Function 132
Amending Reports that have been converted to Functions 132
6.1.11.3Convert to Report Designer 134
Amending Reports that have been converted to OA Designer 135
6.1.12Read Only (Calculated) Fields 136
6.1.12.1Using Parameter driven RO_ fields in Report Writer 137
6.1.12.2Exposing RO Fields 138
6.1.12.3Using RO Fields directly in a calculation 140
6.2 Semantic Layer Exercises - Overview 141
6.2.1 Exercise - A Simple Listing Report 141
6.2.1.1 Build the Data Set 141
6.2.1.2 Test the Dataset 144
6.2.1.3 Make the Dataset available to the Reporting Tools 146
6.2.2 Using the Dataset in a Report Writer Report 148
6.2.3 Using the Dataset in an OA Designer Report 151
6.2.4 Exercise – Adding Calculated Fields to a Collection 155
6.2.4.1 Add the fields 155
6.2.4.2 Add the fields to the Dataset 159
6.2.4.3 Test the Dataset 161
6.2.5 Exercise - Adding Fields to a Collection Table 162
6.2.5.1 Add a new Token 163
6.2.6 Exercise – Adding fields to a Collection 168
6.2.6.1 Add the fields 168
6.2.6.2 Add the fields to the Dataset 172
6.2.6.3 Test the Dataset 173
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 8 of 600
6.2.7 Exercise - Summaries 175
6.2.7.1 Using the summary data in a report 179
6.3 Report Builder 180
6.3.1 Initial Setup 181
6.3.1.1 Browser Version 181
6.3.1.2 Report Builder System Parameters 182
6.3.1.3 Report Data Sources – Pre-Built Datasets 183
6.3.1.4 Security 184
6.3.1.5 Query Results Licence 185
6.3.2 Report Builder 186
6.3.2.1 Canvas 187
6.3.2.2 Button and Options Bar 188
Report Actions 188
Field Formatting 188
Canvas View 189
Actions 189
Run 189
6.3.2.3 Opening an Existing Report 191
6.3.2.4 Creating a New Report 192
Fields 192
6.3.2.5 Data Source 194
Existing Data Source 194
Custom Data Source 195
Data Frame 196
6.3.2.6 Adding Fields 198
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 9 of 600
6.3.2.7 Groups 202
Fields 202
6.3.2.8 Filters 204
6.3.2.9 Run Report 207
6.3.2.10Refining the Report - Fields 209
6.3.2.11Field Positioning 212
6.3.2.12Text,Count,Calculations 213
6.3.2.13Dataset Field Update 215
6.3.2.14Adding Fields to Datasets 216
6.3.2.15Report Builder List 219
Export Report Builder Definition 220
Import Report Builder Definition 222
6.4 Dynamic Queries 224
6.4.1 Dynamic Query Definition 225
6.4.2 Dynamic Enquiry 228
6.4.3 Report 231
6.4.4 Dynamic Grouping 235
6.4.5 Report Builder 236
7 OA Designer - Overview 239
7.1 Designer key features 239
7.2 Functions and Sections 240
7.3 Function Naming 241
7.4 Function Parameters 242
7.4.1 stn_code 243
7.4.2 rtn_code 244
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 10 of 600
7.4.3 Exptype 245
7.4.4 noInfo 246
7.4.5 ExportType 247
7.4.6 Sidemenuhelp 248
7.4.7 Passing Variables 249
7.5 URL Parameters 250
7.5.1 Helpmode 251
7.6 Forms Service Procedures and Report Selection Generates 252
7.6.1 Bill of Quantities - BQFREP 253
7.6.2 Cash Book - CBFREP 254
7.6.3 Central Repository - CIFREP 255
7.6.4 Company - COFREP 256
7.6.5 Credit Control - CNNFREP 257
7.6.6 Contract Sales - CSFREP 258
7.6.7 Fixed Assets - FAFREP 259
7.6.8 Facilities Management - FMFREP 260
7.6.9 General Ledger - GLFREP 261
7.7 Page Designer 262
7.7.1 Page Header 263
7.7.2 Page Header Tab 264
7.7.3 Page Body Tab 267
7.7.4 Page Script Tab 269
7.7.5 Page Forms Tab 270
7.7.5.1 Adding a Page Form 271
7.7.5.2 Page Forms - Add 274
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 11 of 600
7.7.5.3 Page Forms - Body 275
7.7.5.4 Page Forms - Body Detail 276
7.7.5.5 Page Forms - Body Selector 277
Body Selector Example 277
Populating body selectors from generic lookup tables 278
Syntax 279
7.7.5.6 Page Forms - Body Span 281
7.7.5.7 Page Forms - Body Update 282
7.7.5.8 Page Forms - Context 283
7.7.5.9 Page Forms - Detail 284
7.7.5.10Page Forms - Footer 285
7.7.5.11Page Forms - Header 286
7.7.5.12Page Forms - Multiple Update 287
7.7.5.13Page Forms - Record Header 288
7.7.5.14Page Forms - Totals 289
7.7.5.15Page Forms - Update 290
7.7.6 Page Fields Tab 291
7.7.6.1 Page Fields - View As Options 298
blank 298
Blank When Zero 298
Checkbox 298
Code with tooltip 298
Combo 298
Disabled Field 299
Disabled with Lookup 299
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 12 of 600
Editor 300
Email 300
File 300
Grid Element 300
Inline Frame 300
Link 301
List Frame 301
Multiple Selection 301
No Break 302
Ordered List 302
Password 303
Picture 303
Preformatted 304
Radio Set 304
Selection 304
Sorting Combos, Selection List, Multi Selects and Ordered Lists 305
7.7.7 Named Filters 306
7.7.7.1 Replacing body query 306
7.7.7.2 Linked Named Filters 306
7.7.7.3 Multiple Named Filters 306
7.7.7.4 Named Filter Query Conditions 307
7.7.7.5 Using Named Filters to define what fields are built on a form 307
Parameters: 307
7.7.8 Browse Filters 308
7.7.8.1 Parameters: 308
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 13 of 600
7.8 Report Designer 309
7.8.1 Key Features 309
7.8.2 Page Summary 310
7.8.3 Report Designer_Using RS Fields 311
7.8.4 Report Designer_Report Forms 312
7.8.5 Headers and Footers 313
7.8.6 Default Report Labels 314
7.9 Data Display Colour Ranges 315
7.10Alternate Line Shading on Forms and Reports 316
7.10.1Prerequisites 317
7.10.2Task Summary 318
7.10.3Create report field styles 319
7.10.4Applying to forms 321
7.10.5Applying to reports 325
7.11Queries on Purchase Orders 329
7.11.1po_line v po_item 329
7.12Calculations Overview 331
7.12.1Mathmatical Functions 333
7.12.2Calculation Field Rules 334
7.12.3Variables 335
7.12.4Debug 337
7.12.5Curly Braces 339
7.13Datasets - Overview 340
7.13.1Creating a Data Set 341
7.13.1.1Data Set Fields 342
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 14 of 600
7.13.2Best Practice 344
7.13.3Granting Access to Data Sets for Other Users. 346
7.13.4Rules for Keys 347
7.13.5Cross Modular Reporting 348
7.13.6Using Data Sets in the Query Editor 349
7.13.7Using the Data Set in Queries 350
7.13.7.1In-Line Reports 351
7.13.8Report Pre-Processing (Syuds) 353
7.13.8.1Syuds.Calc 354
syuds.calc example 354
7.13.8.2Syuds.Debug 356
7.13.8.3Syuds.Delete 358
7.13.8.4Syuds.Filter 359
7.13.8.5Syuds.GroupQuery 360
7.13.8.6Syuds.Merge 363
7.13.8.7Syuds.Pivot 365
Report Design 367
Sample Output 370
7.13.8.8Repeat 372
7.13.8.9Syuds.Store 373
7.13.8.10Sum 374
7.13.8.11TableAlias 376
7.13.8.12Syuds.TimeSlice() 377
7.13.8.13Top 379
7.13.8.14Union 380
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 15 of 600
8 Workflow Overview 382
8.1 Business Benefits 382
8.2 The COINS Workflow toolkit 383
8.3 Example Workflow 384
8.3.1 384
8.4 Workflow Groups 385
8.5 Workflow Roles 386
8.5.1 Example Roles 387
8.6 Workflow Responsibilities 388
8.6.1 Example Responsibilities 388
8.7 User Workflow Responsibilities 390
8.8 Workflow Delegation 392
8.8.1 Self-Service Workflow Delegation 394
8.9 Workflow Templates 395
8.9.1 WF Template - Details Tab 396
8.9.2 WF Template - Stages Tab 399
8.9.2.1 WF Stages - Appointment 402
8.9.2.2 WF Stages - Delay 405
8.9.2.3 WF Stages - Email 407
8.9.2.4 WF Stages - Fork 409
8.9.2.5 WF Stages - Merge 411
8.9.2.6 WF Stages - Process 413
8.9.2.7 WF Stages - SMS 415
8.9.2.8 WF Stages - Stage 417
8.9.2.9 WF Stages - Task 423
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 16 of 600
8.9.3 WF Template - Diagram Tab 426
8.9.4 WF Template - Check Tab 435
8.9.5 WF Template - Paths Tab 436
8.9.6 WF Template - Active Tab 437
8.9.7 WF Template - Complete Tab 438
8.9.8 WF Template - Outstanding Tab 439
8.10Workflow Deployment to Companies 441
8.11Workflow Monitor 443
8.11.1Workflow Monitor Workbench 445
8.11.1.1WF Monitor - Monitor Tab 446
Workflow Monitor diagram 446
8.11.1.2WF Monitor - Active Tab 448
8.11.1.3WF Monitor - Completed Tab 449
Completed Tab 449
8.12Workflow Summary 450
8.12.1WF Summary - Tasks Tab 451
8.12.2WF Summary - Stages Tab 452
8.12.3WF Summary - History Tab 453
8.12.4WF Summary - Variables Tab 454
8.13Workflow User Variables 455
8.14Workflow Parameters (System Parameters) 456
8.15Database Triggers 457
8.1614 Import/Export Workflow Template 460
8.1715 Launching Workflow 461
8.18Using a Choose Action 462
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 17 of 600
8.1917 Method to force the closure of Workflow: 463
9 Introduction or Executive Summary 464
9.1 soapUI 465
9.2 Licencing 466
9.2.1 Additional Licencing 466
9.2.2 Roles 469
9.2.3 User Maintenance 471
9.3 Viewing Installed Services 472
9.4 Testing a Service Connection (Using soapUI) 476
9.5 ESB COINS User 487
9.6 User Defined Services 489
9.6.1 Datasets 490
9.6.1.1 Function 490
9.6.1.2 Dataset 490
9.6.1.3 Page 492
9.6.1.4 Adding the Function to the Web services Menu 493
9.6.1.5 Testing the Service 499
9.6.2 Pages 503
9.6.2.1 Function 503
9.6.2.2 Page 504
Body Form 506
Update Form 506
Detail Form 507
9.6.2.3 Adding the Function to the Web services Menu 508
9.6.2.4 Testing the Service 513
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 18 of 600
Using the ID field when adding multiple records 516
9.6.3 Calculation Programs 518
9.6.3.1 Defining the Calculation 518
9.6.3.2 Testing Calculation Programs (run, run$) 520
9.6.3.3 Using Calculation Programs with Web Services 523
Create the Function 523
Create the Page 524
Adding the Function to the Web services Menu 526
9.6.3.4 Testing the Service 531
9.6.4 Get/Set 534
10Building COINS Desktops - Overview 540
10.1Desktop Structure 541
10.1.1Creating the Functions (Manually) 543
10.1.1.1Desktop Functions 543
10.1.1.2Desktop Tab Functions 543
10.1.2Add the Desktop to a user profile 544
10.1.3Desktop Section Functions 547
10.1.4Uploading Desktop Images 550
10.1.5Tile Rows and Columns 552
10.1.5.1Desktop – Tile Rows 553
10.1.5.2Desktop – Tile Columns 554
10.1.6Add the Desktop to a user profile 555
10.1.7Alternative Desktops 558
10.1.7.1User Maintenance 558
10.1.8Desktop Import/Export 560
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 19 of 600
10.1.8.1User Maintenance 560
10.1.8.2Export 560
10.1.8.3Import 563
10.1.9Smart Tiles 565
10.1.10Info Tiles 568
10.1.11Using Calculation Programs to prepare data for the Desktop 572
10.1.12Desktop Tile Values 575
10.1.12.1Examples of Desktop Tile Values 575
10.1.13Tile Options 579
10.1.13.1NamedFilter Example 581
Desktop Tile Options 581
Desktop 582
10.1.13.2InitContainer Example 585
Desktop Tile Options 585
Desktop 585
10.1.14Tables on Tiles 587
10.1.14.1Tables : Example 1 – Direct Query against the database 588
Create the Function 588
Create the Page 588
Desktop 589
10.1.14.2Example 2 – Using a Data Set 590
Create the Function 590
Create the Dataset 590
Desktop 592
10.1.15Charts on Tiles 593
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 21 of 600
2 Database Overview
A database is a collection of records stored in a computer in a systematic way, such that a
computer program can consult it to answer questions. For better retrieval and sorting, each
record is usually organized as a set of data elements (facts). The items retrieved in answer to
queries become information that can be used to make decisions. The computer program used
to manage and query a database is known as a database management system (DBMS). The
properties and design of database systems are included in the study of information science.
The central concept of a database is that of a collection of records, or pieces of knowledge.
Typically, for a given database, there is a structural description of the type of facts held in
that database: this description is known as a schema. (You can view the coins schema using
the database enquiry screen in coins OA – see accompaying documentation).
The schema describes the objects that are represented in the database, and the relationships
among them. There are a number of different ways of organizing a schema, that is, of
modelling the database structure: these are known as database models (or data models).
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 22 of 600
2.1 Relational Database Model
The model in most common use today is the relational model, which represents all
information in the form of multiple related tables each consisting of rows and columns.
A relational database is a database based on the relational model. Strictly speaking the term
refers to a specific collection of data but it is invariably employed together with the software
used to manage that collection of data. That software is more correctly called a relational
database management system, or RDBMS.
An important feature of relational systems is that a single database can be spread across
several tables. This differs from flat-file databases, in which each database is self-contained in
a single table.
On Relational Database Model represents relationships by the use of values common to
more than one table
In the relational model some bit of information was used as a "key", uniquely defining a
particular record. When information was being collected about a user, information stored in
the optional (or related) tables would be found by searching for this key. For instance, if the
login name of a user is unique, addresses and phone numbers for that user would be
recorded with the login name as its key.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 23 of 600
2.2 Tables and Modules
The table structure of the coins database has been designed to be organized with a direct
relation to the business processes and modules of the system. One of the main attributes of
this design is the naming convention used on the schema.
Coins have aimed to use a naming convention that would make it easy to identify which
tables are used by which module. On most cases the first two letters of the table will point to
the module code of the system.
Examples:
ap_ Purchase Ledger
ar_ Sales Ledger
cb_ Cash Book
ci_ Central Repository
The above is the standard convention but there are tables in the database which do not
conform to this convention of which users should be aware – main examples are payroll and
all system information which either do not use underscrores but use hyphens or do not break
the table names. The Database Enquiry contains all the information as required
The table name will also contain a descriptive element, for example ap_invoice is the
Purchase Ledger invoice table and ap_invdist the table which contains its associated
distribution records.
Each table has a three letter ID, this is used as reference throughout coins – and is often
used as the prefix of a field name. In the example of ap_invoice this ID is ain, therefore the
field name for the Purchase Ledger Invoice balance is ain_balance.
To reference a field the syntax is:
{tablename}.{fieldname}
For example :
ap_invoice.ain_balance
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 24 of 600
2.2.1 Database Structure
The coins database is based on various levels, the top level being the Central Repository.
Information held in the Central Repository is not COINS Company specific and is available
across the system.
The main pieces of information held in the Central Repository are :
CI Company Information
PI Project Information
TI Technical Information
PP People Information
In addition to the Central Repository, system information such as Users, Functions, Printers
are also held at this top level.
SY System
MS Menus and Functions
PM Print Manager
XL Translations and Language
IB Insurances and Bonds
MK Marketing
Most data in the Coins database is actually held at COINS Company level. Even though only
one company may be used the company details will need to be used to access the data. In
each instance the company number is held on each table in the kco (current logged in
company) field.
There are a set of tables which relate directy to company information – configuration table
etc, in addition to generic tables such as Batches – these are held in the co module.
CO Company
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 25 of 600
2.2.2 Company Specific Modules
GL General Ledger
JC Contract Status Ledger (Job Costing)
CB Cash Book
AP Purchase Ledger (Accounts Payable)
AR Sales Ledger (Accounts Receivable)
SC SubContract Ledger
2.2.3 Process Specific Modules :
CS Contract Sales
FM Facilities Management
SM Valuations (Site Manager)
SW Small Works
House Builders Modules
BQ Bill of Quanties
HS House Sales
LA Land Appraisal
VP Valuations and Payments
WF Workflow
Payroll and HR
CR Credit Control
HR Human Resources
EX Expenses
PR Payroll
Plant, Assets and Stock
CM Components
FA Fixed Assets
FL Fleet
PC Plant Control
SO Sales Orders
ST Stock
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 26 of 600
Procurement
PO Procurement
Other Modules :
BP Professional Billing
CR Credit Control
DC/DM Document Control / Management
IB Insurance and Bonds
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 27 of 600
2.3 Summary and Detail Tables
Coins has designed its database to match the processes of the industry and therefore the
tables mirror the process components. Each of the modules will cover different process and
within each module we can find different sub-processes. The organizational structure of the
company is also related to the structure of the tables.
Each of these sub-processes are also divided in tables that will hold information down to the
lowest level of detail and there will also be tables that will summarize that information at
different levels (depending to the process these could be dates, codes etc).
Company Organizational Structure & Contract Structure Example (House Builders):
Cost Transactions Structure Example (House Builders):
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 28 of 600
2.4 Open Items
In addition to the Summary and detail information, COINS also has specific tables for open
transactions in the database. This is to enhance performance when maintain and reporting on
current data.
For example every PL Invoice that has not been paid, or has been part paid will have an
associate record in the PL Invoice Open Item table. Once an invoice has been fully paid the
open item record is deleted.
It is therefore recommended that when enquiring or reporting on open items that it is the
open item record which is used as the basis of the query.
For example :
FOR EACH ap_invopen WHERE ap_invopen.kco = {kco},
EACH ap_invoice OF ap_invopen
Each of the tables which contain transactional data will have an associate open item table.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 29 of 600
2.5 Indexes
Databases can take advantage of indexing to increase their speed (Dataset retrieval using
queries). The most common kind of index is a sorted list of the contents of some particular
table column, with pointers to the row associated with the value. An index allows a set of
table rows matching some criterion to be located quickly.
The order that columns are listed in the index definition is important. It is possible to
retrieve a set of row identifiers using only the first indexed columns. However, it is not
possible or efficient (on most databases) to retrieve the set of row identifiers using only the
second or greater indexed column.
For example, imagine a phone book that is organized by city first, then by last name, and then
by first name. If given the city, you can easily extract the list of all phone numbers for that
city. However, in this phone book it would be very tedious to find all the phone numbers for
a given last name. You would have to look within each city's section for the entries with that
last name.
Each coins table has one or more Indices. An Index is built up of several fields in a record
which in combination will assist the query in narrowing down the number of records which
will be read to determine which meet the query requirements.
Each Table has a Primary (the index used by default unless you determine otherwise in your
query) and a Unique key (the combination of these fields in a single record is always unique).
However coins OA will use the most appropriate index for your query.
An example of how an index would work is to use the Current Logged in Company (kco) in
addition to Contract Number (job_num) to search for a particular Contract. Another would be
to use Current Logged in Company (kco) plus Order Type (tip_type) where you would query
only where tip_type = "TRADE", the query would immediately know only to search through
Subcontract Orders to find orders which matched the other criteria rather than search every
single order.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 30 of 600
2.6 Record Service Procedures
Every table in the coins database has a RSP (Record Service Procedure). Each of these RSP's
provides the OA Reporting tool with the business logic required to extract the appropriate
data from the database.
In the Database Enquiry you can see the RSP under the Table Code and its Label. The RSP's
have a naming convention –
{table-ID}-rsp.p.
Where the Table ID is as shown in the Database Enquiry (you may also hear this referred to
as the Table Acronym or TLA).
RSP’s control, amongst other things, the basic table update functions for that table. Each RSP
has a common set of methods that define standard behaviour for the object. They control
record scope and locking, security, default values on creation, data integrity rules etc.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 31 of 600
The RSP can also extend the database table to produce a logical view of data for the business
logic to work with. A by-product of this is that we are to provide an XML field on every
record in the database in which the user can configure their own extended fields for use in
the presentation layer. This enables client-specific fields to be added to screens and included
on reports. The RSP handles the translation of the data from the logical record buffer to the
physical fields in the physical database.
The RSP is also able to de-normalise the database for the purposes of logical data access. For
example, the contract record (jc_job) in the database is linked to many other tables including
the contract group table (jc_cgroup). To show the group description on a screen or report it
might be expected that the interface designer would have to build a query to link the
contract record with the appropriate group record and then display the group description
from that record. The RSP extends (for read-only purposes) the table and de-normalises the
data and makes the contract group description available as just another field on the contract
table. The data remains physically in the jc_cgroup table in the database, but to the business
logic and presentation layers it is shown in a more logical place which is on the contract
record.
The same principle is applied to more complex calculation fields. For example, the value of
accruals on a contract is a highly complex calculation involving many tables (purchase orders,
order lines, goods received notes, etc). Again, this data is made available in the logic buffer in
the RSP so that to the interface designer it is just another field on the contract record. They
are simply able to paste, say, the contract number, contract name, group description, and
accrual value on to a page without any need to know where each of the bits of data is coming
from.
The RSP is also able to simplify the database for updates.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 32 of 600
There are instances where common database tables are reused in many areas of the
application. An example is the address table sy_address. This holds the address details for an
employee record in the HR system. In this instance the RSP is able to link the two records
together, presenting a single logical table to the business logic and presentation layers. The
updates are performed on this logical buffer in the RSP and it is only the RSP that knows that
the data is split into two separate records when written back to the database.
The RSP also has an audit layer so that as the logical record is committed back to the
database, changes on the logical table can be recorded in the audit records. Auditing can be
performed as part of the managed data source through the use of triggers, but in this instance
the audit records created are an audit of the physical data and it is much more difficult to
reconstruct the separate physical table and field audit records in to a logical view of the
record at a later stage.
The RSP controls all data access through to the database. COINS can insert bespoke trigger
code in that RSPs that can act on data as it is committed back to the database. For example,
this can be used to push changes in business data in COINS out to a data warehouse
application by creating XML messages from COINS that are then consumed by a data
warehouse load interface. In this way it is possible to keep a data warehouse up to date with
live business data.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 33 of 600
2.7 Read Only Fields
In addition to the standard tables and fields in the coins database, Open Architecture also
uses the RSP's to provide access to certain calculated and non standard fields. These are
known as "RO" or Read Only fields and are also fully documented in the Database Enquiry.
Although these fields have certain restrictions, they are incredibly powerful when used in
enquiries and reports.
In most instances RO_ fields will provide information from related tables to the main queried
table – for example summary cost information at Contract Level, or descriptions from an
associated Lookup Table without the Page or Report designer having to query and access
many tables from the coins database.
Many of the calculated fields reflect similar fields to the coins + Configurable Reporter, such
as Accruals, Costs and Revenue fields. These fields can then be passed parameters to
enhance the information returned to a report. Typically these fields can be limited by dates,
values and financial periods as well simply parameters such as “TD" for a To Date value.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 34 of 600
In the Database Enquiry RO_ fields are shown in a format as the example below. Any
parameters immediately after the caret are mandatory; each parameter is then separated by
a pipe. Any parameters which are encapsulated in square brackets are optional.
RO_ContractCosts^<PeriodType>[|<PeriodOffset>[|<FDate>[|PhaseMasks[|CostcodeMasks
[|CategoryMasks[|AnalysisMask]]]]]
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 35 of 600
2.7.1 Exposing RO Fields
The benefit of using this method is the fields will be selectable by the user as report columns
when building the report, however as RO Fields they will NOT be available for sorting and
grouping. The disadvantage is that the fields will be hard-coded to the state they are
exposed as and any change to their functionality must be made by the administrator and
cannot be changed by the report writer users. For this reason, it is recommended that RO
fields are used in calculations within the Report rather than by Exposing.
Go to the OA Reporting and BI Setup and Table Documentation and select the table in which
the field you wish to pass parameters to.
The Parametrised Fields section then needs to be populated with the appropriate RO_ field
and any parameters that are required. (The parameter information can be found in the
Database Enquiry.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 36 of 600
A single field can be documented more than once with different parameters, for each a
balance field can be documented with several dates, such as a year of period end dates.
Examples of an RO_ field and associated parameters are :
jc_job.RO_ContractCosts^TP|-3 (This Period with period offset -3)
cs_certificate.RO_cst_ctd_cum^10 (Contract Sales Certificate Item – Cumulative to Date
Certificate Line 10)
Once a RO_ field has been entered in the Table Documentation with appropriate parameters
it will then become available in Report Writer for selection as per standard database fields.
It will also appear in the Database Enquiry with a field type of EX
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 37 of 600
2.8 Create and Maintain Lookups
Lookups apply to normal (fill in) fields, and to 'disabled with lookup' fields. Fields for which a
lookup has been set up have a lookup button next to them; disabled with lookup fields
have lookup and clear buttons next to them:
The lookup button launches a separate browser, from which the user can select a record. The
lookup then returns values to populate one or more fields on the form. If a user adds a new
record whilst in a lookup (assuming this is allowed) then when they save the new record it
will automatically be selected and returned from the lookup.
In Lookup Maintenance, enter a record for the field to provide a lookup for.
In the Lookup Function field enter the function to call for lookup. This function is a normal
browse/list type page (and could even be say the maintenance function - the maintenance
buttons will be retained if access security permits). Links are removed.
To provide different lookups for the same field on different functions, enter separate
records with the function code in the Function field. If the Function field is blank, COINS
uses this as the default lookup for the field.
The inline lookups are built using the existing page for the lookup button but there are two
new options you can specify in the parameters of the lookup. quickFields and quickShow.
By default the inline lookup uses the same field (keycode/lookupcode) that is used by the
lookup page and does a begins on that field. If you need to modify the field to another one or
multiple then you can do this with quickFields. This is a comma separated list of field names
to be matched when typing in the characters in the fill in. Example is poh_attention where
quickFields is set to ppc_name,ppc_surname so the matching is on either of those fields.
In the parameter field for the lookup, it is possible to specify the following:
lkeyfield=field. By default (if you do not specify a keyfield), the lookup will return the
value of the field from the lookup record that has the same name as the field you are
looking up (that is, the one specified in the Field field in Lookup Maintenance). By
specifying a keyfield, you can get the value of a different field. For example, the Field
may be avm_factnum, but the keyfield is specified as avm_num.
llookupCode=field-list. A list of other fields on the form that you want to populate (with
the corresponding values from the keyCode list).
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 38 of 600
lkeyCode=field-list. A list of other fields in the same table that you want to get values
from. If keyCode is blank, the values are taken from the field specified in lookupCode.
lcontextFields=field-list. A list of fields on the current page that are required for the
context of the lookup (for example, the supplier code if you are looking up invoices).
These will be passed to the lookup program and used in the query. See RO_hrs_desc
lookup, which requires the current value of hrg_code from the form to be able to show
the appropriate sub groups.
lquickFields=field-list. A list of fields which the inline lookups will query (For example:
quickFields=job_num,job_name - will return the following lookup when entering
characters in the lookup field. It currently uses a begins to provide the search. Example
will give you any job_num beginning with 10 or any job_name beginning with 10 as a drop
down list. A max of 10 entries will be shown and if further information is available on the
11th line of the inline you will see ... If there is an exact match to the entry then the field
will be filled in on leaving the field via tab or enter. By partial input the lookup button will
reflect this search filter, by not entering any input the lookup filter will be remembered
from previous entry.
lquickShow=field-list. This will provide the fields to be displayed in the inline
lookup.These fields can be either database fields or RO fields. It is advisable to keep this
list to a minimal number.
lquickSort=field+. This will predetermine the order the inline information is displayed.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 39 of 600
3 OA Query Language
COINS OA uses a simplified version of the Progress 4GL query language in combination with
RSP's (Record Service Procedures) to extract the data for reports and enquiries (for further
information on RSP's– see later in this guide).
COINS OA uses the query to decide which records are accessed from the coins database
from the database. In response to a query, the database returns a result set, which is just a
list of rows containing the answers. The Page/Report Design will determine which fields from
these records are displayed (either on screen or in a report).
The simplest query is just to return all the rows from a table, but more often, the rows are
filtered in some way to return just the answer wanted.
The flexibility of relational databases allows programmers to write queries that were not
anticipated by the database designers. As a result, relational databases can be used by
multiple applications in ways the original designers did not foresee, which is especially
important for databases that might be used for decades. This has made the idea and
implementation of relational databases very popular with businesses.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 40 of 600
3.1 FOR EACH
To begin a query in OA, the first statement must begin FOR EACH followed by a table name.
Example Query on the coins database to retrieve all contracts (jc_job)
FOREACH jc_job
1. jc_job is the name of the table in the COINS database
2. The FOR EACH statement starts a block of code that iterates once for each
contract record (hence the syntax FOR EACH)
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 41 of 600
3.2 WHERE
Simply specifying the table with a FOR EACH statement in a query is okay, assuming we
want every record from the selected table, but in practice we would normally want to
restrict the number of records returned in some way. In COINS, transactional data is held at
company level.
Even though you may only have one company in your organisation, the data is still recorded
with a company identifier. COINS uses the field kco to identify the company number.
Most queries will need to specify the kco values to ensure that the records returned relate
specifically to the company you are reporting on.
The WHERE statement is used to add a constraint to the query and may refer to a constant,
filed name, variable name or expression whose value you want to use to select records
Example Query on the coins database to retrieve all contracts (jc_job) that belong to
company 1:
FOR EACH jc_job WHERE kco = 1
In the example above we have used ‘=’ as the comparison operator. There are a number of
others than may be used with the WHERE statement. These are listed in the table below:
Keyword Symbol Description
EQ = Equal to
NE <> Not equal to
GT > Greater than
LT < Less than
GE >= Greater than or equal to
LE <= Less than or equal to
BEGINS Not applicable A character value that begins with this substring.
MATCHES Not applicable A character value that matches this substring, which
can include wild card characters
The expression you use to the right of the MATCHES
keyword can contain the wild card characters:
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 42 of 600
Keyword Symbol Description
An asterisk (*) represents one or more missing
characters.
A period (.) represents exactly one missing character.
CONTAINS Not applicable A database text field that has a special kind of index
called a WORD-INDEX
The WORD-INDEX indexes all the words in a field’s
text strings, for all the records of the table, allowing
you to locate individual words or associated words
in the database records, much as you do when you
use an Internet search engine to locate text in
documents on the web..
The WHERE statement can be followed by any expression that identifies a subset of the data
using AND/OR to join multiple tests.
Example Query on the coins database to retrieve a specific contract (field job_num) for
Company 1 from table jc_job
FOREACH jc_job WHERE jc_job.kco = 1
AND jc_job.job_num = '123456'
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 43 of 600
3.3 Joining Tables
Often, data from multiple tables gets combined into one, by doing a join. Conceptually, this is
done by taking all possible combinations of rows (the "cross-product"), and then filtering out
everything except the answer.
To begin each join a comma should end the previous statement before beginning the next
one. DO NOT add a comma to the end of the last statement as this will result in an error.
3.3.1 EACH
FOR is only used for the first table in the query, all subsequent tables must be accessed with
EACH to start an iterating query that will find a single record on each pass
To establish a join, the table(s) you are adding to the query must have some relation to one
or more tables already in the query.
Example Query on the coins database to retrieve all costheads (jc_costcode) that belong to
contracts (jc_job) that belong to the logged in Company
FOREACHjc_job WHERE jc_job.kco = {kco},
EACH jc_costcode WHERE jc_costcode.kco = jc_job.kco
AND jc_costcode.job_num = jc_job.job_num
If you do not use the EACH keyword for a subsequent table then you must use one of the
following to obtain a single record:
3.3.2 FIRST
Uses the criteria in the record-phrase to find the first record in the table that meets that
criterion.
Progress finds the first record before any sorting.
3.3.3 LAST
Uses the criteria in the record-phrase to find the last record in the table that meets that
criterion.
Progress finds the last record before sorting.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 44 of 600
The FIRST and LAST keywords are especially useful when you are sorting records in a table
in which you want to display information. Often, several related records exist in a related
table, but you only want to display the first or last related record from that table in the sort.
You can use FIRST or LAST in these cases.
3.3.4 OF
Some of the tables in the COINS database share a relationship based on common field names
between record and table that also participate in a UNIQUE index for either record or table.
All OF relationships within the coins database are detailed in the database enquiry and
appear for each table in the form similar to:
Where such a relationship exits, the OF statement may be used to relate one table to
another. So in our earlier example we used the query:
FOREACHjc_job WHERE jc_job.kco = {kco},
EACH jc_costcode WHERE jc_costcode.kco = jc_job.kco
AND jc_costcode.job_num = jc_job.job_num
An OF relationship exists between jc_job and jc_costcode as can be seen in the database
enquiry for jc_job:
So we can re-write this query as:
FOREACHjc_job WHERE jc_job.kco = {kco},
EACH jc_costcode OF jc_job
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 45 of 600
3.4 Curly Braces
The functionality of {}'s is to specify a place holder in fields and calculations into which a
value can be passed.. When using {}'s around a field the use of quotes is required if the field
is a character field. The use of double or single quotes is acceptable.
The only thing to be aware of is that when using '{field}' replacement on a character field is
that if the information within the field could contain an apostrophe (for example- J O'Connor)
then the apostrophe would cause close to the single quote and you will get a symbol not
found(Connor) Error. To overcome this error the use of double quotes "{field}" is the answer.
The use of {}'s in calculations is possible on all field values except within the DataSets and
the calculate conditions on a report. In these instances it is necessary to always qualify out
the field with the table name.
{RO_ContractCosts^TD|0|{RS_glp_fdate__2}}
would be written as:
jc_job.RO_ContractCosts^TD|0|{RS_glp_fdate__2}.
The use of the table name is allowed in all calculations but whereas in most instances the
formatting of the result is suppressed, within the calculate condition it is not and therefore
the comma in a result of a figure in excess of 1,000 may result in an error in syntax in a
calculation. (NB. Please note that the replacement on parameters of an RO field is still
acceptable).
Within the OA reporter/screens we use curly braces {} as a method to pass values to a query
or a report or a page. Enclosed within the curly braces you specify the commands, RS_fields,
or other data you need to communicate across or within objects. {kco} is a common usage,
and is used to place the current logged in company number into the query.
The next example gets information from jc_job and inherits the Company Number from the
system, retrieving the company number the user is logged into.
FOREACH jc_job WHERE jc_job.kco = {kco}
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 46 of 600
3.5 Outer-Join
Specifies a left outer join between record and the table (or join) specified by the previous
Record phrase(s) of an OPEN QUERY statement. A left outer join combines and returns data
from the specified tables in two ways. First, the records selected for the table (or join) on the
left side combine with each record selected using the OF or WHERE options from the table
on the right (record). Second, the records selected for the table (or join) on the left side
combine with unknown values (?) for the fields from the table on the right (record) for which
no records are selected using the OF or WHERE options. The join is ordered according to
the given sort criteria starting with the left-most table in the query.
If you specify the OUTER-JOIN option, you must also specify the OUTER-JOIN option in all
succeeding Record phrases of the query to obtain a left outer join. That is, for multiple
Record phrases, all joins in the query following your first left outer join must also be left
outer joins. Otherwise, the result is an inner join for all records up to the last inner join in
the query.
The OUTER-JOIN option is supported only in Record phrases specified after the first Record
phrase in the query. If you specify OUTER-JOIN, you must also specify the OF option,
WHERE option, or any combination of the OF and WHERE options. These options are
required to select record (the right-most table) for the specified left outer join.
This query specifies a left outer join between customer and order, and also between that join
and order-line. Thus, for each customer record that has no orders or has no orders with an
order-num less than 50, the query returns the customer fields and ? for all fields of the order
and order-line tables. In addition, if there are no order-line records with item-num less than
15 for any selected customer and order, the query returns ? for all fields of order-line.
Otherwise, it returns each customer record along with its first selected order record and
order-line record.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 47 of 600
In all statements where multiple Record phrases are allowed, the default join (without the
OUTER-JOIN option) is an inner join between record and the table (or join) specified by the
previous Record phrase(s). An inner join returns the records selected for the table (or join)
on the left side combined with each selected record from the table on the right (record). For
an inner join, no records are returned for the table (or join) on the left for which no record is
selected from the table on the right (record).
If you specify a Record phrase as an inner join, the current Record phrase and all preceding
Record phrases in the query participate in contiguous inner joins, even if prior Record
phrases specify the OUTER-JOIN option. Thus, for multiple Record phrases, all joins in the
query up to the right-most inner join result in contiguous inner joins.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 48 of 600
3.6 Buffers
There are circumstances when you may wish to access a single table multiple times in a
query. The method for doing this is to use buffers in your query.
Using buffers in a Body Query.
The buffers MUST be named xxn<table> where n is a letter.
Example Syntax:
The buffers are automatically created (and deleted) and you can refer to fields using
xxn<table>.field
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 49 of 600
3.7 Calculation Programs
This section describes the new capability for the user to store programs (effectively user
defined functions) for use in calculations throughout COINS.
The standard COINS syntax is used in the stored program and it can take input parameters
from the calculation from which it is called.
User defined functions can call other user defined functions recursively.
The variables used and set in the original calculation and all the user defined functions are
globally shared. So a variable set in a user defined program is then available in the
calculations that follow it.
3.7.1 Creating Calculation Programs
You can define user defined programs using the Calculation Programs maintenance function
in System > Misc Maintenance (also available via OA Reporting and BI > OA and BI Setups).
To create a new program, click
lThe program name is unique and is the code by which the function will be run when used
in a calculation.
lThe description and notes are for information only, they do not affect the use and are
there for you to document what the function does and how it should be used.
lThe calculation code field is the calculation that will run when the program is called. This
can contain {n} where n is a number. These will be replaced with the values of the
parameters passed to the function at run time. The calculation code can run other user
defined calculation programs in the same way that any other calculation.
If the program is run without passing the required {} parameters then they will be replaced in
the calculation with blanks.
Variables defined before the calculation is run can be accessed. Variables defined or updated
in the calculation program will be available following the call to the calculation program.
3.7.2 Calling Calculation Programs
The calculation programs can be called by any calculation in COINS e.g. report field
calculations or workflow initialisation calculations etc.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 50 of 600
The calculations defined can be run by using the new calculation keyword RUN or RUN$.
RUN runs a calculation program returning the last decimal value from that calculation
whereas RUN$ expects a string to be returned.
If a calculation program called CALCTEST is defined with the following calculation code:
theSum={1} + {2}
then it can be run in a calculation as follows:
run(‘CALCTEST’,’5’,’7’)
this would return the answer 12 and could take part in a further calculation e.g.
grandTotal = 10 + run(‘CALCTEST’,’5’,’7’) + 8
this would return 30
The calls to the programs can also use variables
A=5
B=7
$PROG=”CALCTEST”
Run(PROG,A,B)
theSum
This would also return 12.
By constructing appropriate functions and/or programs in this way it will make using complex
features much easier to maintain by placing the complexity in a reusable function.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 51 of 600
4 OA Calculation Syntax
This section explains the syntax of the various types of calculations that may be used within
the COINS OA Framework
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 52 of 600
4.1 Finding the Period Number from a Date
For Character results:
$a=method$(glp-rsp.FdatetoPeriod,Offset,Date)
$a=method$(glp-rsp.FdatetoMonth,Offset,Date)
$a=method$(glp-rsp.FdatetoYear,Offset,Date)
For numeric results:
b=method(glp-rsp.FdatetoPeriod,Offset,Date)
b=method(glp-rsp.FdatetoMonth,Offset,Date)
b=method(glp-rsp.FdatetoYear,Offset,Date)
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 53 of 600
4.2 syuquery aggregate
syuquery.aggregate and syuquery.aggregateRSP (via rsp) takes two parameters, query and
field list.
Fields in the field list are aggregated. First total is returned (typically might be only one field)
You can then get the other totals with syuquery.getDAgg,n and count with getIAgg
Multiple fields with comma delimited allowed on last part of method then you can call with
method('syuquery.getDAgg',1); method('syuquery.getDAgg’,2); etc
Figure 1: Example using syuquery.aggregate
method('syuquery.aggregateRSP',"for EACH pp_planenroll WHERE pp_planenroll.ppo_seq =
'" + pp_organisation.ppo_seq + "' AND hbp_plan = 'Office Vacation'",'RO_Accrual_Balance');
dvacav=method('syuquery.getDAgg',1);
$string$(dvacav,"->>>,>>>,>>9”);
Figure 2: Example using syuquery.aggregateRSP
method('syuquery.aggregate',"for EACH se_orderlive WHERE se_orderlive.kco = " + co_
config.kco + ",first se_order WHERE se_order.sso_intref = se_orderlive.sso_intref and se_
order.sso_complete = yes,first ar_custaddr OUTER-JOIN WHERE ar_custaddr.kco = se_
order.kco AND ar_custaddr.rcm_num = se_order.rcm_num AND ar_custaddr.rca_code = se_
order.rca_code",'kco');
method('syuquery.getDAgg',1);
method('syuquery.getIAgg');
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 54 of 600
4.3 Performing Calculations on Numerical Values
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 55 of 600
4.3.1 A Simple Count
Creating a Simple Count and Accumulating the Calculation Manually
To simply count the number of records being returned, assign the value in each to be 1 – this
value can then be totalled and manipulated in the same way as other values. For example –
the calculation below would count each line and complete a running total :
a = 1; counta = counta + a;
Counts can also be used in conjunction with all other functions to allow counts of certain
types of information, e.g. the example below where dStockItemCount is checking the PO
Line type and assign 1 if it is a Stock Item, this value is then being totalled.
dStockItemCount = INLIST("{po_line.pol_type}","I");
dTotStockItemCount = dTotStockItemCount + dStockItemCount;
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 56 of 600
4.3.2 String$
Purpose: To display a numerical value as a character
Syntax: $string$(number value,”Format”)
Number
value
The numeric value to be converted
Format: The output format required (uses standard format notation and must be
surrounded by double quotes)
Figure 3: Example
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 57 of 600
4.3.3 IF
An IF statement is split into two separate areas and all parameters of the 'If statement' are
separated with commas
The Test
The Result
The TEST
The first section of an 'if statement' can be referred to as the 'Test' it has to compare two
numerical values. If the field that is being tested is a character value then that field has to be
converted into a numerical value to do the 'Test'. (see calculations on character values)
The RESULT
The result will either be a true or false answer. Without the specification of any true and
false results an if statement will automatically return a 1 if true and a 0 if false. Therefore it
may be a simple test between two numbers and can be defined as follows:
Figure 4: Simple examples of an 'IF statement'.
IF(a,EQ,b); returns 1 if true / 0 if false
IF(a,EQ,b,truevalue,falsevalue); returns truevalue if true /falsevalue if false.
The operands available for use within the IF function are :
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 58 of 600
EQ Equal To
NE Not Equal To
LT Less Than
GT Greater Than
LE Less Than or Equal To
GE Greater Than or Equal To
If the'Result' of an IF statement needs to be a character value then the IF statement must be
encapsulated within $ signs.
$IF$(a,eq,b,"Yes","No")
If a variable is to be assigned then the variable will be situated between the first $ sign and
the IF statement.
$var=IF$(a,eq,b,"Yes","No")
NB : Using an IF statement to create a simple Flag, in the example below the calculation is
setting a variable, dFlag, to either 1 or 0 depending on the comparison of the Valuation Due
Date against the Report Selection Date passed through to the report - this flag can be used to
multiply values in other fields.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 59 of 600
dFlag = IF(datestring(cs_valcert.cvc_duedate),LT,datestring('{RS_date__2}'),1,0);
Note : Date format fields require special consideration – please see below.
Example of above:
Field1={RO_ContractCosts^TD} * dFlag
Field2={RO_ContractRevenue^TD} * dFlag
Field3=({RO_ContractRevenue^TD} - {RO_ContractCosts^TD}) * dFlag
This means that only when the dFlag is true will these figures for Field1,Field2 & Field 3 will
appear as a multiplication by Zero will return value of Zero.
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 60 of 600
4.3.4 RANGE
These two functions will allow the comparison of data against series of other values:
The RANGE function will return 1 or 0 for a true or false result when comparing a value
against an upper and lower limit and should be used in the format:
RANGE(value,lowerlimit,upperlimit)
Therefore:
RANGE(5,1,10)would return 5 (true returns value)
RANGE(5,10,20)would return 0 (false)
In the example below, the RANGE function has been used within an IF function to calculate
values in Ageing columns for Purchase Ledger invoices, where the variable Days has been
calculated as an integer value of the days between TODAY and the Due Date on the Invoice:
AgeDays = (TODAY - datestring(ap_invoice.ain_duedt))
IF(AgeDays,LE,30,ap_invoice.ain_balance,0);
IF(RANGE(AgeDays,31,60),GT,1,ap_invoice.ain_balance,0);
IF(RANGE(AgeDays,61,90),GT,1,ap_invoice.ain_balance,0);
IF(AgeDays,GT,90,ap_invoice.ain_balance,0);
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 61 of 600
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 62 of 600
4.3.5 Limit
The LIMIT function will return values dependant on where the value falls in relation to the
lower and upper limit values. The limit function is useful is reports scores where you cannot
allow scores to fall outside the limit of valid scores eg 1 to 10. The format for using LIMIT is:
LIMIT(value,lowerlimit,uperlimit)
If the value is below the lower limit, the function returns the lower limit value, if it is within
the range it returns the value itself, if above the upper limit it will return the upper limit
value.
Example
LIMIT(X,A,B) would return x if x is in the range a-b, otherwise return a if x<a, or b if x>b.
LIMIT(1,5,10) would return 5 (FALSE returns lowerlimit)
LIMIT(11,5,10)would return 10 (FALSE returns upperlimit)
LIMIT(6,5,10) would return 6 (TRUE returns value)
COINSOAEnhancement:
COINS BI Toolset User Guide
31-May-2017 Construction Industry Solutions Page 63 of 600
4.3.6 Max</