This is the first of multiple articles on creating ReportWriter Reports.  Begin your report creation journey here.

1. Begin Report Creation

To start creating a report, click on the "Reports" tab.  This brings you to the Reports tab.  It is initially empty until you hit "search" - but to create a new report, you do not need to hit search.  Instead, click the "Create a Report" button:

This brings up the "Create a Report" dialog:

All fields are required, but when you enter a name for the report in the "Name" field, it is automatically copied to the Description field and, turned into a code, into the "Unique Code" field:

You can alter the Description and Unique Code fields or leave them as is;  Description can be changed later but the Unique Code cannot be (it becomes the internal identifer for the report).

The "Group" can be changed now or later.  It is never blank - the report will always be part of a group.

When you are done, simply click "Create" to create the report.

2. Selecting ther Base and Joined Tables

As soon as you click "Create" the "Tables" screen is displayed.  Your first task is to select the Base Table for the report.  This is the starting point table:

There are many tables in many functional group available to you.  To help in selecting the correct table, you have two tools:  Corresponding to each table, on the right side of the screen, you'll find a "Schema" button and a "Preview Rows" button.  These help you discover what sort of data is in each table.   You can scroll down to see the full range of tables.

Some common starting point (i.e. 'base') tables include:

- Staff  - the start of each staff member's record.

- PositionTracking - The position tracking table.  There is a record for every active position.

- Position - The list of positions - Each record in this table describes a position.  The records in the Position Tracking table each link a Position with a staff member.

Taken together, the Position table and the PositionTracking table constitute the district's Position Control Roster.  The HR, Evals and Payroll systems are based on the Position Control Roster.

To select a base table, find your desired table in the list and click on the "Select Table" button.  The tables are generally in alphabetical order, however, the Staff table is so often selected as the base table that it is conveniently positioned at the top of the list:

To select it, click on the corresponding "Select Table" button.

Once you select the base table, the Columns screen is immediately displayed.  Typically, various columns have already been added to the Columns screen:

If you selected the Staff table, a basic set of demographic information is automatically selected.

You have multiple options here:

1. You can return to the Tables screen and select additional tables to "join" to the report.

2. You add additional columns directly from the base table.

3. You can add single columns from any other table you can link to.

4. You can add Expression Langauge columns.

5. You can go directly to add parameters, filters or sorts to your report.

Options 2, 3 and 4 are offered when you the click the "Add Columns" screen:

Adding Joined Tables

To join additional tables to your report, click on the "Tables" tab.  This brings you back to the Tables screen:

The tables screen now lists your base table at the top in the "Selected Tables" area and all other tables that can be directly joined to the base table are listed below.   You still have the "Schema" and "Preview Rows" discovery tools available to you.   To actually 'join' one of the tables to the report, click the "Join Table" button corresponding to it:

(In this example, we scrolled down to find the PositionTracking table and will select it).

When you click the "Join Table" button, you are presented with a "Select Linkage" dialog:

On the Tables screen, tables are always linked together with a single link.  Additional linkages can be added later as filters on the "Filters" screen.  Most of the time, there will be a single option in the "Select Linkage" dialog and you will not have to choose.  If you are writing more complicated reports there might possible be more than one option. the following section gives you a little bit of guidance on how to choose.   When you have selected the columns to link on, click "Add" to perform the join.

Guidance on the Links & Joins

Every table in the system typically has an "object id" field.  E.g. The Staff table's 'objectid'.  Object IDs are internal codes that uniquely identify something - like a staff member or a Position, etc.   They are NOT ever shown to users.  But they are your best friend for linking.    When two tables are capable of being joined, one table will contain the "object id" of the entry in the other table  and it will name that object id accordingly.  For example, the PositionTracking table links to the Staff table and the "link field" is named  'staffObjectId'.  So the "staffObjectId" field in the PositionTracking table points to the "objectId" field of the Staff table.     You can identify the links by how they are named.  A link to the Position table would have the name "positionObjectId".    When joining tables, carefully examine the "Link on which columns" to make sure the names look like a match:

After Joining a Table

As soon as you click "Add" to create a Join, you are again immediately brought to the Columns screen.  The Columns screen now contains columns pulled from the newly joined table:

You again have similar options for adding columns, however, you can now additionally add columns feom the joined table:

Joining More Tables

You can again return to the Tables screen to Join another table.   At the top of the screen you base table and all joined tables are now listed:

You can use the search fields at the top of the list of tables to narrow your choices:

The list of tables avaialble to join will be different from those available the first time:  the next table to be joined must link to the previously joined table, not the base table.    We will link in the Position table, which was not available initially:

Again, when "Join Table" is selected, the "Select Linkage" dialog is displayed:

Notice that the table to be newly joined is linked via the previously joined table ("PositionTracking").   Again, make sure that the link names appear to line up correctly.  The 'positionObjectId' field in trhe PositionTracking table links to the "objectId" field in the Position table.  

Now the Columns screen is displayed - this time with columns extracted from the newly joined Position table:

Joining Multiple Tables

There is no built-in limit to how many tables can be joined.    

Removing Joined Tables

Tables must be removed from a ReportWriter report in the reverse order from which they were joined.

The Tables screen always displays the list of the base table and all joined tables:

There is a "Remove" control at the bottom of the list:  only the most recently added table can be removed.    Removing the table also removes all fields from that table from the Columns screen.    You can only remove tables one at a time and only the 'bottom' table can be removed.  

What if I picked the wrong base table?

If the very first table you selected - the base table - turns out to be the wrong one, the best thing to do is delete the report and start over from scratch.

3. Adding Columns

You can add columns to your report via the "Add Columns" control on the Columns screen:

Adding Columns from the Base Table or a Joined Table

The "Add Columns" dialog will contain a separate control for the base table and each of the joined tables:

To mass add columns from any table, click on that table's "Columns from the .... table" button.  This will bring up a "select columns from" dialog for the selected table:

THe available fields are listed in the following order in the dialog:

- The key fields are listed at the top and identified by a key icon.

- The 'real' fields in the table are listed next in alphabetical order

- The 'magicGetter' fields are listed below the real fields, again in alphabetical order (so the alphabetical order repeats itself for the magicGetters.  The magicGetter fields are identified by magic wand icons.  (What are magicGetters?  see below).

You can check off all of the fields you want to add: there is no limit.  When you are done, click the "Add" button to add all of the checked fields to your report.   They wil be added in the order in which they appear in this control (i.e. alphabetical order).  You can change their order later.

Adding Magic Getter Columns

MagicGetters are data items in a table (e.g. the Staff table) that appear in the table in the ReportWriter but are not really in that table when it is stored on a storage device (i.e. in the actual database).   In the table schema, magicGetters are always identified by the magic wand icon:

When the table is brought into the ReportWriter, multiple automatic extra queries are done to bring in the magiczGetter fields.  In order to do those silent background queries, the object IDs of all of the items are needed.  If any are missing, you may get the following error message displayed at the top of the Columns screen:

To fix this, simply click the "Add Missing Primary Key Coloumns" button.  This will display the following dialog:

 Click "OK' to add the missing columns - the columns will be added and the error message will go away.

Note that you do not need to print every Column - much data will be in your report for 'control' purposes and can be omitted from the output.

Adding Linked Columns

To add a single column from another (non-joined) table, use the "Lookup a value from another table" control.  When you click that control an intial Add Look Up Column dialog is displayed:

You must supply the 'code' for the column and the "Display As" (or column header).  The "Seq" number places the new Expression column into the list of existing columns.  The number displayed will add the column at the bottom of the existing set of colukmns, however you can enter any other number and add the column wherever you need or want it to be located in the list.  You can always change the columns sequence # (and thus position in the list) later.

When you have entered your Code and Display As/column header, click Add:

Clicking Add immediately brings up the "Lookup Data" screen where you define the one field you want to link in and where/how to get:

For a full discussion on linking columns, please refer to the "Linking Data" article.

Adding Expression Language Columns

To add an "Expression" column (a column containing an Expression Language expression), use the "An Expression" control::

When clicked, this brings up a "add column" dialog for an Expreesion column:

You must supply the 'code' for the column and the "Display As" (or column header).  The "Seq" number places the new Expression column into the list of existing columns.  The number displayed will add the column at the bottom of the existing set of colukmns, however you can enter any other number and add the column wherever you need or want it to be located in the list.  You can always change the columns sequence # (and thus position in the list) later.

The column header will be able to be changed later - the code will not be able to be changed but it is only used internally:

To add the column, click "Add".  This brings up the "Modify Column" screen for the Expression Column:

The "Expression Column" screen lets you edit the Expression column.  The controls at the bottom provide quick ways to add existing columns and/or existing parameters to the Expreesion.   The Expression itself will be located in the "Expression" field.  This field initially appears small, but can be expanded to any size by the 'drag' control at its lower right.  It is essentially unlimited in size.    The "Notes about this expression" field allow you to annotate the Expression (and leave notes to yourself for the future).

For more information on Expressions and Expression columns, please see the Expression Language in the ReportWriter article.