Skip to main content

Create and Run Inquiries

Create a New Inquiry

There are two ways to create a new inquiry:

  1. From the Backstage view, click New.
  2. Use the Quick Access Toolbar, if configured.

Run an Inquiry

When all of the filters have been defined, click on the Run button (on the Home menu in the Documents group) to run the report. The result set is shown in the panel in the bottom half of the screen. Alternatively, you can add the Run button to your Quick Access Toolbar and use this instead.

Hotkey for running an inquiry: The F5 function key is a shortcut for running an inquiry.

Save an Inquiry

Inquiries can be saved in any of these ways:

  1. From the Home menu in the Documents group, click Save.
  2. Use the Save button in the Backstage View.
  3. Use the Quick Access Toolbar, if configured.

If the inquiry has not been previously saved, you are presented with the Save Inquiry As dialog. By default, this dialog initially opens within your own ‘My Inquiries’ folder. To save an inquiry, provide an inquiry name by typing the desired name and clicking Save.

The buttons used in this dialog are similar to those used to access files elsewhere within Windows.

BUTTON FUNCTION DESCRIPTION
Back Go back to the last selection within the tree
Forward After using ‘Back’, go to your most recent selections
Up To Parent Move your selection up the tree directly to the parent level
Go To Your Folder Return to your own ‘My Inquiries’ folder
Create New Folder Create a new folder
Copy Copy the selected object to the clipboard
Cut Cut the selected object from its current location to the clipboard
Paste Paste from the clipboard
Delete Delete the selected object
Refresh Refresh the dialog’s contents
Show/Hide the Workspace Tree Show/Hide the workspace tree on the left side of the dialog
View Switches from Details to List View

When an inquiry has been saved, the name will appear at the bottom of the screen, above the Status Bar:

Set Titles

You can add a title to your inquiry, and within that title you can use either static text or dynamic text.

Static text is text that always stays the same, whereas dynamic text is based on filter selection and updates according to the filter selections. For example, if you select the dynamic selection of <Period> in the title, each time the Period filter data changes, the title will be dynamically updated to display that period. If you filter on the period of March, the title would display “March”; if you filter on the period of April, the title would update to show “April”. In the Inquiry Title dialog, any dynamic text is denoted with the < > characters.

Add a New Title

To add a title to an inquiry:

  1. On the Design menu, in the Setup group, click on Inquiry Title.
  2. In the first field, enter the inquiry title. You can set static text, dynamic selections, or both in the inquiry title. These can be positioned over two rows. As the name suggests, the Preview section shows a preview of the title.

  3. The default title is <Saved Report Name>, which dynamically updates according to the saved inquiry name. This can be removed, if desired, to create a different title.

Edit a Title

You can edit an inquiry title through the Inquiry Title dialog (Design menu > Setup group, > Inquiry Title).

Once a title is displaying in an inquiry, a shortcut to accessing the Inquiry Title dialog is to double-click on the title itself.

Use Static vs. Dynamic Text

To set static text in the Inquiry Text dialog, manually type in the text you wish to display in the title.

To add dynamic text to your inquiry title using the dynamic selections complete the following the steps:

  1. In the Inquiry Title dialog, put your cursor in the title section at the top of the dialog. You will see a preview of the title, as you are creating it, in the window at the bottom of the dialog.
  2. Some templates have a default title, such as Saved Report Name. (This is available via the Special Dynamic Selections drop-down menu.) You can leave this or change it, if desired.
  3. If you wish to use values from other filters in the title, choose the appropriate filter field in the drop-down menu.
  4. Click Add next to the designated item.
  5. If you wish to use a Special Dynamic Selection, such as RunDate, select the appropriate value in the drop-down menu. In some cases you will need to select how you want the data to display, so you will do this in the drop-down field to the right of that.

  6. Click Add.
  7. Check the box to Show Title if you wish to display it on the inquiry.
  8. Click OK to exit the dialog and return to the inquiry.

In the example below, we have added two lines of dynamic text. Below are the steps on how this was done.

  1. Select Saved Report Name from the Special drop-down in the Dynamic Selections of the dialog and select Add.
  2. Add the run date by selecting RunDate from the Special Dynamic Selections drop-down menu. Next to that, select Short Date.
  1. Click Add.
  2. Add the run time by selecting RunDate from the Special Dynamic Selections drop-down menu. Next to that, select Short Time.
  3. Click Add.
  4. To format any of the dynamic text, highlight the specific text and choose the formatting using the controls above the title box.

Note: Note that title formatting set up in Desktop will not be displayed in Hubble Web as Hubble Web uses its own standard format.

  1. Check the box to Show Title to display it on the inquiry.
  2. Select OK.

The below example on a Profit and Loss report shows the static text “Profit and Loss Report” on the first line, and on the second line the Business Unit filter is a dynamic selection. In this case, there are two business unit values in the Business Unit filter, so both display in the actual title:

Dynamic filter selection: description vs code – You can make the titles be even more dynamic to display filter labels and/or filter codes, as well as display only the information for certain values within a filter. All of these dynamic selections are identified by the syntax symbols < and >. The default is to show the filter label (such as the specific name of a Business Unit that is in the Business Unit filter), however you can show the filter code (such as the specific Business Unit number) instead.

If you want to show the codes of the filter values (in this case Business Units) instead of the labels, you would change the title to look like this, entering in a colon and the letter "N" within the brackets:

Following is a list of examples that can be done using dynamic titles.    

Dynamic Title Syntax Options

OPTION

SYNTAX

DESCRIPTION

EXAMPLE

Show Default

<Business Unit>

Displays the descriptions for all values in the filter

Distribution Center,

Eastern Distribution Center,

French Distribution Company

Show Label

<Business Unit:Label>

or

<Business Unit:L>

Displays the descriptions for all values in the filter.

Distribution Center,

Eastern Distribution Center,

French Distribution Company

Show Code

<Business Unit:Code>

or

<Business Unit:N>

Displays the codes for all values in the filter.

75,30,75

Show First Filter

<Business Unit:Label:1>

Displays the description for the first value in the filter. Adding a number to the variable is position based (1=first item in the filter, 2=second item in the filter, etc.). Positional logic can only be used in conjunction with label or code options.

Distribution Center

Show Label and Code

<Business Unit:N:1>

Displays the description and value for the first

75 Distribution Center

30 Eastern Distribution

Displaying Selected Filter Selections in the Title - You can modify the syntax of the title to set which filter selections you wish to display; this is done by adding a number to the variable and making it position-based (1=first item in the filter, 2=second item in the filter, etc.). Positional logic can only be used in conjunction with label or code options.

For example, if you have these values in your period filter: CE3,CE6,CE9,CE12 but only want to display CE12 in the title, you would set the dynamic title as <Period:N:4> since CE12 is listed 4th in the filter:

User Defined Captions and an Inquiry Title - If the filter selection has a User Defined Caption set for it, this will be used instead of the text or description of that filter. For example, I have a UDC or User Defined Caption set for Period 1 = January:

  • A title set as: <Period> would display as “March”
  • A title set as <Period:N> would still display as “3”

Display a Title

Make sure you have set the title to display, which can be done in either of these two places:

  1. Check Show Title in the Inquiry Title dialog (Design menu > Setup group > Inquiry Title).
  2. On the View menu, in the Show/Hide group, click Title.

Print/Export a Title

To set whether a title is included when printing or exporting:

  1. Go to PDF Options/Excel Options, depending on whether you are exporting to PDF or Excel.
  2. Go to the Report tab and mark the checkbox next to Show Title.
  3. Once you export the report, the title will be included.

Format a Title

To format an inquiry title:

  1. Go to the Inquiry Title dialog (Design menu > Setup group > Inquiry Title).
  2. Here you can set a font, font color and alignment of the title text. Note that different formatting can be used throughout the title.
  1. A preview is shown at the bottom of the dialog.
  2. Click OK to exit the dialog and return to your inquiry.

Hyperlink in a Title

Follow the below steps to insert a hyperlink into an inquiry title.

  1. Go to the Inquiry Title dialog (Design menu > Setup group > InquiryTitle).
  2. Select the Hyperlink icon.

  1. Enter the Text to display that you would like to appear in the title as well as the Address for link to the website you would like to access. Alternatively, you can define a network folder in the Address field to navigate to an instruction sheet or other necessary document for an inquiry.

  1. Now when you click on the link embedded in the inquiry title, you will be taken to the website defined for that hyperlink.

Reference Boolean and Date Report Variables within an Inquiry Title

In certain templates, such as the Rent Roll template in Real Estate Management, we have filters that are derived using Date Report Variables and Boolean logic.

In the Rent Roll template you have the filters:

  • Show All Rent Steps
  • Validate Lease Dates

If you add these variables to your report Title they will display as Yes or No depending on whether they have been activated (selected) or not:

In this case, we added the static text “Show All Rent Steps” before adding the Special Dynamic Selection, and the same with adding the static text “Validate Lease Dates” before adding that Special Dynamic Selection:

Depending on the template being used, there are different Special Dynamic Selections that can be selected, such as dates, times, and in the above case, yes/no filters.

Formatting

If you want all inquiry results, or even just inquiry values, to display in a certain way, you would set this in default formatting. For example, if you may want to display all values in thousands or with a certain number of decimals, make that setting a default in the Defaults tab of Formatting. Default settings can overridden by any other formatting styles.

Default formatting can be set for any of the following:

  • Default - all data in the result set
  • Default (Numbers) - all values in the result set
  • Disabled Input Cell Style - budget cells where entry is not allowed for the user
  • Input Cell Style - cells where budgeting data is being entered
  • Input Cell Validation Error Style - cells which fail validation
  • Inserted Row Style - planning rows that has been inserted into a form by the user
  • Missing Row Style - displayed when the Missing Balances Feature is turned on, this is for rows representing accounts with a missing balance in the JD Edwards Account Balances Table (F0902)
  • Model Cell Style - used with all cells in which the Model Scope has been applied in Budgeting When formatting is applied within Hubble, it will be applied to any output. Note that formatting of column headers and titles are only displayed in Hubble Desktop. The Hubble Web has its own standard formatting for headers and titles.

When formatting is applied within Hubble, it will be applied to any output. Note that formatting of column headers and titles are only displayed in Hubble Desktop. The Hubble Web has its own standard formatting for headers and titles.

When you change the formatting for values, such as reversing the signs, it does NOT change the value itself - it is only changing the display of the value. This is important to understand, especially in the case when calculations are being performed. If the displayed values and actual values are different, the calculations will use the actual values, not the displayed values.

Formatting can be performed in any of the following ways:

  1. On the Formatting menu, in the Format Types group, click on the button representing the type of formatting you wish to change (see Formatting the Content of Columns).
  2. Interactive column formatting: right-click on a column and choose Format Column to access the Formatting dialog (see below).
  3. Headings and totals: in the Columns, Sorting & Grouping dialog, go to the Sorting & Grouping tab and click on the Formatting button next to its respective item (see Other Column Design Options).

Formatting Dialog

The tabs within the Formatting dialog that the types of items to format; formatting can be applied to any of them:

  1. Defaults (change default format settings to all inquiry results or values only)
  1. Headers
  2. Totals and Subtotals
  3. Columns
  4. Column Headers
  5. Hierarchies
  6. Hierarchy Levels
  7. Conditional
  8. Priority

Some tabs already have formatting styles created by default (Defaults, Headers, Totals and Subtotals). To change the formatting style, select the item and click Edit to open the Define Formatting Style dialog.

Other tabs, such as Columns, do not have any formatting styles defined yet. To create one, click New to define a style for your selected item.

When formatting a column, select the column you wish to format (all columns in your inquiry are listed).

The Define Formatting Style dialog opens, and you are able to set up the formatting style.

With the exception of Conditional formatting (see below), regardless of the item for which you are defining or editing a formatting style (e.g. totals, columns, hierarchy rows), the same dialog and options are available. The Define Formatting Style dialog includes the following 3 tabs.

General Tab

Format value columns: prefix, suffix, decimal places, thousands separator, display negatives in red, reverse the signs, display a sign, cell alignment, and value divisor. Note that the formats grouped together in Override Inherited Formats are only enabled when the checkbox is marked. Only one associated function needs to be enabled for the others to be enabled and affect formatting priority.

Within Override Inherited Formats:

  • Prefix - provide a prefix character to identify the currency of the value
  • Suffix - provide a suffix character(s) to the data values (e.g. %)
  • Decimal Places - increase or decrease the decimal places shown
  • Thousands Separator - toggle whether a thousands separator is used
  • Show negatives in red - toggle whether negative data values display in red
  • Reverse Signs - reverse the value sign, i.e. to show revenues as positive numbers
  • No Positive Sign/Always Show Sign/Parenthesize Negatives - set how value signs are displayed

External to the Override Inherited Formats:

  • Alignment - Horizontal - set the alignment of the data value (left, center or right)
  • Alignment - Vertical - set the alignment of the column header (top, center or bottom)
  • Divisor - allow data values to be divided down or multiplied up (e.g. to show values in thousands, millions, etc.)
Appearance Tab

Format the appearance of data:

  • Font Color - Set the text color
  • Back Color - Set the background color of a cell
  • Font - Set font type, style, size, script, underline, and strikeout. Check the option you wish to enable
  • Transformation - Set the text transformation option, if required, i.e. whether text is to be automatically transformed to all uppercase, to title case, or to all lowercase
  • Suppress data - Suppress the data displayed in the cell
Borders Tab

Set borders and row spacing:

  • Border - Set top/bottom/left/right/horizontal/vertical borders. Using the first drop-down menu, you can select the line type of border and using the second drop-down menu, you can set the border color.

Note: The option for double lines is useful for financial statements as it is commonly used to indicate the final total of a statement.

  • Row Spacing - add white space before or after a row. (This is often used to provide space between different groups defined within the Columns, Sorting and Grouping dialog.)
  • Preview Style - This displays how the current item is formatted. This is especially helpful as you can preview the current formatting settings and change them as needed without having to completely exit the dialog.

Other available options in the Formatting dialog:

  • Remove - remove a user-defined formatting style
  • Reset - reset a default formatting style back to its original settings

To use these settings:

  1. Select the formatting style you wish to remove or reset.
  2. Click Remove or Reset.

Formatting Group Headers

In the Headers tab in the Formatting dialog, some styles are set by default depending on the template you are using; any of these can be changed by:

  1. Selecting the header you wish to edit.
  2. Clicking Edit to open the Define Formatting Style dialog.

Group headers for other columns that are NOT displayed by default can be edited once you set up these headers in the inquiry through Columns, Sorting and Grouping.

Formatting Advanced Aggregations

In the Totalsand Subtotals tab in the Formatting dialog, some styles are set by default depending on the template you are using; any of these can be changed by:

  1. Selecting the item you wish to edit.

  2. Clicking Edit to open the Define Formatting Style dialog.

The formatting styles for the advanced aggregations, such as average, count, maximum, etc. do not display in the Totals and Subtotals tab in the Formatting dialog until at least one aggregation is being used. For example, once you set up the average for the Company Column, you will see the formatting styles for Average on Company as well as the other advanced aggregations: Count on Company, Maximum of Company, Minimum of Company, etc. Any of these styles can be edited as previously described.

Formatting Hierarchies

To format a hierarchy, be sure that you have added the hierarchy to your hierarchy filter in order to make it active. Then you can format the hierarchy as desired.

Hierarchy formatting is done through the Formatting dialog and can be done by hierarchy level or hierarchy node:

  1. Hierarchy Levels tab - Set formatting on specific hierarchy levels so that all nodes at that level are formatted in a certain way.
    1. You must first have a hierarchy created in your inquiry.
    2. Within the Formatting dialog, click on the Hierarchy Levels tab.
    3. Highlight the level you wish to format.
    4. Click Edit.
    5. This launches the Define Formatting Style dialog where you can set the format.
  1. Hierarchies tab - Set formatting on individual nodes within the hierarchy.
    1. You must first have a hierarchy created in your inquiry and have it selected/active in the Hierarchy Filter.
    2. Within the Formatting dialog, click on the Hierarchies tab.
    3. Highlight the node you wish to format.
    4. Click New.
    5. This launches the Define Formatting Style dialog where you can set the format. There are two formatting options that are specific to hierarchy nodes:
      1. Label Indent -available in the bottom left corner of the General tab, this option applies a row indentation separate from the hierarchy indentation
      2. Values Only - available in the Borders tab, this option applies a border to the top or bottom of the value columns (so there is no need to use conditional formatting to select values only)
Creating a Blank Row

You can create blank rows either within a hierarchy or without a hierarchy. There are 2 ways to create a blank row within a hierarchy.

Option 1: Format Hierarchy

  1. Create your hierarchy.
  2. From the Formatting menu, select Hierarchies.
  3. Using Ctrl, click to select the rows where you want additional spacing.
  4. Click Borders and select row spacing.

Option 2: Create a label entry in the hierarchy

  1. Position a label entry in the hierarchy wherever you want a blank row to appear.
    1. Type: Label Entry
    2. Name: Blank1 (subsequent blank rows: Blank2, Blank3, etc.)
    3. Display: Always
    4. Reverse signs: Unchecked
    5. Roll values up into this one: Unchecked
  1. Create a conditional format.
    1. Condition: Select a label that contains the text 'Blank'. (This is case sensitive. Do not enter quotes or wildcards.)
    2. Apply to: Entire Row
    3. Set Format: Check the Suppress Data Box on the Appearance tab

There are 2 ways to create a blank row when not using a hierarchy.

Option 1: Format from the Formatting menu

  1. Go to the Formatting menu and select either Headers or Totals.
  2. Select the specific header or total to which you want to add spacing.
  3. Click Edit to change the format definition.
  4. Click Borders and select row spacing.

Option 2: Create and suppress an aggregation

  1. Go to the Design menu and select Columns, Sorting & Grouping.
  2. Click the Sorting & Grouping tab.
  3. Click the Advanced button.
  4. If it is not yet created, create an aggregation at the level you want a blank row.
  5. Click the Format Icon for this aggregation.
  6. Check the Suppress Data Box on the Appearance tab (this completely suppresses that aggregation.)

Conditional Formatting

Conditional formatting allows you to apply formatting rules to values, labels, calculations and/or custom columns based on user-defined conditions.

You may use conditional formatting to highlight information in an inquiry, such as high or low amounts, specific business units, certain accounts, or category codes. Through conditional formatting, you can also suppress certain information or reverse the sign on a range of object accounts (e.g. for revenue accounts).

Creating a conditional formatting style
  1. Access Conditional Formatting by clicking New in the Conditional tab. The Conditional Formatting dialog is then displayed

  1. Create the format:
    1. Name - enter a meaningful name to best reflect the contents and scope of the format.
    2. Item - using the drop-down menu, select the item that the condition will be based off of.
    3. Selection (e.g. ledger, calculation, label column) - according to your previously selected item, this updates accordingly. Using the drop-down menu or Visual Assist, select the column and specific item to which you wish to apply the formatting rules. For example, if you wish to apply a format to the calculated column Budget Variance %, you can either manually type in the calculation name or find it in the Visual Assist for Ledger inside the Calculations tab.
  1. Item/Text Only Radio Buttons - available when the selected item is a label, these options enable you to base your condition either on the item code or on the description (text).
    As required, select either Item or Text, then choose equal or not equal from the drop-down list, and enter the code or description text in the field next to it.
  2. New/Remove Buttons - New allows you to create an additional rule for this conditional formatting definition; Remove will delete the highlighted item from the condition listing.
  3. And/Or - if you are building conditional formatting with more than one condition, the and/or option can be used to specify how the conditions relate. For example, you might want a condition for business unit A OR business unit X.
  4. Apply To - defines where you want the formatting to appear in your inquiry: only value columns, only labels columns or to the entire row.
  1. Set the format style:
    1. Click Set Format to open the Define Formatting Style dialog.
    2. Set the formatting style.
    3. Click OK.
Example: Conditional formatting

In this example, we will format expense accounts for Business Unit 3 that are greater than $400,000.

  1. Go to the Conditional Formatting tab within the Formatting dialog.
  2. Provide a meaningful name for the format.
  3. Create 2 conditions:
    1. Business Unit = 3
    2. AA Ledger > 400,000
      For this example we want to be using the And option as we want both of these conditions to be fulfilled before formatting occurs.
  4. Specify the Apply To option. (For this example, retain the default setting of Value Cells.)
  5. Click Set Format. (Set the Back Color to red and the Font to Bold.)
  1. Click OK once these formatting changes are made and then click OK to save your changes and to exit the Conditional Formatting dialog. Then click OK to exit the Formatting dialog.

The result is shown below, with expense accounts over 20,000 displaying in bold text with a red background color:

Stoplight Formatting

Using stoplight formatting, you can quickly and easily highlight the numbers that fall in or out of a designated range. For example, if stoplight formatting is set for the calculated column Budget Variance % and the threshold is ranged from -10 to +10, then all amounts that exceed the upper threshold will be displayed in green, all amounts that fall within the range will be displayed in yellow and the amounts below the lower threshold will be displayed in red.

To set up a stoplight format:

  1. Access Stoplight Formatting by clicking New Stop Light in the Conditional tab. The New Stoplight dialog is then displayed.

  1. Create the stoplight format:
    1. Name - enter a meaningful name to identify the conditional format.
    2. Item - use the drop-down menu to choose one of the across-pivoted fields to apply the formatting rules. For example, by choosing 'Ledger', the conditions will be set and applied to all value and calculation columns that contain the selected ledger values.
    3. Selection (e.g. ledger) - according to your previously selected item, this updates accordingly. Using the Visual Assist, select the specific item to which you wish to apply the formatting rules. For example, if you wish to apply the stoplight format to the calculated column Budget Variance %, you can either manually type in the calculation name or find it in the Visual Assist for Ledger assist inside the Calculations tab.
    4. Thresholds - type the lower threshold value on the top box and the upper threshold value on the bottom box to identify the desired range.
    5. Stoplight Color Boxes - click on the colored boxes to open the Define Formatting Style dialog, where you can choose the color of the cells that fall above, within, and below the thresholds defined to the right.
    6. Expense Reporting - check this option to recognize a change in accounting behavior with the accounts used in the conditional formatting.
  2. Click OK.
Expense Reporting Option

The Expense Reporting checkbox allows you to activate functionality that recognizes differences in account behavior. Within most accounting systems, double entry bookkeeping means accounts like Revenues will be recorded as a negative figure; likewise expenses are recorded as positive figures.

With the Stoplight functionality, values below a threshold are displayed in a certain color and values above a threshold are displayed in a certain color. With Revenues, Expenses and other accounts, the Stoplight functionality needs to be reversed. By enabling Expense Reporting, the underlying system switches the formatting to take this into account.

More specifically, in JD Edwards the identification of accounts that need to have their formatting changed is done through the use of specific AAIs (Automatic Account Instructions). An AAI is used to provide accounting structures for specific object accounts and to allow processes like annual and monthly closes. A standard AAI series is critical for the Expense Reporting functionality to work correctly.

The following list is only a subset of all AAIs provided by JD Edwards:

GLG3 Beginning Assets
GLG4 Retained Earnings
GLG6 Beginning Revenues
GLG7 Ending Revenues
GLG11 Beginning Other Income
GLG13  Beginning Other Expenses

Using the above AAIs, the Expense Reporting feature will reverse the formatting for any object accounts that fit into the following:

  • Equal to or greater than GLG3, but less than GLG4 -1
  • Equal to or greater than GLG6 up to GLG7 (Including GLG7)
  • Equal to or greater than GLG11 but less than GLG13 -1

For example, if GLG3 was linked to Object Account 4000 and GLG4 was linked to Object Account 4980, the range of object accounts to be formatted differently would be 4000:4979.

Example: Stoplight Formatting

For example, use the Stoplight feature to highlight half-year revenue and expense figures within a Business Unit. Highlight the following ranges:

  • Red - less than zero
  • Yellow - greater or equal to zero, equal to or less than 200,000
  • Green - greater than 200,000

The results of the stoplight formatting with Expense Reporting are shown below:

The AAIs for this underlying data specified that Revenues are from object account 5000 to 5979 and Expenses are from 6000 to 7999.

Object accounts 5002, 5003 and 5004 are correctly formatted in green as they are Revenues. Object Account 5110 is correctly coded as its value is negative, but is less than the Threshold level of 200,000

Likewise with the expenses, 6001 is correctly shaded in green as it is greater than the Threshold level of 200,000. 7050 is correctly shaded in red as it is less than zero.

To change the colors of the stoplight format:

  1. When creating/editing the stoplight format, click on the color you wish to change. For example, click on the default color of red for the lower threshold:

  2. The Define Formatting Style dialog opens, where you can go to the Appearance tab and change the background color as desired:

  3. Click OK to exit the dialog.
  1. Back in the Create/Edit Stoplight dialog, you see the new color of that threshold. You can also change the other colors if desired.

Formatting Priority

The order in which formatting is applied is controlled through the use of priorities.

Formatting priority is important as it determines what happens where formats intersect on an inquiry. This most commonly happens where formatting on rows crosses formatting on columns, but it is important where conditional formatting takes place since these styles can conflict with other formatting styles such as hierarchy level and column formatting.

The Priority tab within the Formatting dialog lists all of the currently applied formats, and the format at the top of the list is the one with the greatest precedence. The next in the list has the second precedence, etc.

To change the order:

  1. Click on the appropriate format style.
  2. Use the top/bottom/up/down arrow keys to re-order the selected style.
  3. When the correct priority has been established, click OK to return to the inquiry.

Formatting and RIO

A user with a Configurator license can add a format to Reusable Inquiry Objects (RIO), making it available to other users in order to use it in their inquiries.

To add a format to RIO:

  1. Open the inquiry where the item exists.
  1. Access RIO by clicking on Reusable Inquiry Objects from the Inquiry Objects menu. The Reusable Inquiry Objects dialog is displayed.

  1. Highlight the formatting style on the left panel to be moved into RIO.
  2. Highlight the folder where you want it moved to on the right panel.
  3. Click the arrow in the middle of the screen to move the item over to the right.

If a user with a Configurator License has added a format to RIO, users can subscribe to it in their own inquiries. To subscribe to a formatting style that has been added to RIO:

  1. Open the inquiry in which you want to subscribe to the formatting style.
  2. Access RIO by clicking on Reusable Inquiry Objects from the Inquiry Objects menu.
  3. Subscribe to the formatting style by checking the box to the left of it.
  4. Click either Apply to apply the format or OK to apply the item and exit the dialog at the same time.

Data Display Settings

Hide Detail – this option, on the Design menu in the Options group, sets whether headers and totals display without the detail lines.

For example, an inquiry showing details looks like this:

The same report hiding details looks like this:

Displaying headers, totals, and/or zero balances - On the Design menu in the Options group, there are several checkboxes used to activate and de-activate certain features within the inquiry result set. They enable/disable the following functions:

  • Totals
  • Headers
  • Hide Detail
  • Zero Balances
  • Sort by Values
  • Missing Balances

These toggles are especially helpful because sometimes you may want to display them and sometimes you may not. Either way, the items (such as headers and totals) still exist on the inquiry.

Note: In order to see the options for Zero Balances and Missing Balances, your Hubble Administrator must first set these capabilities for you to be able to show accounts with a zero and/or missing balance. (In Administrator, the Advanced Capability for Zero Balances is under Feature Control > Show Zero Balances and the Advanced Capability for Missing Balances is under Feature Control > Show Missing Balances.

Show Zeros and Show Missing Balances Features

Show Zeros – there has been activity in the account and the ending balance is $0.

Missing Balances – there has not been any activity in the account. [Missing Balances is a feature which is only available in inquiries based on the JD Edwards Account Balances Table (F0902) or the Asset Account Balances (F1202). When this feature is turned on, Hubble reads the Account Master Table (F0901) for General Ledger or Asset Master (F1201) for Fixed Assets, and returns all available rows according to the filter criteria in the inquiry. The optimal use of the Missing Balances feature is by using filters or a small hierarchy to narrow down the data being returned.]

Note: When you turn on the option to view Missing Balances, you are joining to another table (F0901 in GL or F1201 in FA), which can increase the time needed to return inquiry results. Therefore this should only be turned on when needed. The Missing Balances Feature can be particularly useful when using Budgeting, when budget amounts need to be entered for accounts regardless of whether there are already account balances.

Status Bar

Once you have run an inquiry, you can see detailed information about the results that were returned via the Status Bar. This is a customizable bar that displays at the bottom of the screen, showing specific details about the current inquiry:

Pivoting

Value columns have a specific structure by default, however you can change the structure using the pivot feature; you can change rows (down) to columns (across) and/or columns to rows.

For example, in a GL Balances inquiry, the default structure for amount columns is Period/Ledger Types/ Fiscal Year:

You can add Business Unit to the structure so that each business unit displays in its own column:

To change the way data is pivoted:

  1. Unless you are using the Power Pivot feature (explained below), you must define specific values in the filter as you cannot use a wildcard or a range. If no filter selections have been made, you are prompted to either specify the selections or have the system choose one for you:
    1. If you click Yes, the Visual Assist screen will be displayed for that filter and you can define the filter values.
    2. If you click No, the selection in the filter changes to be the first item available in the filter, e.g. Business Unit 1 if this is the Business Unit Field.
  2. On the Design menu, in the Data group, click on Pivot Inquiry. In this dialog, the row selections are on the left and the column selections are on the right. To move a selection from a row to a column or vice versa (known as ‘pivoting’ the data), first highlight the selection to be moved and use the arrows or double-click to move it from one panel to the other.
  3. Click OK.

Notes on pivoting data:

  • Any item can be added or removed from the pivot across except Period.
  • There must always be at least one item in the row and one in the column.
  • To change the order of the pivoted fields, in the Pivot Inquiry dialog, highlight the dimension to move and use the up/down/top/bottom buttons to re-sequence the fields.

For example, in GL Balances the dimensions display as Period first – then Ledger – then Year:

For this result:

If you want to display Ledger Types first, you can move it to the top using the up arrow:

For this result:

Designating whether filter selections from a pivoted field display as code, description, or a combination of both in the column:

  1. From the Design menu in the Setup group, click Descriptions.
  2. Using the tree structure on the left, find and highlight the field/item.
  3. Under the Settings Section, choose whether code, description, or a combination of both is used.

In this example, we have added the Business Unit field to Pivot Inquiry, and are showing the Business Unit code and description:

Power Pivot

Rather than having to specify a single selection or list over which to pivot data, this feature provides users the ability to use a wildcard or range and have Hubble create only those columns that will actually contain data.

Also, an optional total column can be created.

For example, this is a GL Balances inquiry, filtered to company 1, and the user wants to pivot Business Unit.

The Power Pivot option will automatically create the columns required to display only those Business Units which have data for Company 1 based on the other, so pivot Business Unit and mark the Power Pivot checkbox.

The following columns are created in the report:

Note: Power Pivot runs the query behind the scenes before the user hits “run”. This determines which columns will be displayed based on the filters selected in the report.

Each change made to a filter results in the query being run behind the scenes to determine the columns shown.

Some care should be used when enabling Power Pivot:

  • Having reports where most filters are set and not changed (except for maybe Period and Year) works well with Power Pivot. Every filter change results in a query run, so minimizing the filter changes also minimizes the processing time when using Power Pivot.

  • If there is the potential for thousands of columns display because Power Pivot is being used, best practice is to limit the number of columns that will be returned by having other filters set on the report.

  • Be sure to uncheck the Power Pivot checkbox if not using Power Pivot.

To add a total column, the option is in the filter:

By selecting this, a total column is created on the data at the right hand side of the columns.