Report Building

Make Selections

There are several ways to select data within Hubble :

Filters
Visual Assist
Query by Example (QBE) Line

Filters

Filters, placed at the top of the screen, allow you to narrow down your inquiry results by selecting the data to be displayed. Each template has different filters. The filters shown below are those in the General Ledger Balances Template:

Show/Hide filters - To display more data on the screen, you can hide the filter selection by clicking the up arrow symbol at the top left corner of the filter section. Once hidden, a down arrow symbol appears and clicking it will display the filter section again:

An asterisk (*) is a wildcard, meaning all selections will be returned for that filter unless a specific value(s) is entered.

Once you have tabbed out of the filter or clicked somewhere else, the filter will highlight in red if the selection is invalid. If you run the inquiry, you will receive a validation error that tells you which value(s) in the filter is not valid.

Filter data is verified using the model Business Unit(s) defined during the application configuration process. Therefore it is possible that the user may enter what they consider to be a valid entry, only for it to be rejected as being invalid. In such cases it is highly probable that the entry has not been included within the model business unit. This scenario can be avoided through the use of Visual Assists or account ranges.

Multiple filter selections can be made by using a comma to separate values. When multiple values are entered into a particular filter, the background color of the filter is changed to green. This is for visual purposes only and does not change any functionality.

Note: The only exception to this is when in Chinese input mode. In this case, Chinese characters, commas and colons are not recognized by filters. This is due to the comma and colon being different characters in Chinese punctuation.

When making multiple selections, each selection is validated. If any of the selections are invalid, the entire filter is shown in red. To determine which value(s) is invalid, hover over the filter to see a help message with this information.

Types of Filters

There are four types of filters:

Standard Filter - The most common type filter is the Standard Edit Filter; you can select data by typing directly into the filter or by using the associated Visual Assist to make selections. An example is the Object Account filter:

Group Filter - Group filters are a special type of standard edit filter and share many common features, such as the option to type directly into the filter and use a Visual Assist. The unique aspect of a group filter is its ability to work with multiple sets of related items.

Group filters are used to group items together - category codes, attributes, or subledgers. The below example shows how to make multiple selections from both Business Unit Category Codes 01 and 02. In this case, we are selecting values 100, 115 and 120 from BU Category Code 01 and values 210 and 220 from BU Category Code 02.

The entered group filter will be reflected in the values displayed in any associated component filter fields displayed for the inquiry. For example, entering above group filter will result in this being displayed in the Division field:

In this example, we are selecting all values from BU Category Codes 02 and 04, and all values from 01 except 110 and 140 (note the use of the <> “not equals” filter option to exclude those values).

See below for details of the options that are available when typing in filters.

Date Filter - The Date Filter allows you to select data based on a period or range of dates using from the From and To filter controls.
Hierarchy Filter - The Hierarchy Filter allows you to select and apply selection criteria to any hierarchies that have been created:

There are two ways to select criteria in a filter:

Manual entry
1. Click within the filter.
2. Type in the value.
3. To validate the value, click out of the filter using the left mouse button or press Tab to go to the next filter.
Visual Assist

More on Standard Filters

A standard edit filter allows you to input/select values for one data element. The following options can be used in standard filter fields. These can be used in any combination by separating each value with a comma.

OPTION	EXAMPLE	DESCRIPTION
Item	A	Items that are equal to the one item listed in the filter.
List of Items	A,B,C	Items that are equal to the list of items in the filter.
Range	A:C	Items that are in the range of values in the filter (This feature is only available for manual entry within the filter itself, rather than with the Visual Assist. However, when using the Visual Assist, any ranges that have been manually set are displayed).
Greater Than	>C	Items that are greater than the value specified in the filter.
Greater Than or Equal To	>=C	Items that are greater than or equal to the value specified in the filter.
Less Than	<C	Items that are less than the value specified in the filter.
Less Than or Equal To	<=C	Items that are less than or equal to the value specified in the filter.
Not Equal to	<> or !=	Items that are not equal to the value specified in the filter. This filter can be applied by typing in != or <> or by using the <> button: See below for more details of this filter control.
Wildcard	C*	The wildcard is positional and will bring back all the items that start, end or contain a specific value. This example will return all values that start with the letter C.

Note: Filter fields that are used to define the amount columns (e.g. Period, Ledger & Year on the Balances Template) can only use single values or lists.

Filter on Blank Text

BLK is the default setting for BLANK. The text can be used as a single value or as part of a list of values. This special text is configurable in Report Options, as is the setting for Missing Text.

The options when right-clicking on a standard filter include:

Reset Filter – Resets filter to original default value.
Show Descriptions in Filter – Set whether the descriptions of the filter values display in the filter itself.
Show Descriptions in Grid - Set whether the descriptions of the filter values display in the inquiry results.
Include Nulls – Include null values in the result set.
Include Non-Matching – Include non-matching values in the result set.
What’s This? - Displays the associated caption, file name and field name.

Not Equals Filter Control

A “not equals to” filter can be applied either by typing in != or <> or by using the <> button:

The button applies the filter to all values in the filter field that do not already have this filter applied. For example:

If the symbol on the button is red, this indicates that a not equal to filter has already been applied to all the values that t cane be applied to.

Click to remove all of them.

The button will be disabled if the filters in a field include modifiers such as <,>,<=, and >= that prevent Hubble from changing to “not equal to” and “equal to” and back.

More on Group Filters

Right-click in a group filter to view and select from lists of available attribute and category codes and their respective descriptions:

Once a group is selected (such as BU Category Code 04), a standard column representing that group will automatically be displayed in the inquiry results.

Note: A user with a Configurator license can make an individual category code as its own filter, separate from the group filter.

Selecting specific values in a group filter - Once selected via right-clicking with the mouse, a syntax is created with a wildcard to show all values. Either manually type in values in place of the wildcard or use the Visual Assist to select specific values.

In the below example, we are filtering for records where:

Business Unit Category Code 1 = 100 or 115 or 120 AND

Business Unit Category Code 2 = 210 or 220

And/or logic when using multiple fields in a group filter - When multiple fields are included in the filter, it is possible to toggle between AND/OR logic by right-clicking on the filter and selecting the appropriate option. To uncheck the item once it has been selected, right-click on the filter and click on the item to unselect it.

Display Padding Characters Option when right-clicking on a group filter - This option enables the display of any leading spaces or zeros from the code for presentational purposes only. (Only the codes and attributes that have been chosen for inclusion are available for selection. This is set by profile in Administrator).

More on Date Filters

When the dates in a date filter are grayed out, it means that open-ended ranges are being used. When both the From and To filters are grayed out, as shown below, it means all the dates within the system are selected:

To change either the From or To date, follow these steps:

Check the box to the left of either the From or To filter:

Click in the filter and edit the date by doing either of the following:

Type the date directly into the filter.
Select from the drop-down calendar that appears when you click the down arrow of the filter. Once the calendar displays, you can also click the little arrows to the left and right of both the month and year to for more selections:

Combined Date Filter - This enhanced date control is only found on certain transaction inquiries in the General Ledger, Fixed Assets, Job Cost and Advanced Cost Accounting Modules when using weekly reporting:

Selecting Period modifies the filter controls to select Period numbers, and selecting Weeks modifies the filter controls to select weeks. This filter cannot be disabled as it is a safety feature to prevent the inquiry searching across the entire Account Ledger (F0911).

Note: The combined date filter defaults to the JD Edwards current period (General Accounting current period for company 00000).

View Filter Selections

In the Filter Selections dialog, you can view your filter selections.

On the Design menu in the Setup group, click Filter Selections.
In the Filters Selection tab, view all the filters currently being used to select the data returned on the inquiry.

In the Make Selections tab, change your filter selection as needed. This will update the filters in the main inquiry screen.

Other Notes on Filters

Adding new Ledger Types - In JD Edwards, you must first add the ledger type in UDC 09/LT. Then you must go to P0025, Work with Ledger Types, and add it there. Once you do this, you will see the correct description displaying in Hubble.

Difference between CE (Cumulative Amount) and ITD (Inception to Date) - For General Accounting, the Balance Sheet accounts and P&L (profit and loss, aka Income Statement) accounts are treated differently. For the Balance Sheet accounts, as you move into a new year, you always want to consider the beginning balance (APYC field). For example, if you bought some equipment in 2004 for 1 million, that is still going to be an asset in 2005. So to calculate your assets & liabilities correctly, the APYC must be included. Contrast this with Income Statement accounts. For example, you made 2.5 million in revenue last year but obviously you cannot include last year's revenue in your calculation of this year's revenue. Therefore, the CE will include the APYC for balance sheet accounts and not income statement accounts whereas the ITD will include the APYC even for P&L accounts. Below is an example.

Visual Assist

Visual Assists, available for each filter in Hubble, provide a way to search for filter selections.

Activating the assist: Click on a filter and then click the magnifying glass button to the right-hand side of the filter

This brings you to the Visual Assist dialog that is specific to that filter. It is split vertically into two halves; the left panel displays a list of available selections and the right panel displays the list of current selections. Using the arrows, you can move the selections as needed.

Visual Assist Ribbon Options

Visual Assists, available for each filter in Hubble , provide a way to search for filter selections.

ICON	FUNCTION	DESCRIPTION
	Find	Locate and display all items matching the current search criteria that are ready for selection.
	Clear	Clear the list of current and available selections as well as the QBE filter settings and restore them back to their defaults.
	Export to Excel	Export the results of the Visual Assist dialog to Microsoft Excel.
	Print	Print the results of the Visual Assist dialog to PDF format.
	Sequence	Sequence and group the list of available selections.
	Show Tree	Display all available items in a hierarchy layout (particularly useful when there are several tabs associated with the data, such as with group or data filters).
	Split	Organize individual items such as category codes and periods available for selection onto separate tabs (only available on the data and group filters).

Visual Assist Usage

To use the Visual Assist:

Find the available data from which to make selections. Optionally, use the Query by Example (QBE) line to narrow down search criteria.
1. Click the Find button to return results. Any results returned are shown within the available selections panel. The number of items returned is shown on the status bar at the bottom of the dialog.
2. Move to the right panel any selections you wish to use in the filter.
3. Click OK to return to the inquiry.

Note: When using the Visual Assist for the Object Account and Subsidiary Filters, the Model Business Unit (set during the configuration process in Administrator) is used in the QBE Business Unit column. This can be changed by double-clicking in that filter and typing new search criteria.

To move the data between the left and right panels, you can do either of the following:

Double-click the item(s)
Select the item(s) and use the arrows in the middle (the double arrows moving ALL data from one panel to the other).

Note: You can multiple-select by holding down the CTRL or SHIFT key while selecting the items.

In the right panel (Current Selections), the order in which the selections are displayed represents, in left- to-right visual order, how they will be displayed in the filter once you return to the main document panel. If a column is pivoted, it affects the placement of the columns.

Warning message upon clicking Find - These warning messages appear on filters with potentially large result sets. However, they can be turned on and off in the Warnings dialog or by clicking the Don’t show again checkbox on the warning message.

Split by Column function within the Visual Assist - This feature, available for group filters, makes it possible to order the list of available selections into logical groups rather than just displaying them as a long list of entries within the Visual Assist screen.

By default, the Split by Column functionality is enabled, which results in each group being placed on its own tab. For example, 30 Business Unit Category Codes will be placed on 30 separate tabs.

The split option, once disabled, will put the selections on the All tab. Click on the Split byColumn Button to enable or disable the functionality.

When enabling the Split by Column functionality, you will be presented with the Split by Column dialog:

Selecting Group Code means the tabs will be identified by the code.
Selecting Group means the tabs will be identified by the description of the group.

Query by Example

When a field does not have a Visual Assist, you can filter on the column by using the Query by Example or QBE line as shown in the below example. You can also use this in conjunction with a filter so the two criteria work in conjunction to return the data being selected.

To use the QBE line, double-click in the white space above the relevant column and type in the search criteria.

In the example below, you see two different filtering mechanisms:

Exact Matching: For example, to search for Object Account 5010, enter the exact value into the Object Account Column in the QBE line. All accounts matching this value will be displayed.
Wildcard Matching: Some text is used to identify selections, giving a match at the beginning, end, or both beginning and end of the character string. For example, the asterisk character (*) is used to search for Object Accounts that start with 11, e.g. 1105, 1110, etc. Had the asterisk appeared prior to the value 11 (i.e *51), then Object Accounts such as 3111, 2511, etc. would be returned using this criteria. Searching on *Cash* returns anything with “Cash” in the Account Description that also fits the other search criteria.

Note: Filtering based on exact matching is the quickest filtering mechanism. Full wildcard matching is the slowest. Therefore, it is recommended that you use the most appropriate matching mechanism for your desired search.

Entering multiple filter criteria within a single QBE filter or column - You can enter in multiple filter criteria in a single QBE filter. For example, to search for items with ‘Banks’ and ‘Cash’ in the object account description, the QBE would *Banks*,*Cash”.

QBE Options - The below options can be used in any combination by separating each value with a comma.

The following can be used with either text (label) or numeric (value) columns:

OPTION	EXAMPLE	DESCRIPTION
Equal	=	Combined with other options, this will return all values equal to the selection. If not specified otherwise, an equal sign is always assumed.
Not Equal	<>	Combined with other options, this will return all values not equal to the selection.
Item	A	Items that are equal to the one item listed in the filter.
List of Items	A,B,C	Items that are equal to the list of items in the filter.
Range	A:C	Items that are in the range of values in the filter.
Wildcard	C*	The wildcard is positional and will bring back all the items that start, end or contain a specific value (this example would return all values that start with the letter C).
Blank	<space>	A space represents blank values in the QBE line.

The following can be used with text columns (labels):

OPTION	EXAMPLE	DESCRIPTION
Greater	>C	Items that are greater than the value specified in the filter.
Greater Than or Equal To	>=C	Items that are greater than or equal to the value specified in the filter.
Less Than	<C	Items that are less than the value specified in the filter.
Less Than or Equal To	<=C	Items that are less than or equal to the value specified in the filter.

The following can be used with numeric columns (values):

OPTION	EXAMPLE	DESCRIPTION
Greater Than	>10	When a number is entered, it is treated as an absolute value (this example would return items that are greater than 10 or less than -10),
Greater Than or Equal To	>=10	When a number is entered, it is treated as an absolute value (this example would return items that are greater than or equal to 10 and less than or equal to -10),
Less Than	<10	When a number is entered, it is treated as an absolute value (this example would return items that are less than 10 and greater than -10).
Less Than or Equal To Number	<=10	When a number is entered, it is treated as an absolute value (this example would return items that are less than or equal to 10 and greater than or equal to -10).
Greater Than Signed Number	>+10	When a specific sign (‘+’ or ‘-‘) is used, only values that are greater than that value will be returned (this example would return items greater than +10). The same logic can be used for negative values with a ‘-‘ symbol.
Greater Than or Equal To Signed Number	>=+10	When a specific sign (‘+’ or ‘-‘) is used, only values that are greater than or equal to that value will be returned (this example would return items greater than or equal to +10). The same logic can be used for negative values with a ‘-‘ symbol.
Less Than Signed Number	<+10	When a specific sign (‘+’ or ‘-‘) is used, only values that are less than that value will be returned (this example would return items less than +10). The same logic can be used for negative values with a ‘-‘ symbol.
Less Than or Equal To Signed Number	<=+10	When a specific sign (‘+’ or ‘-‘) is used, only values that are less than or equal to that value will be returned (this example would return items less than or equal to +10). The same logic can be used for negative values with a ‘-‘ symbol.

Invalid selection in the QBE line - Unlike filters, there is no warning message to tell you that you have entered in invalid data into a QBE line. Data selections made in the QBE line are not validated against your ERP data. If the selection you enter does not match any data, no results will be returned.

Multiple values in the QBE filter that are all ‘not equal to’ values - To enter in multiple ‘not equal to’ values, you need to put the ‘not equal to’ sign before each value, for example <>301, <>302, <>303.

Filters in combination with a QBE line - You can narrow down your data selection using both filters and the QBE line. For example, you could define a range in the Object Account filter and use the QBE line to ignore a specific object account within that range:

Null Values

You can filter over null values by either using the QBE (Query by Example) line OR by using a Filter.

To filter over null values by using the QBE line:

Use the equals sign (=) in the specific column for which you want to filter over null values. In the example below, the = sign in the Subsidiary column results in all rows being returned with a null value in that column:

When you run the report, the text in the QBE changes to “=NULL”:

To filter over null values using a filter for any inquiry that uses more than one table in either a standard inquiry or one that has been created by Designer Express:
1. From within an inquiry, right-click on the filter for which you would like to display nulls and select Include Nulls.
If you have the Configurator license: from the Configurator tab, select Filter Layout, right-click on the desired filter in the right panel and select Always Include Nulls.

To define the text that appears for a null value on an inquiry:

From the Home tab within an inquiry, select Inquiry Options.
Type the desired text in the Null Value Text filter.

Examples: Include Nulls and Include Non-Matching options

In the first example, we will use an HR inquiry.

When filtering on Date Worked from the F06116, we only get active employees who have a timecard record for 4/1/2005 through 4/15/2015. 11 records are returned:

Change the filter to include nulls. This shows active employees with timecards between 4/1/2005 and 4/ 15/2005 AND active employees with NO timecards in the F06116. 29 records are returned.

Change the filter to include non-matching rows. This shows all the active employees in the F060116 and those employees that have a timecard in the F06116 for the dates 4/1/2005 through 4/15/ 2005. 117 records are returned.

For the second example, the scenario is that you have a report that combines customer data with address data and you want to filter on a country, say the United States.

If you only say “US” in the filter, then the report will return just the customers with an address in the US.
If you say “always include nulls”, it will also include rows where the country was set to Null.
If you say “include no match”, it will include all customers but will only display address data for customers from the US.

Non-Matching Values

There is an option to show non-matching values, meaning rows which do not match the criteria in a specific filter. You can set a specific filter to return non-matching rows if you wish to have visibility to the rows themselves but not include the data from the rows.

For example, in this Master Data report you have set the Phone No Type to include non-matching rows. No data is returned from the F0115 table for Suppliers 4319, however it is displayed from the F0101 so you still have visibility to this row.

You can filter on non-matching values for any inquiry that uses more than one table in either a standard inquiry or one that has been created by Designer Express.

From within an inquiry, right-click on the filter for which you would like to display non-matching values and select Include Non-Matching.
Run the report.