Create: Building jDropdowns

Overview

Interject reports typically have parameters that can be passed to a stored procedure that can filter the data. It can be difficult to remember what the exact filter names are and which are even available for the stored procedure. This is especially true in large reports with many filtering options. However, you can use Interject's jDropdown formula to automatically create hyperlinked lists of available filters. This makes filtering in large reports a much simpler task!

In this example you will modify the Customer Aging report by adding the jDropdown functionality to one of the filters, while using an existing Data Portal.

Note: The jDropdown feature requires a Data Portal with a stored procedure to operate. In the event that you cannot create a stored procedure, you can reuse a Data Portal from the report formula that populates the report in which you want to use this feature. To learn how to build the Database Connection, Data Portal, and stored procedure used in this example, follow the instructions in the developer example. Otherwise contact your IT department to help implement this functionality.

If you are following the Training Labs, this report file can be found in the Report Library at Training Labs > Lab 5 Advanced Features > Lab 5.3 Building jDropdowns.

Preparing the Report

Step 1: Open the report Interject Customer Collections under the Interject Demos in the Report Library.

Step 2: Next, unfreeze panes by going into Quick Tools and selecting Freeze/Unfreeze Panes.

Adding a Named Range

Step 1: Now select the Formulas ribbon tab and select Name Manager.

Step 2: Next, select New….

Step 3: For the Name: field input CompanyNameDDL, and in the Refers to: field input =CustomerAging!$H$7. Click OK and Close the Name Manager window.

Note: A named range is used here so that upon the movement of the formula, the hyperlink that is created later will still reference the cell with the jDropdown() formula.

Creating the Formula

Step 1: Next, in cell H7 insert =jDropdown() then select fx.

Step 2: For the Data Portal argument field, input NorthwindCustomersDropdown.

Step 3: For the MultiSelect argument field, input False. Then for the Target Cell Argument field, input C17.

Step 4: In the Value Column Name argument field, insert CompanyName.

Step 5: Scroll down in the function arguments until you see the Display Column Name argument field and insert DisplayText.

Step 6: Next, scroll down in the function arguments to find the Instruction Text argument field and input Select A Customer. Click OK.

Important: The Value Column Name and the Display Column Name arguments must exactly match the name of a column that is returned by the stored procedure. A good place to look for valid columns is in the Column Definitions section of the report. To learn more about the jDropdown() function and what each of its arguments do, review its index page.

Adding a Special Hyperlink

Step 1: Now select cell B17 and right click. Then select the Link option in the menu.

Step 2: Click on Place in This Document then select the CompanyNameDDL defined name in the list of options. Then select the ScreenTip… button.

Step 3: In the ScreenTip text: field input Interject Dropdown (Note: The jDropdown function will not work without this).

Reviewing the Report Changes

Step 1: Now select the hyperlink you just made and type Market into the search options. Notice that there are 4 options. Select BOTTM - Bottom-Dollar Markets.

Step 2: Pull the report.

Step 3: The pull will only return the Bottom-Dollar Markets data.

To build the stored procedure and Data Portal, and database connection that allows this formula to work, continue to the developer section of this example.

Creating a Custom jDropdown List

The jDropdown feature can be used to create a custom list of items in a dropdown list. Instead of a Data Portal, you can enter a list. For example, to create a dropdown list of 3 items: Yes, No, Maybe, use the following syntax:

list:Yes,No,Maybe

To display the list options in the dropdown window something different then the entry, use the following format:

list:Yes^This is Yes,No^This is No,Maybe^This is Maybe

Step 1: Find an empty cell and enter a heading:

Step 2: On the cell above the heading, enter =jDropdown( and press the "fx" button to bring up the Function Wizard:

Step 3: Enter the following for the jDropdown parameters and click OK:

• Data Portal: "list:Yes^This is Yes,No^This is No,Maybe^This is Maybe"
• Target Cell: <Enter cell ref here>
• Value Column Name: <Enter anything here>

Step 4: Right click the cell you wish to create a link and click Link:

Enter the cell reference of the jDropdown function and click ScreenTip:

Enter "Interject Dropdown" for the ScreenTip and click OK and then OK again to create the link:

Step 5: Click on the link to see the dropdown list:

Click on an entry to see it populated: