Modifying Reports and Filtering

Revision as of 05:20, 29 January 2013

Modifying Human Resources Reports

The Subscribe-HR Software system is Object orientated and this is the same for Reports. The ability to add particular fields into a report solely relies on that field being part of the object.

Example: We are modifying the Employee Details report, which belongs to the Employee Object.
The Database Tables and Fields that are available to me are limited to anything in the Employee Object and this would include Employee, Employee Medical, Employee Property and so on.

We would not be able to add any fields from the Jobs Table because Jobs is its own Object. We can however add fields from the Employee Jobs Table.

After logging into the Subscribe-HR either select Tools > Reports > ‘Standard’ or ‘My Reports’ SubTabs,
Or select List Reports. on the Quick Link Bar.
Select the area the Report is in. This will be one of the following SubTabs: Standard, My Reports, Shared With Me and Custom.
Each TAB is explained below.
Select the Report you wish to modify.
You will see 3 or 4 SubTabs: General, Join Tables (if option selected), Fields and View Report.
Each TAB is explained further below.

Report Groupings

All the Reports are grouped into the following 4 areas:

Standard SubTab

This is a list of all the Standard Reports in the Subscribe-HR System. They can be modified or deleted as required. . . .

My Reports SubTab

This is a list of all the Reports created by the User.

Share With Me SubTab

This is a list of all the Standard Reports, My Reports & any other reports created by other users & marked as Public. . . .

Custom SubTab

This is a link to the Custom Reports which have been created by Subscribe-HR for the client.

General

Select the General SubTab.
Select the Public check box.
By selecting the Public check box you are making this report available to all users.
By removing the check from the Public check box you are making this report private and only available to users with permissions.
Select the Report Type from the dropdown, such as ‘Human Resources’, ‘Recruitment’, etc.
Enter Report name. The name of the report should be descriptive in what data it shows.
Object is not changeable when modifying a report. The Object defines what Tables and Fields are available for your report.
Join Tables (Advanced) is not changeable when modifying a report.
Display Empty Records is where you can specify to make all entries in an object to sub-object relationship to be displayed even if the entries in the object does not have an entry in the sub-object.
Report Description is where you can make notes on the report and describe the data it shows.
Enable the Report Scheduler if required.
If Report Scheduler is selected, then you will get the following options:
Select the Format you want the report delivered in
Select the Frequency you would like to send report
Select the Recipients by selecting the user
Save Report before moving onto Joining Tables or setting up fields.

Join Tables

On the Join Tables SubTab, enter Table (Object) name (or part of) you wish to include in the Join.
Click on table, then drag line to next table to join them.
Then you will be prompted to select required From Field & To Field to join, then the Join Type:
LEFT JOIN shows all records from left table regardless of the existence of matching records in the right table.
RIGHT JOIN means opposite of LEFT JOIN. It shows all records from the second (right) table and only matching records from first (left) table.
INNER JOIN shows all records where there is at least one match in either table.
Click OK & repeat process for other Tables to include.
To remove a Link between Object, simply click on the dot where the link connects to the Object & drag & drop it away from the Object.
Save changes, before moving onto the Fields SubTab.

Fields

Select the Fields SubTab.
To find the required Field (Element), start typing in the Fields section. This will show in a dropdown of all available Fields matching the search criteria.
Optionally select the field(s) to Group the Report by.
Save changes.

Totals & Aggregate Functions

Click on the Pencil Icon on the right of the Field. Select from the dropdowns, the Basic Function (Average, Count, Max, Min or Sum).
The Calculate Total field is populated with a check box when a numeric field is added.

If the check box is selected this will provide a total of all numeric values at the bottom of the report, if the report is grouped by a field, then the total is placed at the bottom of each grouping.
Save changes.

Formatting

Click on the Pencil Icon on the right of the Field. The Date Formatting field is populated with a drop down when a date field is added.

If formatting is selected such as Month Name this will only display the Month name in the report and not the dd/mm/yyyy.
Save changes.

Sorting

Click on the Pencil Icon on the right of the Field. The Sorting Order field is populated with a drop down for all fields.

Selecting the sort order of either Ascending or Descending for a field will cause the report to output the data in either an Ascending or Descending order highest to lowest.
Save changes.

Condition

Conditions are used to filter your report based on a criteria.

Down the bottom of the Fields SubTab there is the Condition section.
Select the field you wish to filter by.
Select the filter type. The filter type varies depending on what field you are filtering on.
The most common are Equals To, Not Equals To, Is Last, This Last, Is Next.
Select or Enter the value you are filtering on.
For more than one Condition an AND or a OR operator can be selected.
You can select multiple results for some fields by clicking on Options (if available) as shown below.
To group Condition clauses together, brackets can be used. This is done by clicking on the Greater Than icon to the right of the Field in the Condition clause. Clicking on the Less Than icon will remove a Bracket.
The below Condition clause can be written as:
:IF (Leaver = No OR Last Day of Work is Last Week) AND Department=”???list of Department???”
Save changes.

View Report

The View Report TAB allows us to generate and view the report. We can export to PDF or Excel file formats.
Only reports that are 'Grouped by can generate a Graph, otherwise a plain table of results will be generated.

Select the View Report SubTab.

Graph Types

The Graph type option will not be shown if a report has not been Grouped by. Selecting a Graph type will change the graph drawn on the report.

Graph Record Count

Selecting the Graph Record Count will show the number of records being reported on.

Export Options

Selecting an Export Option and then selecting the Export link will cause the report to be exported into the chosen format (Excel, PDF, etc).

Do Not Export Graph

Selecting this checkbox will cause the graph to be excluded when exporting the report to the chosen format.

@@ Line 1: / Line 1: @@
 {{page.name: Core Human Resources: Modifying Reports and Filtering - Subscribe-HR Wiki}}
 {{page.head: Modifying Reports and Filtering}}
-<!--TWS--> <big>''' <<< Currently Under Construction for Report Writer Modifications >>> '''</big>
 __TOC__
@@ Line 67: / Line 65: @@
 # To remove a Link between Object, simply click on the dot where the link connects to the Object & drag & drop it away from the Object.
 # '''Save''' changes, before moving onto the Fields SubTab.
 ===Fields===
-# Select the Fields TAB
+# Select the Fields SubTab.
-# You should see a Report Object drop down. In the drop down are all the Tables that are available under this Object.<br>[[File:report_object.gif]]
+# To find the required Field (Element), start typing in the Fields section. This will show in a dropdown of all available Fields matching the search criteria.  <br>[[File:Report-Fields-Search.jpg]]
-# On the left is a list of all Fields available from the selected Table in the Report Object drop down.<br>[[File:report_tf.gif]]
+# Optionally select the field(s) to '''Group''' the Report by.  <br>[[File:Report-Fields-Done1.jpg]]
+# '''Save''' changes.
-# In the centre of the screen you should see 2x headings '''Fields''' and '''Groups'''<br>The fields that appear under the '''Fields''' heading are fields that you are showing on your report.<br>'''Example:''' If I wanted to add Employee Age to my report I would expect to see First name, Surname and Age under the fields heading.<br>[[File:emp_age.gif]]
-# The Groups heading allows for a '''Group By''' function, where by adding a field under the Groups heading would cause all fields to show on the report and be in groups by the field that appears under the Groups heading.<br>'''Example:''' If we added a field such as Department under the Groups heading our report would show All Employees First Name, Surname and Age, and this would be Grouped in departments.<br>[[File:report_output.gif]]<br>Example Report:<br>[[File:emp_dep.gif]]
-# To add a field under the '''Fields''' heading select the [[File:F.gif]] next to the desired field.<br>To add a field under the '''Groups''' heading select the [[File:G.gif]] next to the desired field.<br>To remove a field select the [[File:X.gif]] next to the desired field.
-# Select the '''Save''' button.
-====Total====
+====Totals & Aggregate Functions====
-The '''Total''' column is populated with a check box when a numeric field is added.<br>[[File:numeric.gif]]<br>
+# Click on the '''Pencil Icon''' [[File:Report-Fields-Pencil.jpg]] on the right of the Field. Select from the dropdowns, the '''Basic Function''' (Average, Count, Max, Min or Sum).  <br>[[File:Report-Fields-Function.jpg]]
-If the check box is selected this will provide a total of all numeric values at the bottom of the report, if the report is grouped by the total is placed at the bottom of each grouping.
+# The '''Calculate Total''' field is populated with a check box when a numeric field is added.  <br>[[File:Report-Fields-Calc.jpg]]  <br>If the check box is selected this will provide a total of all numeric values at the bottom of the report, if the report is grouped by a field, then the total is placed at the bottom of each grouping.
+# '''Save''' changes.
 ====Formatting====
-The '''Formatting''' column is populated with a drop down when a date field is added<br>[[File:date_field.gif]]<br>If formatting is selected such as Month Name this will only display the Month name in the report and not the dd/mm/yyyy
+# Click on the '''Pencil Icon''' [[File:Report-Fields-Pencil.jpg]] on the right of the Field. The '''Date Formatting''' field is populated with a drop down when a date field is added.  <br>[[File:Report-Fields-Formating.jpg]]  <br>If formatting is selected such as Month Name this will only display the Month name in the report and not the dd/mm/yyyy.
+# '''Save''' changes.
 ====Sorting====
-The '''Sorting''' column is populated with a drop down for all fields.<br>[[File:sort.gif]]<br>Selecting the sort order of '''Descending''' for a field will cause the report to output the data in descending order highest to lowest.
+# Click on the '''Pencil Icon''' [[File:Report-Fields-Pencil.jpg]] on the right of the Field. The '''Sorting Order''' field is populated with a drop down for all fields.  <br>[[File:Report-Fields-Sort.jpg]]  <br>Selecting the sort order of either '''Ascending''' or '''Descending''' for a field will cause the report to output the data in either an Ascending or Descending order highest to lowest.
+# '''Save''' changes.
 ====Condition====
 Conditions are used to filter your report based on a criteria.
-Example: You have created a report that lists all employees names, age and its grouped by department, but you only want to see data for 2x locations.
+# Down the bottom of the '''Fields''' SubTab there is the '''Condition''' section.
-# Select the Condition TAB.
-# We will see '''Add condition''' drop down.
 # Select the field you wish to filter by.
-# Select the filter type. The filter type varies depending on what field you are filtering on.<br>The most common are Equals To, Not Equals To, Is Last, This Last, Is Next
+# Select the filter type. The filter type varies depending on what field you are filtering on.  <br>The most common are '''Equals To''', '''Not Equals To''', '''Is Last''', '''This Last''', '''Is Next'''.
 # Select or Enter the value you are filtering on.
-# Select the '''Save''' button.
+# For more than one Condition an '''AND''' or a '''OR''' operator can be selected.
+# You can select multiple results for some fields by clicking on '''Options''' (if available) as shown below.  <br>[[File:Report-Fields-Options.jpg]]
-'''Example: Filtering on selected Job Locations.'''
+# To group Condition clauses together, brackets can be used. This is done by clicking on the Greater Than icon [[File:Report-Fields-Bracket.jpg]] to the right of the Field in the Condition clause. Clicking on the Less Than icon will remove a Bracket.  <br>The below Condition clause can be written as:  <br>:IF ('''Leaver''' = No OR '''Last Day of Work''' is Last Week) AND '''Department'''=”''???list of Department???''”  <br>[[File:Report-Fields-Done2.jpg]]
-<br>
+# '''Save''' changes.
-[[File:location_select.gif]]
 ===View Report===
@@ Line 117: / Line 100: @@
 <br>
 Only reports that are '''Grouped'' by can generate a Graph, otherwise a plain table of results will be generated.
+# Select the '''View Report''' SubTab.  <br>[[File:Report-View1.jpg]]  <br>[[File:Report-View2.jpg]]
-# Select the View Report TAB.
 ====Graph Types====
 The Graph type option will not be shown if a report has not been '''Grouped''' by.
 Selecting a Graph type will change the graph drawn on the report.
+====Graph Record Count====
+Selecting the '''Graph Record Count''' will show the number of records being reported on.  <br>[[File:Report-View-RecCount.jpg]]
 ====Export Options====
-Selecting an export option and then selecting the Export link will cause the report to be exported into the chosen format.
+Selecting an '''Export Option''' and then selecting the '''Export''' link will cause the report to be exported into the chosen format (Excel, PDF, etc).
 ====Do Not Export Graph====
 Selecting this checkbox will cause the graph to be excluded when exporting the report to the chosen format.
+<br>
+<br>
 [[File:subscribehr_logo.gif]]