Thursday, November 15 2018

Report Writing and Programming in Express Maintenance Part 1

Report Writing / Programming in Express Maintenance Part 1

I have found calculations and accessing variables not intuitive in the RAP environment so these are my notes to help eliminate some pain between tasks.

Basics: SQL = "Structured Query Langauge" Variable = "A container to hold a specific value, this could be an integer, string, etc.."

Start with the end in mind

DashBoard.PNG

  • Add your local variables these will be containers for values, see image above

Screen Shot 2018-11-15 at 8.39.11 AM.png

  • Declare your global variables where values will come from another source

Declarations.PNG

  • Map your global variable to your local variables

Summarty from Global.PNG

  • Sub reports will have logic and calculations then will get assigned to your global variables

IfThen at detail level.PNG Summary update and globals update.PNG

SQL Queries and Joins

To get a good report out of Express Maintenance it starts with a good SQL query and how you join the tables to get the data you are looking for. DashBoard.PNG This example has several queries working together

To do that you first need to know what tables have the data you need.QueryBuilder.PNG

((/blog/public/SQL_Joins.png
SQL Joins.png, Nov 2018
from https://www.dofactory.com/sql/join

JoinsFrom Code Project Website https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins https://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins Inner JOIN Inner.png This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key

Example: Table A is units and Table B is WorkOrders. The output would be all of the WorkOrders where the Units from Table A matched.

Left JOIN Left.png This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key

Right JOIN Right.png This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key

Outer JOIN Outer.png This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key