ODI Parameter

Target Report

Example

Adding ODI Parameter

1. Add ODI parameter

  1. Go to the Data window

  2. Right-click USER-DEFINED PARAMETER

  3. Select Add Parameter Field

  4. Add an ODI parameter "orderID" to the member pane.

2. Use parameter in Query

  1. Add WHERE clause with ODI parameter

-- order
SELECT orderheader.SalesOrderID, customer.Channel, customer.CustomerName, orderheader.DueDate, orderheader.ShipDate, orderheader.TotalDue, customer.EmailAddress, customer.Phone, address.PostalCode, address.Country, address.StateProvince, address.City, address.AddressLine1, orderheader.PurchaseOrderNumber 
FROM orderheader 
INNER JOIN customer ON customer.CustomerID = orderheader.CustomerID
INNER JOIN address ON address.AddressID = orderheader.AddressID
WHERE orderheader.SalesOrderID = #OZParam.orderID#
ORDER BY address.Country ASC, address.StateProvince ASC, address.City ASC, customer.CustomerName ASC, orderheader.SalesOrderID ASC

You can run the query by providing a value for the parameter orderID (for example, 63222).

Using Parameter in Report

1. Put the parameter value

  1. Datainfo > DataTree > order > OZParam > Select order ID

  2. Properties > Data > set Field Value to "63222".

  3. Preview

2. Set the ParameterToolbar

  1. Components > right click ParameterToolbar > Refresh Toolbar Wizard

  2. Move order.orderID to the Selected parameter from the Selectable Parameter.

  3. Set options as below.

Preview the report. You can try a new value for order ID. (ex: "69550")

Last updated

Was this helpful?