Setting Up the jDataPortal

Overview

The jDataPortal is a powerful Interject function that not only allows users to not only establish and customize the connection to an Interject Data Portal, but also override certain parameters of the Portal to customize reporting needs.

Setup jDataPortal

In this section, you will open a report and change the Data Portal to a jDataPortal.

Begin by opening up the Customer Collections report.

In order to access the Interject configuration area, unfreeze the panes via the Quick Tools menu.

Select the report function.

In the formula bar, change the DataPortal parameter from "Northwind Customers" to:

jDataPortal("Northwind Customers")

Enter "Market" for the Company Name filter and Pull the data.

Result Sets

The DataResultNumber is the second parameter for the jDataPortal. This parameter specifies the particular data set you want returned. For instance, if the stored procedure for the data portal returns 4 queries, entering 2 for this parameter will return the 2nd query to your report.

You can also enter negative numbers to specify the query in relation to the last query. For example, -1 will return the last query listed in your stored procedure. To illustrate, the following chart shows an example for a stored procedure returning 4 data sets:

Order By

The OrderBy parameter accepts comma separated values of column names that will be used to sort the data result set. In this section you will add a value for this parameter to order the results.

First select the jDataPortal function by putting the cursor inside the name and click the fx button to bring up Excel's Function Wizard.

In the OrderBy field, enter:

"[CustomerName] DESC"

Now Pull the data again and notice the results are in descending order by Company Name.

The OrderBy parameter accepts multiple column names (e.g. "CompanyName, ContactTitle"). Some key points:

• This parameter is not case sensitive
• Brackets may or may not surround column names
• Result sets are sorted in the order of the columns listed
• Sorts in ascending order (ASC) unless descending order (DESC) is specified

For more information, see Microsoft's DataView.Sort.

Filter

The Filter parameter accepts valid expressions that will be applied to the results.

Bring up the Function Wizard again and for the Filter parameter, enter:

"[ContactTitle] LIKE '%Manager%'"

Pull the data again and notice the results are filtered to only included entries whose Contact Title includes "Manager".

The Filter parameter excepts multiple expressions. Some key points:

• This parameter is not case sensitive
• Brackets may or may not surround column names
• Multiple expressions can be added using the keywords 'AND' or 'OR'
• Terms can be compared using '<', '>', '<=', '>=', '<>' or '='

For more information, see Microsoft's DataView.RowFilter. For more information about syntax and keywords, see Expressions.

Overriding a Connection

With the ConnectionOverride parameter, a jDataPortal function can use a different Interject Data Connection when accessing the Data Portal. Making the same Data Portal for different connections is redundant. This parameter makes it possible to host one Data Portal with many different possible connections and thus accommodate your unique reporting needs.

To use, simply enter the name of the connection from the Portal site that you want to use:

Overriding a Command

Similar to the ConnectionOverride, the CommandOverride can override the command (stored procedure) stored in the Data Portal. Again, you only need one Data Portal to access different stored procedures and thus cut down on the required maintenance of having to manage many different portals.

Command overrides can only be performed by a ClientAdmin role.