Versions Compared

Old Version 44

changes.mady.by.user Diane Momongan

Saved on

compared with

New Version 45

changes.mady.by.user Diane Momongan

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Overview

Reports when produced can be very time-consuming especially if you are scanning the whole database with no filters. To avoid the need to extract out all data from Paradigm in favor of targeting just the records that are desired, Report Builder will allow you to add filter clauses. This is both to minimise the time taken to generate a report and also remove the need for manually editing (removing rows) from the output before it is ready to support business decision-making.

INTRODUCTION: FILTER CLAUSES User Interface Navigation

Expand
titleTab 1: Set Filter Clauses

This is where you will add the filter clauses required for your report. You can group your clauses by an AND or OR set and most importantly an option to validate the rules you’ve added.

Expand
titleTab 2: Form Editor

After adding all your rules set, you will be given an option to set the fields whether you want them to appear on the form when you save the report. A convenient way to preview your form is also available by clicking the PREVIEW FORM button.

Expand
titleTab 3: Base Report Parameters

Expand
titleFILTER CLAUSES section Buttons

Buttons

Brief Description

COMPARE EXPORT STRING

COMPARE EXPORT DATA

PREVIEW FORM

+ AND SET

+ OR SET

VALIDATE RULES

Key Terms and Concepts

INCLUSIVE Filter Clause Operators

Expand
titleEquals to the value (=)

Equals to the value (=)

For this operator, the data within the column name has to be an exact match.

 

Example 1

“Postal Code” = 3153 (only records with the exact match (3153) will be returned in the data set)

Image Added

Example 2

“Course Accredited” = YES (only records with the exact match (YES) will be returned in the data set)

Image Added

Greater Than (>)

Expand
titleGreater than the value (>)

Greater than the value (>)

Expand
titleGreater than or equal to the value (>=)

Greater than or equal to the value (>=)

Expand
titleLess than the value (<)

Less than the value (<)

Expand
titleLess than or equal to the value (<=)

Less than or equal to the value (<=)

Expand
titleContains the value (ilike)

Contains the value (ilike)

Expand
titleContains the case sensitive value (like)

Contains the case sensitive value (like)

Expand
titleContains any of the following options (in)

Contains any of the following options (in)

Expand
titleIs not empty (not null)

Is not empty (not null)

EXCLUSIVE Filter Clause Operators

Expand
titleNot equal to (!=)

Expand
titleDoes not contain (not ilike)

Expand
titleDoes not contain the case sensitive value (not like)

Expand
titleDoes not contain any of the following options (not in)

Expand
titleIs empty (null)

ADVANCED Filter Clause Operators

expand
Expand
title
Field equals

expand
Expand
Expand
Expand
titleField is not equal to

Expand
title
Filter Clause Operators

Click the inclusive operator that you want to learn how to use:

  • Equals to the value (=)

  • Greater than the value (>)

    • Greater
    Field is greater than (>)

    Expand
    titleField is less than (<)

    Expand
    titleField is greater than or equal to
    the value
    (>=)

  • Less than the value (<)

    • Less

    Expand
    titleField is less than or equal to
    the value
    (<=)

  • Contains the value (ilike)

  • Contains the case sensitive value (like)

  • Contains any of the following options (in)

    • is not empty (not null)

    Expand
    titleCustom

    Expand
    titleFilter Clause Operators

    Equals (=)

    For this operator, the data within the column name has to be an exact match.

    Example 1

    “Postal Code” = 3153 (only records with the exact match (3153) will be returned in the data set)

    Image Modified

    Example 2

    “Course Accredited” = YES (only records with the exact match (YES) will be returned in the data set)

    Image Modified

    Greater Than (>)

    Normally used for numeric values like date, credit points. Return values greater than what has been set.

    Example 1

    “Start Date” Greater Than (>) 2020-01-01 (values returned will start after 2020-01-01)

    Image Modified

    Less Than (<)

    Normally used for numeric values like date, credit points. Return values less than what has been set.

    Example 1

    “Start Date” Less Than (<) 2020-01-01 (values returned before 2020-01-01)

    Image Modified

    Greater Than Equal To (>=)

    Normally used for numeric values like date, credit points. Return values greater than and equal to what has been set.

    Example 1

    “Start Date” Greater Than Equal To (>=) 2020-01-01 (values returned will start from 2020-01-01)

    Image Modified

    Less Than Equal To (<=)

    Normally used for numeric values like date, credit points. Return values less than and equal to what has been set.

    Example 1

    “Start Date” Less Than Equal To (<=) 2020-01-01 (values returned on or before 2020-01-01)

    Image Modified

    In

    IN is normally used when selecting predefined options from the drop-down box. You can also multi-select the values which you would want to be included in your clause.

    NOTE: In Paradigm, when you use this operator, you need to click the UPDATE FILTERS button first before you will see the options under the column Value.

    Example 1

    “Course Level” In Graduate (all records with a course level “Graduate” will be returned)

    Image Modified

    Example 2

    “Course Level” In Graduate (all records with a course level “Graduate”, “Non-Award”, and “Graduate” will be returned)

    Image Modified

    Not In

    NOT IN is normally used when selecting predefined options from the drop-down box, and it will choose which of the predefined options should be specifically excluded from the results. You can also multi-select the values which you would want to be included in your clause.

    NOTE: In Paradigm, when you use this operator, you need to click the UPDATE FILTERS button first before you will see the options under the column Value.

    Example 1

    “Course Level” Not In Graduate (all records with a course level “Graduate” will not be returned)

    Image Modified

    Example 2

    “Course Level” Not In Graduate (all records with a course level “Graduate”, “Non-Award”, and “Graduate” will not be returned)

    Image Modified

    back to top

    Expand
    titleNested Filters

    Brackets

    Putting your clause inside brackets (parentheses) will tell the system to assess the conditions or clauses inside the brackets first before evaluating the clauses outside of it.

    Example

    Conditions

    AND

    This will return all the data or information which are true for all conditions separated by AND.

    Example

    “Start Date” > 01/01/2019 AND “Course Enrolment Status Id” In ENROLLED

    Tip

    Output

    Conditions allow for clauses to be joined together to form more complex filter clauses. e.g. “Start Date” > 01/01/2019 AND “Course Enrolment Status Id” In ENROLLED.

    This will produce a list of students who have a start date greater than 01/01/2019 AND have a course enrolments status of enrolled. To appear in this data set the record must satisfy BOTH conditions. Or if more conditions are added using the AND condition the record has to meet all of the required criteria to be returned in the produced data set.

    The example used for this report is the Centrelink report. This is a pre-saved report within the report builder

    “Person Status’ IN Active AND

    “Course Enrolment Status Id” IN Enrolled AND

    “Overseas Student” NOT EQUAL Yes.

    All three of these criteria have to be met for a record to appear within the data set produced for this report.

    OR

    This will return all the data or information which are true for any of the conditions separated by OR.

    Example

    “Start date” >01/01/2018 OR “Course Enrolment Status Id” in Enrolled

    Tip

    Output

    This will produce a list of students who have a start date of 01/01/2018 OR a Course Enrolment status of Enrolled OR both. The record only has to meet ONE criteria in the filter clauses to return a value in the produced data set.

    NOTE: You can have multiple filters (with AND and OR conditions) that can be mixed and matched to reduce the data set to produce a specific data set from the whole database.

    back to top

    Expand
    titleConfiguring the Report Parameters Screen
    Info

    INFO

    • If you want a report where users could dynamically enter data or information every time they run it (without having to run the report in the Report Builder menu, but just straight to the report type section where you saved the report, see the sample scenario below for an overview of this feature), you have the option to set the base report parameters.

    • You can set the parameters under the FILTER CLAUSES section or use one or more of the predefined parameters under the BASE REPORT PARAMETERS section.

    Anchor
    section1
    section1

    Using the FILTER CLAUSES section

    You can set the fields or parameters to be dynamically entered by the next user who will use your report by setting the last three column names under the FILTER CLAUSES section.

    • Appear On Form – when set to YES, the field will appear on the form for the user to enter a value each time the report is run

    • Form Label – will let you define the label or description of the field or value that you want the user to enter

    • Mandatory – when set to YES will require the user to enter a value to the field which are set as mandatory or required for it to run

    Example Report (UNIT ENROLMENTS INVOICED WITH NO GRADES): take note of the Form Labels, Appear on Form, and Mandatory columns which are set to YES.

    Report Form when run format: as a result of making the fields appear on the form and making them mandatory, the user will be prompted to enter the required values each time the report is generated

    Anchor
    section2
    section2

    Utilising the BASE REPORT PARAMETERS section

    You can also set the suggested or available parameters under this section if required for your reporting requirements. This is very helpful if you want the user to dynamically enter or change some of the dates on which the report will become required or needed.

    NOTE: When you want to add one or more of the available BASE REPORT PARAMETERS on this section, you simply follow the same instructions as above, by setting the Appear On Form column to Yes and also have the option to make the field mandatory by selecting Yes under the Mandatory column.

    The sample base report parameters below can be added and edited when using the Student Unit Enrolment Details base report.

    Anchor
    scenario
    scenario

    Example

    In the sample screenshot below, the Course Namefield is set to Appear on Formand Mandatory under the FILTER CLAUSES section, while in the BASE REPORT PARAMETERS section, the field Unit Census Date is also being set to appear on the form.

    With the sample scenario above, given that this report is saved under the Student type, once a user will run the report, those two fields (mandatory) will become part of the form and as set, the user can’t run the report if the two fields are not entered or set.

    back to top

    Expand
    titleSample Filter Clauses

    Example Clause 1

    (without configuring the report parameter screen):

    Tip

    Output

    In the sample clause above, all data within the specified start and end date (2018-09-26 and 2021-09-26), and with a Completed unit enrolment status Id will be displayed.

    Example Clause 2:

    UNIT ENROLMENTS INVOICED WITH NO GRADES Report (with a report parameter screen configuration):

    Tip

    Report Output Form Format when Run: (see screenshot below)

    back to top

    Workflow

    Status
    colourGreen
    title1
    Click the ➕ to add a new filter clause. In the first row, under the Bracket Open column, select a bracket if you are adding to add nested clauses, if not proceed to the Column Name field.

    Status
    colourGreen
    title2
    Select the field name that you want to add a filter clause to under the Column Name.

    NOTE: In this column, the field or column name you choose would not necessarily be included in your report output. You can choose the field here that could help you filter your result. See sample clauses above (Navigation > Sample Filter Clauses) as your guide.

    Status
    colourGreen
    title3
    Select a comparison operator under the Operator column required for the field you chose under the Column Name (see above section (Navigation >Basic Filter Clause Operators)or the Complex Filter Clause Operatorspage for a definition of each possible operator you can use).

    Status
    colourGreen
    title4
    Enter the value you want for the column name to be true against the comparison operator selected.

    Tip

    In Paradigm, when you use the IN and NOT IN operators, you need to click the UPDATE FILTERS button first before you will see the options under the column Value.

    Status
    colourGreen
    title5
    Select a condition or logical operator (AND or OR) if you want to add another line of clause or condition (see above (Navigation >Nested Filters section).

    Status
    colourGreen
    title6
    Click the ➕ before the bracket column every time you add another filter clause or condition.

    NOTE: Only the filter clauses inside the circle with the ➖ (minus) sign will be considered as valid or part of your clauses. Normally, once you’re done with adding all your filter clauses, there will still be one circle with the ➕ plus sign on it left at the very end, disregard this and proceed to the next step.

    Tip

    OPTIONAL STEP

    Refer to the (Navigation > Configuring the Report Parameter Screen) section above if you want some of the fields to be dynamically inputted by the user each time a report is run.

    Status
    colourGreen
    title7
    Once you’re done with adding all the required filter clauses you want for your report, click the UPDATE FILTERS button.

    Status
    colourGreen
    title8
    Proceed to the next page for the for the next workflow or instructions.

    Previous Page

    Report Builder Main Page

    Next Page

    Introduction to Report Builder | Step 1 - Select a Base Report | Step 2 - Add and Remove Fields | Step 3 - Rename and Reorder Fields | Step 4 - Add Filter Clauses | Step 5 - Save Report