Data Sets provide a view of the Coins ERP+ data schema and business logic layer that:

Data Sets can be shared throughout the toolset, so the same Data Sets can be used to generate Coins ERP+ Data Marts, used in Page/Report Designer, and made available to Report Writer Queries. This common business view is then shared at all levels of an organisation to ensure reporting integrity across the same information.

Data Sets can also be accessed via external reporting tools through the Coins ERP+ Application Adaptor.

Once the fields which are required have been identified the query to obtain those fields must be created. In this example the fields required have been identified as

• Contract Number –(job_num)

• Contract Name – (job_name)

• Contract Cost – (RO_ContractCosts^TD)

• Contract Revenue – (RO_ContractRevenue^TD)

• Contract Profit (RO_ContractRevenue^TD – RO_ContractCosts^TD)

To create a new Data Set:

Reporting and BI > Designer > Data Set Definitions

Click

Select a Data Set Name and Description for the Data Set in this example is has been named 'NLWJC_PROF' with a description of ‘Standard JC Profit/Loss DataMart'.

A Query must be assigned to the Data set, which in the following example is a simple query of each Contract which takes advantage of a 'Contract Selection' made at run time.

FOR EACH jc_job WHERE jc_job.kco = {kco}{jobSelect}

Create an identifier for the Data Set Table Name. In this example it is 'contract'.

Once created, the table name is always prefixed with a ‘tt’ when used, so in this case the table will be accessible in a report using a ‘FOR EACH ttcontract’ query.

There are three calculation fields which can be applied to the data set.

Variables used in a Dataset are NOT available in the report.

To move onto the Data Set fields, click

When Adding fields to the table, you can give the field a Name that can either be same field name as the field in the source table or you can name the field something that will help Report Writer/Designer users identify the information in the field more easily. (Eg: location_desc is assigned to the field jcl_desc)

Each field should be given:

As an example, the sample of the fields which have been added to the Data Set are seen in the diagrams below.

Debug(1); - Can be turned on to debug a dataset but should be removed when setting a DataSet live. Debug(1) should be defined as a calculation in the Initial Calculation box. Once debug is turned on then all calculated fields will be verbose in the Report Log File.

Data Sets can be exported from one environment to another by simply choosing the Export Data Set Definitions option from the Designer menu.

Enter the Data Set Name or use the lookup facility to find the relevant Data Sets and click Next.

Once the Data Set has been exported to the Definition Data Window use the standard windows select all(Ctrl-A) and copy(Ctrl-C) functions to copy the data from the Definition Data Window and then Paste(Ctrl-V) to the Import Data Sets Definitions window also found Menu below in the environment you are wanting to add the Data Set after which you should then click the save icon.

Once the Data Set setup has been completed you can allow access to the relevant users by creating a %DttContract Function via Function Maintenance.

Access to this function is then granted via the standard Function Security procedure.

The Data Set can be enabled to be accessed by Report Writer by the creation of a Report Writer query accessing the ttContract table.

It is possible to summarise DataSets by adding a ‘KEY’. If the query is looking at all Contract (jc_job) records but the requirement in the report is to be by Contract Location then a ‘KEY’ can be set at kco/jcl_loc (ie: Tick the Key box on both of these fields). In this instance only one record will be created on a unique find of Company/Contract Location. Any numerical fields are accumulated whilst character fields are assigned where there is common data where records share the same ‘Key’ details. (If it finds character fields which differ then the value of the field will be blank).

If there is a requirement to do a calculation once all of the records for the ‘Key’ are

accumulated then you can tick the ‘Recalculate Summary’. (Eg: An example of where this might be required is when a percentage is required).

Note: Any field which is used as a key CANNOT have a value of blank.

To achieve Cross Modular Reporting two independent Data Sets can be created using different Source information but containing the following commonalities

The datasets can then be both called on the report and the common table name will unionise the data. An example of such an application might be a Accounts Payable and Subcontract Ledger Open Invoice Dataset.

The Query Editor allows fast access to information in the Coins ERP+ Database via the Business Logic. It also allows export to Microsoft Excel.

It is possible to reference a Data Set from the query editor. Simply referencing the data set and running will return the complete contents of the Data Set - No query is required.

If the Dataset query takes a parameter replacement field (i.e. {RS_year}) then you can set the parameters by adding them to the URL on the Query Editor Page. (i.e. &RS_year=2009)

This has allowed the extract to Excel to be more flexible as limits in the information being passed limited both the query length and number of fields that could be accessed through the editor. Referencing a dataset code greatly enhances the ability to extract data this way.

The Data Set can now be used in Queries in Report Designer. Using our example dataset, the body table should be ttContract, the body query should be - FOR EACH ttContract and the Data Set Definition would be BIWJC_PROF.

Note that you can use more than one dataset in a query. To do this enter each dataset name in the Data Set Definition field separated by a comma. You will need to reference each ttTablename as appropriate in the body query.

It is possible to sort on any field from within the Data Set – In the example below we are using a field which has been populated with the virtual field jcl_desc with is the Location Description. This will put the contract in alphabetical order of the locations to which they belong. (NB. It is always necessary to qualify the field name with the Data Set table name in the sort)

Fields can then be added to the report in the standard way. It is always good practice to qualify the field names also with the Data Set table name although it is not mandatory.

For in-line reports, specify the dataset(s) to be used on the container report e.g.:

Then for each inline report, only use the ttTablename in the body query of each report but do not specify the dataset e.g.

This will ensure that the datasets are only built once and then data then shared across each report that uses it. If you specify the dataset name in the in-line reports, the dataset has to be rebuilt for that report which will have an impact on performance.