advanced reporting tool · advanced reporting tool manual page 10 of 70 report designer the report...

Advanced Reporting Tool Manual

of 70

Advanced Reporting Tool

The Advanced Reporting tool is designed to allow users to quickly and easily create new reports or modify existing reports for use in the Rewards system. The tool utilizes the Active Reports utility to present reports in a PDF format. The Advanced Reporting tool was intended to be intuitive and easy to use.

The Advanced Reporting consists of the following tools:

Report Selector

Report Designer

Function Builder

Conditional Field Formatting

Sort / Group By

Report Filters

Hidden Fields

Report and Field Attributes


of 70

Advanced Reporting Tool ..................................................................................1

Report Selector ...............................................................................................5

Report Attributes.............................................................................................7 Report Name........................................................................................................................... 7 View ........................................................................................................................................ 7 Paper Size............................................................................................................................... 8 Default Font............................................................................................................................. 8 Field Spacing .......................................................................................................................... 8 Use.......................................................................................................................................... 8 Actions .................................................................................................................................... 8

Report Designer ............................................................................................10 Field Tree .............................................................................................................................. 11 Expanding / Closing the Category Branches ........................................................................ 11 Dragging Fields to Report Design Screen ............................................................................ 13 Trash Can ............................................................................................................................. 15 Placing a Field between 2 other Fields ................................................................................. 16 Positioning a Field after it’s been dragged onto the Report Design Screen ......................... 16 “Text Boxes” Category .......................................................................................................... 17 Centerline Snap .................................................................................................................... 18 Adjusting Header Line and Footer Line ................................................................................ 18 Fields off the right of the Report Design Screen................................................................... 19 Default Report Objects.......................................................................................................... 20

Field Attributes..............................................................................................21 Changes affect both the Window and the Report Designer ................................................. 22 Width / Height can be changed by Slider or Text Box .......................................................... 22 Borders.................................................................................................................................. 23 Font ....................................................................................................................................... 23 Font Color / Background ....................................................................................................... 23 Font Size ............................................................................................................................... 23 Text ....................................................................................................................................... 23 Formatting ............................................................................................................................. 23 Wrap Text.............................................................................................................................. 23 Hide Field .............................................................................................................................. 24 Closing the Field Attributes Window ..................................................................................... 24

Calculated Fields...........................................................................................25

Function Builder............................................................................................27 Enter Name for Calculation................................................................................................... 27 Use (Public / Private) ............................................................................................................ 27 Equation box ......................................................................................................................... 28 Clear Options ........................................................................................................................ 28 Builder ................................................................................................................................... 28 Function Tree........................................................................................................................ 28 Field Tree .............................................................................................................................. 28 Getting Started with the Function Builder ............................................................................. 28 Activate a Field...................................................................................................................... 29 Field Type and Validation ..................................................................................................... 30 Embedding Functions within Functions ................................................................................ 32 “Clear Above” Revisited ........................................................................................................ 32 Structure – The IF Statement................................................................................................ 33


of 70

Logical Category – Tests ...................................................................................................... 34 Embedding an IF function in another IF function .................................................................. 34 Keyboard Entry of Values ..................................................................................................... 34

Calculated Fields Continued… ....................................................................35 Public or Private .................................................................................................................... 35 Calculated Field Owner......................................................................................................... 35 Impact of Public / Private Flag .............................................................................................. 36

Conditional Field Formatting .......................................................................38 Function Builder .................................................................................................................... 39 Fields past Right Margin – Text Boxes (Spacer) .................................................................. 39 Attributes ............................................................................................................................... 40 Hide Field Attribute ............................................................................................................... 42 Table of Conditions and Formats.......................................................................................... 43

Sort / Group By..............................................................................................45 Enter Name for Sort / Group By............................................................................................ 46 Make Active?......................................................................................................................... 46 Hide Detail Lines?................................................................................................................. 46 Use, Private, Public, Required .............................................................................................. 47 Fields on Report.................................................................................................................... 47 Options – Add Row ............................................................................................................... 47 Table ..................................................................................................................................... 47 Moving a data field to the Table............................................................................................ 48 Ascending / Descending Order ............................................................................................. 48 Reorder tool in Table ............................................................................................................ 49 Section / Page Break ............................................................................................................ 49 Group Function ..................................................................................................................... 50 User Defined Function .......................................................................................................... 50 Group Function Builder ......................................................................................................... 51 Labels.................................................................................................................................... 51 Deleting a Row from the Table ............................................................................................. 51 General Rules ....................................................................................................................... 52

Filters .............................................................................................................53 Enter Name for Filter............................................................................................................. 54 Make Active?......................................................................................................................... 54 Use, Public, Private, Required .............................................................................................. 54 Expression Builder ................................................................................................................ 54 Options.................................................................................................................................. 55 Not in the Expression Builder, Operator ............................................................................... 56 Lookup Values in the Expression Builder, Value.................................................................. 56 Stringing Expressions Together............................................................................................ 56

Hidden Fields.................................................................................................58 Enter Name for Hidden Fields............................................................................................... 59 Make Active?......................................................................................................................... 59 Use........................................................................................................................................ 60 Hide Table............................................................................................................................. 60

Paper Size ......................................................................................................61

Calculation Maintenance ..............................................................................62

Report Designer, Saving to the database ...................................................63

Trash Can, more on Deleting Fields ............................................................64


of 70

Sort / Group By – More on Group Function Builder...................................65

Function Builder – the Differential Category ..............................................67 Differential ............................................................................................................................. 68 Status .................................................................................................................................... 68 Incumbent Weighted Average Flag (Inc Weight Flag) .......................................................... 69

Sort / Group By Average vs Actual Average...............................................70


of 70

Report Selector

The Report Selector is the first page the user will be served when accessing the Advanced Reports. This tool has the following functions:

Access existing reports from the various Report Categories.

Create new reports from the “New” button.

Create or apply Sort / Groups, Report Filters, Hidden Fields and Page Size criteria when running reports. These options are available in the 4 tables at the bottom of the page.

Run existing reports from the “Run Report” button.

Download the data in the report into a CSV file by clicking on the “Download CSV File” button.

For reports that have great amounts of data and take long to process, the “print in background” option is available.

Access the Report Designer from the “Designer” button.

Change the Report Attributes of the currently selected Report from the “Attributes” button.


of 70

Access Calculated Fields through the “Calculations” button.


of 70

Report Attributes

From the Report Selector, clicking on the “New” button located at the top of the page or clicking “Attributes” with a selected report will serve the following page:

Basic information about the report is requested. This information is required for report creation and before any other aspects of the report can be developed. The following attributes and how they affect the report are discussed below:

Report Name This is the name of the report as it appears on the Report Selector page. If many reports are expected to appear on this page, a good naming convention will help users quickly locate their reports. The Report Name can be changed at anytime. However, the report name can not be left blank.

View The available options of Employees, Jobs, Structures, Market Movement and Surveys determine what data fields from Reward are available for the report. A View can be changed at anytime, until fields are added to the Report Designer. Once fields are added to the Report Designer, changing the View will require the removal of those fields before a change will be allowed. Default value of the View is set to “Employees”.


of 70

Paper Size This is a database driven select box and any entries can be included. Current selections include Letter (8.5 x 11), Legal (8.5 x 14) and A4 (8.27 x 11.69). The Landscape / Portrait options allow the user to determine if the short side of the page (portrait) or the long side of the page (landscape) will be at the top. Selections of Paper Size and Portrait / Landscape will be simulated in the Report Designer and Active Reports will use those selections when creating the PDF format. These options can be changed at any time. Default is set to “Letter” and “Landscape”.

Default Font This will determine the default font used in the Report Designer. This can be changed at any time. However, if any fields are currently on the Report Designer, those fonts will not change. This affects only new fields that are added after changing the default font. Currently, 4 fonts are offered, Arial, Courier New, Times New Roman, and Verdana. Default is set to “Arial”.

Field Spacing This attribute represents the spacing between columns on the Report Designer. The range that can be entered is between 1 and 20. This scale is not representative of any real measurement, but one can assume that 8 is equivalent to one character. This attribute can be changed at any time and will affect all column spacing on the report. Default is set to “10”.

Use Use determines if a report is available for private or public use. If the Private option is selected, only the user who created the report can run it. If the Public option is selected, then all users in Reward can run the report. In both cases, only the user who created the report (the owner) can modify the report.

Actions Save – This action will save entries / changes to the database. If a

“Report Name” has not been entered or is blank, then a warning will pop up, alerting the user that a “Report Name is required”. Once the page is saved, the window will close.

Cancel – This action will close this window without saving.

Copy – This action creates a copy of the selected report. When the Copy button is clicked, a message box will appear, asking the user to “Select a User to own Copied Report”. Towards the bottom of the window, a select box will appear with a list of user names in Reward. The user will need to select one of the users to become the owner of this report. Once they select the owner, they can click on the Copy button again and a copy of


of 70

the report will be created, with the selected user as the owner. The window will then close.

Delete – This action will delete the selected report. This action performs a soft delete. Therefore, the report will not actually be deleted from the database, but rather the report name will not be available on the Report Selector page. Once the delete button is clicked, a message box will appear, asking the user “Are you certain you want to delete this report?” The user can click “OK” and the report will be soft deleted. If the user clicks “Cancel”, then the message box will disappear, leaving the user at the Report Attribute window.


of 70

Report Designer

The Report Designer is where the bulk of the work is performed when creating a new report. The data that will appear on the report and how that data will appear is accomplished on this page. Additionally, the tools Function Builder and Conditional Field Formatting are accessed through this page.

The Report Designer is comprised of several tools:

Field Tree – this is the list of categories on the left and contains branches of data fields.

Report Design Screen – this is the area to the left of the Field Tree, and contains the different sections of the report (header, body and footer). Different types of fields will be placed in the appropriate sections of the Report Design Screen.

Trash Can Icon - this icon is where data fields are put that are no longer needed on the Report Design Screen.


of 70

Field Tree The Field Tree is a list of available data fields for the report. This list is determined by the View selected in the Report Attribute page. The following categories are displayed with the selected Views

Employees – Employees, Pay, Jobs, Structures, Incumbent Statistics, Job Matches, Advanced Matches and Market Data

Jobs – Jobs, Structures, Incumbent Statistics, Job Matches, Advanced Matches and Market Data.

Structures – Structures

Surveys – Market Data and Market Data Pay

Market Movement – Market Movement

Categories “Text Boxes” and Calculated Fields are always available regardless of which View is selected. These special categories will be discussed in more detail later in this section.

Expanding / Closing the Category Branches The user can open and close the different categories of fields simply by clicking on one of the categories. For example, clicking on the “Structures” category will open that branch and reveal all the fields related to that category.

The individual data fields in the Structure category appear in green.


of 70

If the user wants to open a different category, they simply click on that category. Any categories that are already open will close, and the clicked category branch will expand out. For example, if the user clicks on “Pay”, “Structures” will close and “Pay” will expand out.

All the category branches operate in this fashion, opening and closing as the user clicks on them. If an open category is clicked on, that category will then close.

This toggling of categories, opening and closing, allows the user to view the data fields with minimal scrolling.


of 70

Dragging Fields to Report Design Screen Once the user finds the fields that they want for the report, they can drag the green field onto the Report Design Screen. Select a field by clicking the mouse down on the field and drag the field to the report (past the vertical line). Notice when the field is dragged from its branch, it leaves a white background place holder behind.


of 70

When the field is dragged past the vertical line, onto the Report Design Screen, let up on the mouse button and the field will snap into place.

Since a data field was pulled onto the Report Design Screen, Report Designer creates a column header object and a body object for this field. Each object (column header and body) can be managed separately, from an attribute perspective. This will be discussed in more detail under Field Attributes.


of 70

As more and more fields are dragged to the Report Design Screen, notice that the Pay category branch has white background placeholders where the fields were dragged from.

This will give the user quick feedback as to what fields have been dragged to the report. Additionally, notice the spacing between the “Compa-ratio” and “Salary Division” objects.

The space between the 2 boxes is the Field Spacing inputted into the Report Attribute page. All fields will have equal spacing between them, as determined by the Field Spacing attribute.

Trash Can Fields that are not needed on the Report Design Screen can be dragged to the trash can icon. Dragging a field to the icon and letting up on the mouse button will remove it from the Report Design Screen. If a data field has been “trashed”, then the white background placeholder will turn back to a green color, indicating to the user that the field is available to drag and drop onto the Report Design Screen again.

To delete a data field, the user must drag the column header object. This action will delete both the column header object and the body object.


of 70

Placing a Field between 2 other Fields When dragging a data field on to the Report Design Screen and the user wants to place it between 2 existing fields already on the report, the user can drag and drop the new field anywhere between the midpoints of the 2 existing fields. If dropped in this fashion, the new field will snap into place between the 2 existing fields.

If the user wants to place a field at the beginning of the report (to the left of “Pay”), drop the new field to the left of the midpoint of the “Pay” object.

Positioning a Field after it’s been dragged onto the Report Design Screen Changing the order of the fields in which they appear on the report is easily accomplished with the ability to drop fields between others. Even after a field is placed on the Report Design Screen, it can be dragged to another position.

The technique is the same, click on the field and drag it between the midpoints of the 2 other fields. Releasing the mouse button will drop it between the 2 fields. The other fields to the right of the drop will automatically move over, making room for the newly positioned field.


of 70

“Text Boxes” Category The Text Boxes category is different from the data field categories in that the same field can be dragged and dropped onto the Report Design Screen multiple times. The data field categories can only be used once.

The Text Boxes are user defined fields and the user can type whatever they want to display in these boxes. The Text Boxes are not linked to any data fields and are strictly for providing the user the means to enter static type of data. Text Boxes can be used for Report Titles, sub titles, any disclaimers, display author name or any other text that the user may want to display on the report.

Header Text Box and Footer Text Box are used to display static data in those report sections. When dragged to the report, Report Designer keeps these objects in their respective areas. For example, if the user drags a Header Text Box into the Header, that box can be placed anywhere in the Header Section. If the user tries to move it below the header line or above the page line, Report Designer will snap it back into the Header Section, keeping the complete box inside of the section.

Spacers are used strictly for creating a space between data field objects and at this time, provide no other functionality.

Header Text Boxes and Footer Text Boxes allow for much more user control than the data field objects. They can be placed anywhere in their respective sections, can overlap each other, and can be of just about any size the user wants. In contrast, data field objects are heavily controlled by Report Designer, keeping the Column Header objects on top of the Header Line and the Body objects directly below. Column Spacing is kept consistent by Report Designer and other than the use of Spacers, no deviation is allowed.


of 70

Header Text Boxes, Spacers and Footer Text Boxes are dragged and dropped the same way as data fields. Spacers can be re-ordered like data fields.

Centerline Snap Since Header Text Boxes and Footer Text Boxes can be moved anywhere in their respective sections, the user may also want an exact method to center these boxes on the report. The Centerline Snap can assist the user to place these text boxes exactly in the center of the report.

While the user is moving a Header Text Box or a Footer Text Box, if the midpoint of the text box is close to the center of the report, a red Centerline, the length of the report, will appear at the report’s center. Once the Centerline is visible, the user can release the mouse button and the text box will snap, moving the center of the textbox equal with Centerline.

The centering has the best affect when the text alignment of the text box is set at “Center”.

Adjusting Header Line and Footer Line The line between the Header Section and the Body Section is referred to as the Header Line. Likewise, the line dividing the Body Section and the Footer Section is called the Footer Line. These lines are set to a default position when a new report is created. However, if the user needs to adjust these lines up or down, clicking on the “Header Section” or “Footer Section” labels will allow them to do so.


of 70

Clicking on either will display an up arrow, close box and down arrow interface. As expected, clicking on the up arrow will move the selected line one increment per click. Clicking on the down arrow will move the selected line down. Clicking on the close box will hide this interface. The user can display the interface at any time, just by clicking on either of the labels again.

Fields off the right of the Report Design Screen It is possible to put more fields than can print on the report.

If there’s not enough room to fit all the data fields you’d like to display, adjust the paper size, making sure that you have the settings you want. Landscape will print more columns on a page, but not as many rows.

Make the widths of the data fields smaller, and this will allow for more fields to fit in the printable area. Also adjust the Field Spacing on the Report Attribute page, 1 is the smallest gap possible between fields.

Any fields that do end up right of the Report Design Screen (where the horizontal lines end) will not print on the PDF format. If a field is partially on the report, only the portion that is on the report will print.

Later, when the Function Builder and Conditional Field Formatting are discussed, having data fields off the right of the report can become a useful technique.


of 70

Default Report Objects When a new report is created, Report Designer will add several Default Report Objects to the Report Design Screen. They have special processing attached to them and work with setting within Reward.

Database Name – displays the database that the user is associated to.

Pg x of xx – Works with Active Reports and displays the page count on the report.

xx/xx/xx – Displays today’s date.

Scoreboard Values – Displays the scoreboard settings of the user.

Enter Report Title – Starts the user off with an object to enter a Report Title.

These Default Report Objects react differently compared to a data field or text box. If they are trashed, they will re-appear in their default spot the next time the report is reloaded. Enter Report Title cannot be trashed. Scoreboard Values will print an additional box below the Scoreboard Attribute label at run time.

However, the user can place these Default Report Objects where they choose (within the respective sections) and also apply attributes to them. If the user does not want the Default Report Object to display on the report, they will need to move it to the right of the right margin.


of 70

Field Attributes

The Field Attributes Window allows users to determine the display attributes for all the objects on the report. These attributes include:

Font Family

Font Size

Font Color

Bold, Italic

Background Color

Wrap Text / Truncate

Field Width

Field Height

Field Border

Left, Center, Right Field Alignment

Formatting ($, thousand separator, decimal points)

Hide Field


of 70

Double clicking on any report object will bring up the Field Attributes window

Changes affect both the Window and the Report Designer A feature of the Field Attribute is as changes are being made in this window, the user can view the results on the Report Designer page. If the user clicks on the “Italic” checkbox, they will see the “Pay” column header on the Report Designer turn to Italic at the same time. They will also see the “Pay” example change in the window as well.

Width / Height can be changed by Slider or Text Box Concerning the width and height of the report object, the user can drag the slider on the vertical and horizontal bars, or they can choose to enter a value into the text box. Either option will adjust the width / height accordingly.

The Height attribute is not offered for Body objects, as their height is preset by the Report Designer.


of 70

Borders A border can be placed around a report object. Four colors are available; red, black, green and blue.

Font Fonts available are Arial, Courier New, Times New Roman and Verdana.

Font Color / Background By clicking any of the 16 colors in the 4 x 4 grid will change the colors in the report object. One scenario to be cautious of is having the same color for Font Color and Background as the data will not be visible.

Font Size Font sizes are available from 8 pts to 72 pts.

Text The “Text” box controls what text displays on the report for the report object. For Header Text Boxes, Footer Text Boxes and Column Headers, this can be changed by the user. For Body objects, the user can not change this because this is where the actual data from the Reward system will display.

Formatting The formatting option is available for Body objects only. For data fields that contain dates or string data, general formatting is applied and no options are offered to the user. For data fields that contain numeric, percentage or currency, the user can control the following:

If a thousand separator is displayed

If negative numbers are displayed with the negative sign or the number in parenthesis.

If a percent sign is displayed (if this option is checked, the thousand separator and parenthesis is automatically turned off).

How many decimal points will display

The Scoreboard setting will take the selected value of the user running the report from the Preferences, Scoreboard, Rounding.

Wrap Text Default value is checked, to wrap text. If the number of characters in a data field is longer than can fit in the width of the object, with Wrap Text set on, the characters will wrap to another line, displaying all the data. With Wrap Text off,


of 70

once the end of the object is met, no more characters will display, truncating the data, no wrapping to another line and showing only a partial view of the data.

When the PDF format is generated and Wrap Text is checked, the field will not increase in width, but will increase in height to accommodate all the data in a field.

Hide Field Hide Field is another attribute that will be discussed in the Conditional Field Formatting Section.

Closing the Field Attributes Window When the user is done with their changes, they can choose to click the “Close” button on the Fields Attributes Window, or they can click anywhere on the Report Designer. Either action will close the Window and allow the user to work in the Report Designer.

The Field Attributes Window does not have a “Save” button. Any changes made in the Window are actually stored in the Report Designer. The next time the Report Designer page is saved, changes made in the Attribute Window are saved to the database.


of 70

Calculated Fields

Calculated Fields is a powerful tool of Advanced Reporting. This tool gives the user the capability of creating their own data fields from calculations based on other existing data fields. Once a calculated field is created, it is made available to other reports that share the same View as selected in Report Attributes.

In the Report Designer, clicking on the “Calculated Fields” branch will open up as follows:

Drag “Add Field” onto the Report Design Screen and place the field as you would any other data field. Double clicking on the newly added “Add Field” object will open up the Field Attributes window.


of 70

Report Designer knows that this is a Calculated Field and offers the Calculation box below the Field Name box.

The Calculation box will display “Click here to Create a Calculation” or the actual Calculation if one has already been created for the field. Clicking on the “Click here to Create a Calculation” will take the user to the Function Builder Page.


of 70

Function Builder

The Function Builder has a number of components to be aware of:

Enter Name for Calculation

Use (Public / Private)

Equation box

Options: Clear Field, Clear Above, Clear All

Builder

Function Tree

Field Tree

Enter Name for Calculation As the user creates more and more calculations, a good naming convention for the calculations is important. This name will display in a list of other Calculated Fields, so knowing how to distinguish between the different Calculations will make them easier to select from this list.

Use (Public / Private) This is similar to the Use flag in Report Attributes. When set to Private, only the owner will be able to use the Calculated Field. When set to Public, others can use it. And as before, only the owner can modify the Calculated Field. More discussion on this will follow.


of 70

Equation box The Equation box will display the equation of the Calculated Field as the user builds it. It shows a cleaner form than that of the Builder and makes it easier for the user to read the equation.

Clear Options As functions and fields are added to the Builder, the “Clear” Options will allow the user to clear out specific pieces of the equation.

Clear Field – Clear Field will clear out the active field (active field will have a green background)

Clear Above – Clear Above will clear the field above the active field. This is only applicable if fields are nested in one another. If only one function is added to the Builder, and one of the fields is activated, clicking on this is equivalent to the “Clear All” option.

Clear All – Clear All will clear the entire contents of the Builder. This option is good if you just want to start over.

Builder Builder contains all the Functions and Fields as the user builds them.

Function Tree This tree is similar to the Field Tree on the Report Designer, however, the categories are of equation types and the fields are actual equations. Clicking on the different Category branches will expand / close just like the Field Tree on the Report Designer. The functions listed under each Category are typically used functions in reporting. Clicking on one of the functions will move it to the Builder, no need to drag and drop.

Field Tree The Field Tree is exactly the Field Tree used in Report Designer, however, it’s on the right, not the left side.

Getting Started with the Function Builder The user will typically start with a blank Builder. The first component will need to be a Function from the left side.

Clicking through the different categories will open and close the available function branches, until the needed function is found.


of 70

Clicking on a Function will move it to the Builder. For example, the user opens the “Math” branch, and clicks on “Add”. This action will move the following to the Builder.

Under the Builder bar, (_+_) appears. The purple boxes are fields to populate with either another function, fields from the Field Tree, or the user can type in values as well.

Activate a Field After the first function is added to the Builder, any subsequent additions must be made to an Activated Field. To activate a field, click on it to turn it green.

Once the field is green, it is ready to accept either another function, field or input from the keyboard. If the wrong field was activated, clicking on the desired field will de-activate the first field and activate the desired field.


of 70

Field Type and Validation Once a field is activated, it’s ready to accept an entry. In the above example, the equation is the addition of two numbers. If the user inputs a string value, into the activated field, at some point, this would cause an error in the reporting process. It’s important for the user to know what type of data the field is expecting and what type of data they may be moving into the field.

By moving the mouse over the activated field, a bubble will display the field type, as shown in the example below.

If the user is unclear what type of data should be moved into the activated field, mousing over the field will alert them of the data type.

Additionally, if the user is unclear how a function works, mousing over the function will provide a brief explanation of what the function does.

The bubble describes that field 1 is expecting a number, field 2 is expecting a number and the result will return a number.


of 70

The same scenario exists with the fields from the Field Tree. If the user is unsure of what data type the field may be, they can mouse over the field and get that information.

Any of the fields that the user is unsure of can be checked by mousing over it.

The user decides to move “Pay” into the Activated field. By clicking on the field in the Field Tree, the “Pay” field will automatically move to the activated field.

The user can now activate the second field, clicking on it and turning it green. However, let’s say the user decides to incorrectly add a string field to this activated field. When this occurs, a message box will pop up, warning the user that this is incorrect.


of 70

The user tried to move the “Name” field to the activated field, but “Name” is a string field, the activated field is expecting a numeric field.

Embedding Functions within Functions With the above example, another Add function can be moved to the second field of the original Add function. The result would appear as follows:

The user can activate the 2 fields in the new Add function and continue to add more functions or fields to the Builder.

“Clear Above” Revisited If the user changes their mind and wants a Subtract function in place of the 2nd Add Function, they would activate one of the Add fields, click on “Clear Above” and that would clear out the Add function.


of 70

Structure – The IF Statement Under the “Structures” category on the Function Tree, lies 3 types of “IF” statements. The “IF” statement is an “if then else” type of structure and depending if the test portion of the “IF” statement returns true, the statement will either return the “THEN” value or the “ELSE” value. When using the IF statement, the THEN and ELSE values must return like data types. If the THEN is returning a numeric value, the ELSE must return a numeric value as well.

Advanced Reporting offers 3 IF statements to address the 3 different data types available. The user can select from the 3 options, depending on what type of data the equation is dealing with.

In the above case, where the Activated Field is expecting a number, the user should select the “If (number result)” function. Clicking on that function results in the following.


of 70

Logical Category – Tests The IF function utilizes the Logical category for the IF portion. The Logical category contains Tests. The Tests will compare one value to another, and determine if the comparison is true or not. Again, if the test is true, the THEN portion of the IF statement is returned, false would return the ELSE portion.

The above illustration shows the available Test Functions under the Logical category. Also notice that the field after the IF portion is expecting a Test result. The only functions that return a Test result are those under the Logical category.

In a nutshell, the field after the IF portion can only accept a Test function. The Test function can only be used in the field after the IF. Test functions can not be used in any other functions.

The AND and OR functions are available under the Logical category and allows the user to perform multiple tests. The AND and OR accepts only Tests functions as well.

Embedding an IF function in another IF function IF functions can be embedded within each other. As long as the user selects the correct data type that the IF returns, embedding IF statements will work.

Keyboard Entry of Values Users can type values into activated fields. Currently, the Function Builder performs no validation on the value being entered. Users will need to learn that numeric fields can only accept numbers, and string fields will require an apostrophe on each end of the string. More development on Keyboard Entry Validation will occur in the future.


of 70

Calculated Fields Continued…

After the equation is built, the user can give the equation a name and save. Clicking on the “Save” button will save the equation to the database and return the user to the Report Designer. For example, the user named their equation “Pay + Allowances” and clicked the “Save” button. The user is returned to the Report Designer, and clicks on the “Calculated Fields” branch. That category expands to reveal the saved Calculated Field “Pay + Allowances”.

This Calculated Field is now stored in the Calculated Field database. This Field will be available to any report that utilizes the same View as selected in the Report Attribute page.

Public or Private When a user creates a Calculated Field, a flag exists that can be set to either Public or Private.

Public - Setting the flag to Public will allow the Calculated Field to be available to other users in Reward. If their report is set to the same View as the Calculated Field, they will see the Calculated Field in the Field Tree on the Report Designer and the Field Tree in the Function Builder.

Private – Setting the flag to Private will restrict the field to be available only to the user who created the field. Any other reports that share the same View as the Calculated Field will have the Calculated Field in the Field Tree on the Report Designer and Function Builder. No other users will have access to Calculated Fields that are set to Private.

Calculated Field Owner The owner of a Calculated Field will be the only user who can modify that field. However, if the flag is set to Public, other users will be able to view the


of 70

Calculated Field in Function Builder, but they will not be able to activate any fields for modification or “Save” the Calculated Field.

Impact of Public / Private Flag Scenario 1

Assumption: Calculated Field “Pay + Allowances” is set to Public, a new report, created by “Another User”, has the same View as selected in the Report Attribute page as the Calculated Field.

If “Another User” (different from the user who created the Calculated Field, “Pay + Allowances”) pulls that field onto the Report Design Screen, they will get the Calculated Field created by the owner.

If this user saves the report, and clicks “Run Report”, the result of the Calculated Field will be the same as the owner created.

If the owner modifies the equation of the Calculated Field, the next time the “Another User” runs the report, they will receive the modified results of the Calculated Field.

Our “Another User” didn’t have to make any modifications to their report, do anything changes or saves to receive the changes made by the owner. Other users will receive the changes to the Calculated Field instantly.

Other users can view Public Calculated Fields in the Function Builder, however, only the owner can modify the Calculated Field. Only the owner will be offered the “Save” button in the Function Builder.

Caution

The Public / Private flag of the Calculated Fields is a powerful and flexible feature. However, the owner of the Calculated Field that is used in many other reports needs to be cautious as to what changes they make to the Field. Such changes could adversely affect other’s reports.

Scenario 2

Assumption: Calculated Field “Pay + Allowances” is set to Public, a new report, created by “Another User”, has the same View as selected in the Report Attribute page as the Calculated Field.

“Another User”, instead of pulling the Calculated Field onto the Report Design Screen, drags “Add Field”. They navigate to the Function Builder and from the Field Tree, they click the Calculated Field “Pay + Allowances” to the Builder. They get the same equation as built by the owner in the Builder. They name and save their Calculated Field.


of 70

In this scenario, the “Another User” has actually created a new Calculated Field. If the owner of “Pay + Allowances” makes any changes to their Calculated Field, those changes will not propagate to the “Another User” Calculated Field. Even though the “Another User” has the same equation as the Public “Pay + Allowances”, changes made to it will not find its way to this new Calculated Field.

Caution

Where in Scenario 1, changes made to Public Calculated Field propagate instantly to other reports that contain that field, Scenario 2 offers none of this functionality.

Difference between Scenario 1 and 2

In Scenario 1, users are pulling the Calculated Field onto the Report Design Screen. Scenario 2 involves creating a new Calculated Field and pulling the equation into the Calculated Field.

Each time a Calculated Field is created (if the user drags “Add Field” to the Report Design Screen), a new Calculated Field ID # is created for it.

Scenario 1, “Add Field” wasn’t dragged onto the Report Design Screen, and the Calculated Field, “Pay + Allowances” had a Calculated Field ID # associated to it. Scenario 2, “Add Field” was dragged onto the Report Design Screen and a new Calculated Field ID # was assigned to it. The Scenario 2 field has no association to “Pay + Allowance”, even if the equation may be the same, and therefore, cannot receive any instant updates from “Pay + Allowance” changes.


of 70

Conditional Field Formatting

Conditional Field Formatting is a useful tool that allows the user to display data on the PDF output in a different format, if certain criteria are met. For example, if the user puts the data field “Pay” on the report, the user can configure the report so that if the value of “Pay” is greater than $100,000, to print that value in a blue color font. Multiple conditions can be put on the same data field, so the user can also add if the value of “Pay” is less than $20,000, then print that value in a red color font.

The Conditional Field Formatting page consists of several tools already discussed:

A Function Builder, similar to the Function Builder for Calculated Fields.

Attributes, similar to the Field Attribute window

A table of Conditions and Formats for the Field

Only body objects are eligible for Conditional Field Formatting. The Conditional Field Formatting page is accessed through the Report Designer, clicking on any of the report objects in the Body Section. This will pull up the Field Attribute window. At the top of this window is a box label “Cond Format”, and clicking on this will serve the Conditional Field Formatting page.


of 70

Function Builder The Function Builder is similar to that of the Function Builder for Calculated Fields. However, a few differences exist:

The Function Builder starts off with the IF function. This is a requirement for Conditional Field Formatting. The first function that is processed needs to be a conditional statement like the IF function.

The Structure category is not available in this Builder. Advanced Reporting is not able to process nested IF functions in Conditional Field Formatting.

In the Field Tree, only data fields that have been dragged onto the report are available for the Builder. Due to how Advanced Reporting processes the Conditional Field Formatting, only fields that are on the report can be used in the conditioning.

Fields past Right Margin – Text Boxes (Spacer) With the Field Tree limited to fields dragged onto the report, it seems to be quite limiting to building functions. However, as earlier discussed in the Report Designer, fields can be pulled onto the report, past the right margin. Any fields that are past the right margin will not print on the final PDF format.


of 70

If any data fields are needed in the Conditional Field Formatting equation, but those data fields should not display on the report, the technique of putting those fields past the right margin can be used. If the report does not have enough data fields on it to place other data fields past the right margin, the Spacer field under the Text Boxes category can be used.

The Spacer can be placed at the end of the report (the right end, that is), and any fields needed for Conditional Field Formatting can be placed to the right of the Spacer. Remember, the Spacer field width can be adjusted from the Field Attribute window to assure that any fields placed to the right of it, will be off the report.

Attributes The Attributes for the Conditional Field Formatting is a subset of what is available on the Field Attribute window.

As the user builds the condition in the builder, they can determine what Attributes will be different if that condition is met. The example given before is a good illustration of this.

Normally, the Pay field will print in a black color. With the Conditional Field Formatting, if the Pay value is greater than $100,000, the user can set that value to print in red.


of 70

In the Builder, “PaySample” is compared to 100,000. If this condition is met, then the font color will appear in red. The box “Format for PaySample” shows an example of what formatting currently exist on the field and what formats would be applied to the field if the condition is met. The middle row where the font color is red shows this formatting.

The other formatting attributes can be used as well:

Font Size

Border

Bold, Italic

Font Family

Font Color

Background


of 70

Hide Field Attribute The Hide Field attribute is one attribute that is normally not used in general formatting of a field. The Hide Field attribute, when checked, will prevent the field from displaying on the PDF format. Later in this document, the Hidden Field tool will be discussed. The Hide Field attribute and the Hidden Field tool are different in how the field reacts to each. The Hide Field attribute will stop the field from displaying on the report, but the column itself will take up space on the report, leaving an empty space where the column would normally display. The Hidden Field tool will also stop the field from displaying, but the fields to the right of it will be shifted over so there are no empty columns.

What good is the Hide Field attribute? When used in conjunction with the Conditional Field Formatting tool, it can create a dramatic result on the PDF format. If the majority of values in a data field are similar, apply the Hide Field attribute. Then, in Conditional Field Formatting, create a Conditional Format that displays the field (Hide Field unchecked) if the value of that field is different.

For example, (and a poor example at that), if the Report Filter (to be discussed later) were set to retrieve records where the last name is like Smith. For the majority of records, the last name would be Smith, but there would be some records where the last name contained the string “Smith”. Conditional Formatting could be applied to the Last Name field to not display, unless the name was something other than Smith. Added to the example below is make the font red and bold.


of 70

Table of Conditions and Formats Once the equation in the Builder is completed and the desired formatting is obtained, the user must now move this Conditional Formatting to the Table. This is accomplished by clicking on the “Move” button under Options.

The Equation built in the Builder and the formatting applied is moved to the Table when the “Move” button is clicked. When the “Save” button is clicked, any Conditions and Formats in the Table will be saved to the database.

If one of the Conditions and Formats in the Table is deemed not needed, clicking the red “X” will delete the Condition and Function from the Table.

If one of the Conditions and Formats requires modification, clicking on the “Pencil” icon will move the Condition up to the Builder and the Format to the Attributes. At that point, either the Condition and / or the Attributes can be modified. Once modification is done, the user can click on the “Move” button to replace the original Condition and Format with the modified one.

When a Pencil is clicked, the Pencil’s background will turn blue, indicating to the user which row is active (being modified). If the wrong Pencil was clicked, the user can click on the correct Pencil, and that row will become the active row. Clicking on an active row will deactivate it.

The important thing to remember is that only Conditions and Formats that reside in the Table will be saved to the database when the “Save” button is clicked.


of 70

Once the “Save” button is clicked, this page will close, and the user will be back to the Report Designer.


of 70

Sort / Group By

On the Report Selector page, there are 3 boxes, Sort / Group By, Report Filters and Hidden Fields. The Sort / Group By allows the user to determine how the report will sort, what groupings will be displayed, and if any aggregation will display at the groupings.

Sort / Group By can only be added if fields have been moved to the Report Design Screen in the Report Designer. If no fields have been moved, the user will need to drag and drop fields first before utilizing the Sort / Group By tool.

Once fields are available, the user can click on the “Click Here to add New Sort / Group” link to be served the following page.

The Sort / Group By page consist of the following tools:

Name for Sort / Group By. Helps distinguish this Sort / Group By from others in the list.

Make Active? Determines if this Sort / Group By will be used when “Run Report” is clicked.

Hide Detail Lines? Determines if detail lines on the PDF report will display.

Use, Private, Public, Required.

Fields on Report. Displays what data fields are available for Sort / Group By.


of 70

Options – Add Row. As more Sort / Group By fields are used, the “Add Row” button will allow the user to continue to add more fields.

Table. This stores the fields being used for Sort / Group By, what type of Report Break will be used, and any aggregation “Group Function” that may be applied.

The Sort / Group By tool is limited to fields that have been added to the Report Design Screen.

Enter Name for Sort / Group By Creating a good naming convention, in general, is a good practice and that remains true to the Name for the Sort / Group By. The Sort / Group By a user creates will display amongst other Sort / Group By names, a descriptive name will tell the user intuitively how the Sort / Group By functions.

Make Active? This feature will be common in all of the 3 boxes on the Report Selector. From the Report Selector page, a user can quickly make a Sort / Group By active and click “Run Report” using the selected Sort / Group By. This type of selection takes only a click or two to set, making it a flexible and easy tool to use.

The Make Active? feature can also be accomplished from the Report Selector page. By clicking on the radio button next to the Sort / Group, the user can activate the Sort / Group they want active for the “Run Report” action.

If the user clicks on the “Save” button in the Sort / Group By page, then the Sort / Group By will automatically become the active Sort / Group By.

Hide Detail Lines? This feature proves useful when there are many aggregations applied to the columns in the report. The report owner may have multiple levels of report breaks as well. Using the Hide Detail Lines assumes that there will be enough columns being aggregated at each break where the report will still make sense.

Clicking the Hide Detail Lines will:

Stop Detail Lines from printing on the report Will print a footer label on the inner most break Will print a horizontal line underneath the footer on the outer most break

This feature will give the report an appearance of having detail lines, but the data that is being displayed are the aggregated (summarized) totals.


of 70

Use, Private, Public, Required This feature is similar to the Private / Public flag discussed in Calculated fields. However, this functionality is really only effective if the Report itself is Public. The owner of the Report may have provided some Sort / Group By along with the Report. For others to use the Sort / Group By, the owner must flag them as Public. Anyone else can add Sort / Group By, flagging them as Public for anyone else to use or Private, making it available only for them.

The Required option is only available to the owner of the report. The concept here is that the owner may have some Sort / Group By options that really define the report and are necessary when the report is run. If so, the report owner can create a required Sort / Group By. When other users run the report, the required Sort / Group By will automatically be active.

However, other users can still apply their own Sort / Group By. Their Sort / Group By will process first, then the required Sort / Group By will process second.

Required Sort / Group By will more times than not, contain aggregations, thus leaving how the report sorts and breaks in control of the other users.

Fields on Report This is a list of data fields that can be used in the Sort / Group. By clicking on the desired field, the field will populate the open active row in the Table.

Options – Add Row As data fields are added to the Table, more rows can be added as well, to make room for more data fields. Clicking the “Add Row” button will add rows to the table.

Also, as the user click on Fields on the Report to move fields down to the Table, a row will be automatically added.

Table As with the Table in the Conditional Field Formatting, entries in this Table can be deleted. Rows can be modified at any time, changing the Order, Break or Function. As with the Table in Conditional Field Formatting, only entries in the Table will be saved to the database when the “Save” button is clicked.

One Sort / Group By can contain one or multiple fields. And, many Sort/ Groups can exist for a report. It’s more of what the user may need for running the report in various scenarios.


of 70

Moving a data field to the Table.

Clicking on a data field in the “Fields on Report” tool will move the clicked field to the active row in the Table. The active row appears in green, and the clicked field will turn to a white background after it’s been clicked and moved. Once a field has been moved to the Table, it’s no longer able to be moved to the Table again, users can only move a field once to the Table. After a field is moved to the table, another row is automatically added and made active.

Ascending / Descending Order Ascending / Descending Order determines how a report will sort and on what field. Normally, a report will have no sorting applied. As the user applies sorting, the report will order the data on the selected field. Sorting can occur over multiple fields, and either in Ascending (Asc) or Descending (Desc) order. For example, a user may want a report sorted by Salary Division, then by Pay. The result would be the overall report is sorted by Salary Division, and within each Salary Division, the Pay is sorted.

To accomplish this in the Sort / Group tool, the user will click on the Salary Division field, moving it to the first row in the Table. Then the user will click on the Pay field, moving it to the 2nd row in the Table. The result would look like the following.


of 70

The order in which fields appear in the Table is significant as the top rows will be sorted first on the report, moving down the Table and the last row being sorted last in the report.

Ascending refers to sorting data with data starting with “A” being first, and data starting with “Z” being last. Descending refers to sorting data with data starting with “Z” being first and “A” being last. The descending option may be used on a date field where the user wants to see the most recent records first.

Reorder tool in Table Understanding that the order in which fields are “ordered” is important, the Reorder tool in the Table allows the user to move fields to a higher / lower sort than other fields. As the arrow icons suggest, clicking on an up arrow will move that field above the field above it, and in affect, changing positions. The similar function occurs with the down arrow, changing a field’s position with the one below it.

The Reorder function allows the user the flexibility to change the sort order on the report easily and painlessly.

Section / Page Break This tool allows the user to create a “break” in the report. In the above example where the report was sorted by Salary Division and then Pay, a break applied to Salary Division will leave a notable space on the report between changing values in Salary Division

A Section break would leave a space on the report between different values of Salary Division. A Page break would cause the report to start the new value of Salary Division on a new page, at the top.


of 70

Section / Page breaks are useful as they cause a visible division between different and changing values of the selected field.

Group Function Group Functions available are:

Sum

Count

Average

Minimum

Maximum

User Defined

These functions are used in conjunction with any Section / Page Break applied to a field. For example, in the previous discussion, Salary Division was set as a Page Break. If the user applied an Average function to the Pay field, whenever the Salary Division value changed, an average Pay would display at the end of the Salary Division break.

User Defined Function The User Defined function allows the user to create their own function. When the user selects User Defined, the “Click Here to Create a Calculation” link in Group Calculations is displayed.


of 70

Clicking on the link will serve the following page:

Group Function Builder The Group Function Builder is similar to the Function Builder of calculated fields. However, due to how the equation is processed, only the Math functions are available. Also, only fields with aggregations applied to it are available in the Fields list. The processing will take the results of the aggregations of the fields in the list and apply those values to the equation created.

The Match Count and Row Count field are special field values offered in the Group Function Builder. Match Count represents the number of matches for the year. This figure is used in the Survey Utilization report. Row Count represents the number of rows returned in the report. This figure is used in the Compensation to Salary Structure Summary report.

More details on this topic will be discussed later in this document.

Labels Labels can be placed to the left or right of aggregate columns. Labels will appear on the same line, next to the aggregated column. The user will need to make sure that space exists where the label will appear. If the label is placed over another aggregated column, one field will end up blocking the other.

Deleting a Row from the Table Any fields that are no longer needed in the Sort / Group By can be deleted by clicking the red X on the right side of the table. This will remove that row, and make the deleted field available for clicking into the table again.


of 70

General Rules Any data field on the report can have an Order applied to it.

If a Section / Page break is applied to a field, that field must have an Order applied to it. Luckily, the Sort / Group tool automatically does this for the users.

Any field on the report can have a Group Function applied to it. However, unless a Section / Report Break is applied to one of the fields on the report, the Group Function will not display.


of 70

Filters

Report Filters is the second box on the Report Selector page. This tool allows the user to determine the set of data that will display on the report. Unlike the Sort / Group tool, Report Filters can utilize any of the data fields available in the View. Fields used in Report Filters is not limited to the data fields dragged onto the report.

Unlike the Sort / Group tool, Report Filters can be created at any time. Having fields on the Report Design Screen is not required for this tool.

From the Report Selector page, clicking on “Click Here to add New Report Filter” will serve the following page.

The Report Filters consists of the following tools:

Name for Filter. Helps distinguish the Filter from other Filters in the Report Filters list.

Make Active? Determines if this Filter is the active Filter when the “Run Report” link is clicked.

Use, Private, Public, Required. Determines if a Filter is available for others or just the owner.

Expression Builder. This Builder provides different functionality than the Function Builder for Calculated Fields and Conditional Field Formatting.

Options – “Clear Expression Box”, “Click to Move Down”, “Record Count / Check Syntax”


of 70

Expression Box. Stores the Filter as it is being constructed.

Enter Name for Filter As with the Sort / Group name, a good naming convention should be practiced with the Filter name. Since this name will appear in a list with other Filter names, having a name that describes how the Filter works will assists users in selecting the Filter they want at Report run time.

Make Active? Clicking this checkbox will mark it as the active Filter. When the “Run Report” button is clicked, it will utilize the active Filter for selecting records.

Again, as the Sort / Group tool, this option is available from the Report Selector page. The user can quickly select the filter they wish to apply to the report by clicking on the radio button next to it.

Also when the Save button is clicked, the Filter automatically becomes the active Filter.

Use, Public, Private, Required Like the Sort / Group By, the Public – Private flag is really only effective if the report itself is public. If the report is public, and a Filter is public, then anyone using the report can also use the Filter, and not be required to create a Filter for themselves. If a user wants a different Filter, of course, they can create a new Filter for themselves, and make it private.

The Required flag behaves the same as the Required flag in the Sort / Group By. This option is available to the owner of the report, and if a specific filter defines the report, the owner can flag the filter Required.

The Filter will become active for any other users who run the report. Other users can also apply their own filters and both their filters and the required filter will be applied when running the report.

Some caution should be used if applying addition filters to a report with a required Filter. Example is if the required filter is Pay > GradeMax and the other user applies “and Pay < GradeMax”. The 2 filters will return no records.

Expression Builder The Expression Builder is comprised of 6 select boxes.

Connector. This is used if a second expression is moved to the Expression Box. This select box can be ignored when constructing the first Expression.


of 70

“(“. An open parenthesis is used when grouping of expressions is necessary.

Column. This select box contains all the fields in the View, as selected on the Report Attribute page. As discussed earlier, any field available in the selected View can be used in Filters.

Operator. Operator is the test that will be used on the selected column. Available tests are:

o Equals

o Less Than

o Less Than or Equal To

o Greater Than

o Greater Than or Equal To

o Is Blank

o Like

o In

o Between

Value. This field accepts keyboard entry of a value.

“)”. A closing parenthesis, used if an open parenthesis and specific grouping is required.

Options Options contain the following buttons:

Clear Expression Box. Clicking on this button will clear all contents in the Expression Box.

Click to Move Down. Clicking on this button will move the selected contents of the Expression Builder to the Expression Box. Only contents in the Expression Box are saved to the database when the “Save” button is clicked. Anything selected in the Expression Builder will not be saved.

Record Count / Check Syntax. Clicking this button will display the number of rows that the filter in the Expression Box will return. Additionally, this tool will check to verify the syntax in the Expression Box


of 70

and assure that the processing will not fail due to any errors in the Expression Box.

Not in the Expression Builder, Operator The Not checkbox applies reverse logic on the operator. For example, if the user builds the expression “Pay Greater Than 100000”, and clicks on the Not checkbox, the test will change to “Pay Not Greater Than 100000”. Additionally, Filters will apply additional parenthesis to the Expression when the “Click to Move Down” button is clicked. The additional parenthesis will assure that the expression will process correctly and as expected.

Lookup Values in the Expression Builder, Value The Lookup Values is a handy tool that allows the user to view data that exists in the Column selected. For example, if “Region” was selected in Columns, clicking on the “lookup values” link would pop up a window with all the different Region values that exists in the database.

Additionally, when the values are offered on the page, clicking on any of the values would automatically populate the Value box with the clicked value from the window. No manual entry is necessary with this tool.

Stringing Expressions Together The Expression Builder allows for easy construction of an expression. However, the user may require more than one expression to filter the report by. Utilizing the Connector select box will accommodate this requirement.

If the user needs to filter the report with the following logic:

Where Pay is greater than 100,000, or less than 20,000.


of 70

The Connector select box would be utilized to accommodate the above example. The user would construct the first part of the filter, and click on “Click to Move Down”.

The user would then select “OR” from the Connector select box, and construct the remainder of the filter, “Pay Less Than 20,000”.

Clicking on “Click to Move Down” would produce the following result in the Expression Box.

The filter now has the 2 expressions in effect. This filter will allow records where the Pay is greater than 100,000 or less than 20,000, no records that have Pay between the 2 values will be brought to the report.


of 70

Hidden Fields

The 3rd box on the Report Selector page houses the Hidden Fields tool. This tool is utilized if the user wishes to temporarily hide a field on the report that typically displays. Normally, the user would have to go to the Report Designer, remove the field, save and then run the report. Afterwards, the user may have to go back into the Report Designer, and add the field back on.

This tool alleviates that exercise by allowing the user to mark certain fields to not display on the report. The field is not removed from the report, it simply doesn’t display when the report is run.

This functionality can be useful if the audience of the report changes from run to run. For example, if a report normally contains an employee’s pay, the Hidden Field tool can temporarily stop the Pay field from displaying on the report. If the Pay field is used in other tools (Conditional Field Formatting, Sort Group), this functionality will not adversely affect its field usage in those tools.

A technique was discussed earlier where users may place fields on the report, but past the right margin. This technique was to accommodate the Conditional Field Formatting tool, and allowed users to use fields with that tool, that they didn’t want displaying on the report.

The Hidden Field tool will not adversely affect that technique either. The field to be hidden, isn’t actually removed from the report, it’s stopped from displaying. When the PDF format is displayed, the rest of the fields to the right of the hidden field are shifted over, in order to fill the gap that hidden field has created. However, any fields that have been purposely placed past the right margin, will remain there, and will not be shifted over.

To utilize the Hidden Fields tool, data fields must exist on the Report Design Screen. Once there are data fields in the Report Design Screen, clicking on the “Click here to add New Hidden Fields Group” link from the Report Selector page will serve the following page:


of 70

The functionality of this tool is much simpler than the other 3 tools available from the Report Selector.

Name for Hidden Fields

Make Active?

Use, Private or Public. Determines if the Hidden Fields group is available for others or just the owner.

Hide Table

Enter Name for Hidden Fields As with the other 2 tools on the Report Selector, the user will want to name the Hidden Fields in a fashion that will help distinguish it from other Hidden Fields names that may be in the list.

Make Active? Also the same as the other 2 tools, Make Active? will activate the select Hidden Fields when the Report is run. A Hidden Field group can be made active from the Report Selector page by clicking on the radio button next to it.

If the Save button is clicked, then the Hidden Fields will become the active Hidden Fields.


of 70

Use Again, this feature is only effective is the report itself is public. Like the other tools, a Hidden Field group can be made available for others, or if the flag is set to Private, only available to the owner.

Hide Table The user clicks on the checkbox next to the fields they wish to be hidden.


of 70

Paper Size

When a report is created, and the Paper Size in the Report Attributes is selected as either Letter or A4, the Paper Size box on the Report Selector will activate. This box will allow the user to select either Letter or A4 paper size at run time. These 2 paper sizes are pretty close and will make our international European users quite happy.

If the paper size is other than Letter or A4, this box will be inactivated.


of 70

Calculation Maintenance

The Calculation Maintenance page is where a user can view and manage any of their Calculated Fields. Access to this application is from the Report Selector page, under the Calculation button at the top. This button will only appear if the user has any Calculated Fields that they own. When the user clicks on that button, the following page will be served.

This page basically lists the Calculated Fields the user is an owner to, and what reports they are used in. Additional information that is displayed is the View the Calculated Field belongs to, whether the Calculated Field is Public or Private, the data type and the Equation of the Calculated Field.

The Name column of this page contains a link to the Function Builder page, allowing the user a quick way to modify the Calculated Field. This is especially useful since the only other way to modify the Calculated Field is to navigate to through the report the Calculated Field was used on.

Also, if one of the users Calculated Fields was Public and used in many reports, this page can display the names of those reports. If a Calculated Field is not used in any reports, then the “Used in Report” column would be blank.

If a Calculated Field has no Reports associated to it, the user can click on the name and in the Function Builder page, a Delete button would be offered. The only Calculated Fields that can be deleted are those that are not used in any reports.


of 70

Report Designer, Saving to the database

The Report Designer is where the most work occurs in Advanced Reporting. The user is offered a “Save” button, and it’s expected that the user will save their work from time to time. However, due to functionality requirements on other pages, a “Save” to the database can occur without the user clicking on the “Save” button.

Under Actions, the following buttons have the following effect:

Save and Return – A save is executed. This navigates the user back to the Report Selector page.

Save – A save is executed. The user remains in the Report Designer.

Cancel – A save is not executed. Cancel takes the user back to the Report Selector page.

Other navigations and their affect:

Field Attribute – NO save is performed. Remember, the unique thing about the Field Attribute window is that as changes are being made in the window, the results are also stored on the Report Designer page. However, no database saves are performed.

From the Field Attribute window, Calculation (Function Builder) – YES, a save is performed. Additionally, if the user performs a “Save” from the Function Builder, then the Report Designer will be saved again to the database and the page refreshed. Logic is that the user may have created field that needs to display in the Field Tree, under the Calculated Fields branch.

From the Field Attribute window, Conditional Field Formatting – YES a save is performed. Logic being that the Conditional Field Formatting can only use fields that are placed on the Report Design Screen. If the user placed any new fields from the last save and then navigates to the Conditional Field Formatting page, they may expect to see those newly placed fields. Additionally, if the user performs a save in Conditional Field Formatting, the Report Designer will be saved and refreshed.


of 70

Trash Can, more on Deleting Fields

As discussed earlier, if the user wants to remove a data field from the Report Design Screen, they can drag the field to the Trash Can icon. The next time the report is saved, the field will also be removed from the database.

In actuality, more processing is performed in deleting a data field from the database. Advanced Reporting will look into the other tools and determine if the deleted field was used. If so, Advanced Reporting will delete the records that pertain to the deleted field. This is necessary as to not cause any issues when the PDF format is generated.

The tools involved in the extra processing are as follows:

Sort / Group. Remember that this tools relies on the field existing the in the Report Design Screen. If the field is not present there, the processing will fail, causing an error. When a field is deleted that is used by Sort / Group, it is deleted in that tool as well.

Hidden Fields. This tool requires that the field exist on the Report Design Screen as well. It would error in processing if not, so the field is deleted from this tool to prevent that.

Conditional Field Formatting. This tool requires presence of the field on the Report Design Screen for proper processing. However, there are 2 areas that are examined when deleting records from this tool. If the field is the main field to receive the Conditional Formatting, the record will be deleted. The second is if the field is used in the Condition, or if the field is part of the Equation. If the field is in the Equation, that record will be deleted to prevent any errors in processing.


of 70

Sort / Group By – More on Group Function Builder

In the Sort / Group By, one of the Group Function options is “User Defined”. This is somewhat of an unconventional function and warrants further discussion. When the “Click Here to Create a Calculation” link is clicked, the Group Function Builder page will be served. The Group Function Builder contains only Math and Differential functions.

The Report Fields that are offered will only be those that have an aggregation applied to them. Example: if we have a Pay field and a Market field, and we applied an average function to the 2 fields, the Pay and Market fields would appear in the Report Fields list.

If the function that was created is take the difference between Pay and Market (Pay – Market), the processing would work similar to this.

When ever there was a section or page break, Active Reports would process the aggregations.

The average of Pay would be calculated along with the Market average, for that break level.

Then the function would be applied: Average Pay – Average Market. The result would be displayed in the field which User Defined was selected.

The thing to note here is that the function is not performing any aggregations on any fields itself. The function is taking the results of the 2 field’s aggregations and applying the subtraction to the aggregations.

Example: If we have the following set up in Sort / Group By as below:

Pay and Market have an average aggregation applied to them and CompaRatio has a User Defined function of (Pay – MarketValue) applied to it.


of 70

The report would process like the following:

The 52,819.21 represents the average for Pay, 45,638.00 the average for Market Value. The 7,181.21 is the difference between Pay and Market. Note that this figure is placed under the Compa-Ratio column but does not apply any aggregation to the Compa-Ratio column.


of 70

Function Builder – the Differential Category

On the Function Builder and the Group Function Builder pages exists a function category called Differential. This category is specific to Reward settings concerning how some calculations will be performed. The following page contains the settings:

The functions in the Differential category are:

Differential (pay, market)

Status (pay, market)

Inc Weight Flag


of 70

Differential The Differential function reacts from the Differential setting. Reward users can select how Employee Pay and Market differentials will be calculated. The following options are available for the users to select:

(Pay – Market) / Market

(Pay – Market) / Pay

(Market – Pay) / Market

(Market – Pay) / Pay

Pay / Market

Market / Pay

When creating a report, the owner will not have to address each of the calculations. They can simply place the Differential function into the Function Builder and Advanced Reporting will determine what Differential calculation the user running the report has selected and will apply that selected calculation in the report.

Example is the Report Owner creates a Report with a Calculated Field on it, with the Differential function applied to that.

User One runs the report, and has a Differential calculation of Pay / Market set in their Miscellaneous Calculation Options. When the report displays, the differential will calculate as Pay / Market.

User Two runs the same report but has a Differential calculation set as (Pay – Market) / Market. Even though User Two’s Differential calculation is different from User One’s, the differential calculation on the report will process as (Pay – Market) / Market.

Status Status works similar to Differential. It will react off the differential calculation selected by individual users. It also uses the Market Range calculation settings of that user. If the differential calculation is less than the bottom setting, Status will return a “Low”. If the differential calculation is greater than the top setting, Status will return “High”. Anything in between will return “OK”.

So like the Differential function, Status will react off the settings of the user running the report.


of 70

Incumbent Weighted Average Flag (Inc Weight Flag) The Incumbent Weighted Average Flag in the Miscellaneous Calculation Options determines if subtotals using Market Value or Average Pay should be weighted by the number of incumbents on each job for 2 specific reports. Advanced Reporting utilizes this flag a bit differently.

Market Value Summary and Market Comparison reports show data at the job level. However, sub totals on report breaks will show averages for Market Value and Average Pay. This flag will allow the user to select to see those averages on the report break weighted by incumbents, or not.

Advanced Reporting can utilize this flag as well, however, some foundation must be built by the report owner for the flag to react similarly.

Firstly, the Inc Weight Flag will be used in a Calculated Field built by the Function Builder. The value of the Inc Weight Flag can be either zero (not checked) or one (checked). Checked means, yes, the user wants averages to be Incumbent weighted.

Each row will contain a job, the number of incumbents in that job and a market value for that job. Setting up an equation that will react off the value of the flag will look something like this:

Market Value x ((IncWeightFlag x # of Inc) + (1 – IncWeightFlag))

Using some actual values:

Market Value = 17,890, # of Inc = 17

If Inc Weight Flag is checked, or equal to one

17,890 x ((1 x 17) + (1 – 1)) = 17,890 x 17

If the flag is not checked, equal to zero

17,890 x ((0 x 17) + (1 – 0)) = 17,890 x 1

The report owner would need to build out the calculated field as such, but Advanced Reporting can check to see if the user running the report has the checkbox checked and apply the appropriate value to the flag.


of 70

Sort / Group By Average vs Actual Average

The Sort / Group By Average function takes the sum of the column being averaged and divides it by the count of rows. Unfortunately, this, at times, can give faulty results. Taking the Market Value of a job as an example, some jobs don’t have a market value. The Sort / Group By average will give false results on that scenario.

Market Values of: 12,355 No value 13,634 10,355

The average that the Sort / Group By will come up with is:

12,355 + 13,634 + 10,355 = 36,344 36,344 / 4 = 9086

This is considered incorrect within Reward because the job with no market value should not be included in the average. So instead of dividing by 4, the “more” correct answer is to divide by 3.

36,344 / 3 = 12,114.66

For most fields where a value exists for each row, the Sort / Group By average will work fine. For fields that have the same scenario as the Market Value, a different approach will need to be used.

Two calculated fields will need to be created. One calculated field will store the Market Value sum, the second will store the count of rows with a Market Value. These calculated fields can be placed to the right of the right margin so they don’t appear on the report.

The calculated field will look similar to the following:

If Market Value > 0 then Market Value else 0 end If Market Value > 0 then 1 else 0 end

Next, these 2 fields will need to have a sum function applied to them in the Sort / Group By. The field where the user wishes the average to appear will need to be changed to a User Defined function. Click on the link to open the Group Function Builder, and create a function that divides the first calculated field by the second calculated field.

advanced reporting tool · advanced reporting tool manual page 10 of 70 report designer the report...

Documents