Code for production of a Farmer List with Crop Hectages

This help topic works through the stepts needed to create a Resource which can be used to show farmer acreages based on the current Inspections data for each farmer.

Steps to create the Resource

Create new Resources Record

Go to System, View / Edit Resources.

When the Grid opens, double click on any record to open the edit-window.

Click on the "+" in the toolbar to add a new Resources record to the data-table (1).

Set values of Resource meta-data fields

To enable the resourc to appear, linked to part of SerendiSys, seet the meta-data fields as they need to be set (2). Note particularly that the "ComponentID" is set to "Cube" so a data-cube report will be generated.

Draft SQL (explanations of individual lines follows below)

Draft the SQL.

Remember that you can right-click on the SQL Editor and select "Add Script created using SQL Modeller", which opens the Orixa SQL modelling tool which will create a first-draft of SQL for you.

As you draft the SQL remember you can right-click on the SQL Editor and select "Test SQL" or "Show data in Grid" to see whether your script works, and what it shows.

Click on the SQL Toolbar, and select "Refresh Database Resources"

Once the SQL is complete, you will want to design the user interface which appears when users run the resource.

To update the list of active resources, click the SQL toolbutton and select "Refresh Database Resources".

New Resource will be added to the "People, Farmer" entity

Once database resources are updated, the new Resource will automatically appear, as shown in the screen on the left.

Explanation of SQL Script

SELECT
F.FarmerCode,
P.FullName as Farmer,
I.DateDone as InspectionDate,
C.Name as Crop,
F1.PlotCode,
    T.Name as CropType,
    Hectares,
    NumTrees
FROM Farmers F
LEFT JOIN People P ON (F.ID = P.ID)
LEFT JOIN Inspections I ON (P.ID = I.PersonID)
LEFT JOIN FarmFields F1 ON (I.ID = F1.InspectionsID)
LEFT JOIN Crops C ON (F1.MainCropID = C.ID)
LEFT JOIN Types T ON (C.CropsTypeID = T.ID)
WHERE I.Current = true
AND F.FarmerCode LIKE '%[STR FarmerCode]%'
AND I.DateDone IS NOT NULL
AND Hectares > 0
ORDER BY F.FarmerCode, C.Name

SerendiSys stores farmer data relating to the crops and land they own in the "FarmFields" data-table, so this is the "main" data-table we need to access to find the needed data. However, this data is update each year via a new Inspection, so for Current data, we have to add some lines to the SQL to ensure we only bring back data we want.

The SQL Returns the "FarmerCode" from the Farmers data-table, which is the "lead table" in the SQL statement. This means it is the first table to appear after the FROM keyword. The SQL then returns the "FullName" of the farmer from the People data-table.

After this it pulls back the InspectionDate, Name of Crop, Farm-Fields PlotCode, CropType Hectares and NumTrees data-fields from the FarmFields data-table.

Note the WHERE clause. This ensures that the data which is returned is only for the "Current" inspection (the most recent inspection) and then uses the syntax [STR FarmerCode] to allow users to enter part of the farmer-code to allow the resulting data to include only farmers from one farmer-group. As some Inspections may not have been completed, we add "I.DateDone IS NOT NULL AND Hectares > 0" to remove incomplete Inspections.

Steps to Design the "Data Cube" to show data as desired, and export it in Excel Format so non-System Users can view the data

New Resource will be added to the "People, Farmer" entity

Click on the resource to activate it.

Run the Resource and enter a FarmerCode search

Because the SQL Script includes a search-text syntax, you are asked to enter some search text.

Remember, if you leave this blank then all farmers will be returned.

Find Fields generated from the SQL

Once data is returned, the Data-cube interface will update with extra "Field List" "Measures" and Toolbar user interface elements

Drag fields to the axes of the data-cube

Click on the Field-list item to show the list of available fields, and click-and-drag individual fields to different parts of the data-cube.

Fields you want to be "rows" should be dragged down to the "rows" area.

Fields You wanto to be "columns" should be dragged up to the "columns" area.

Fields you want to see inside the data-cube should be dragged into the centre of the data-cube.

Key step: Drag "Measures" to sit beside the "Columns"

Once all the fields are arranged as you want them, there is one more step.

Click on the "Measures" item and drag it so it sits beside the column fields.

Once this is done the data will appear in the data-cube, based on the data-set you have created in the earlier steps.

Data shown for resulting data-cube

Full data on show in the data-cube

Exporting resulting data to Excel

Click on the "Export Cube" button to open opens to export the data cube to Excel

Filtering resulting data to only show certain Crops

Note that prior to export, you may want to filter the data. Click on the right hand side of any field as shown at (1), the list of values in the data will be shown, and you can tick and untick the ones you want.

Resulting data shown in Excel from saved file

Resulting data displayed in Excel

Data-cube data can also be shown in Chart format

Note that SerendiSys data-cubes can also show data in Chart format. Simply click on the "Toggle chart"/cube tool-button.

Chart can be toggled to show total by Column as well as by Row

And charts can be "toggled" to show totals for either rows or columns. Click on the "Format Cells" toolbutton, and then show the chart.

Extending this resource to show other data

Duplicate Resource Record

Note that you can add:

Farmer Gender
Farmer Age(s)
Total Deliveries of palm / cocoa.
Size of famer household (if recorded)

to the above data-set, just by extending the SQL.

The resulting data can be shown in new data-cubes, or in other data formats including charts.