Examples

This section provides some basic examples on using custom drill through. See Knowledge Base article 1145391 on the Infor Support Portal for additional examples.

This example is included in the CTrain sample application. The example is for a merged period year dimension.

SELECT ORDERID AS 'Order id',
CONVERT(VARCHAR(8), DATESOLD, 1) AS 'Order Date',
UNIT AS Unit,
CUSTOMERID AS Customer,
PRODUCT AS Product,
QUANTITY AS Quantity,
UNITPRICE AS 'Unit Price',
SALESAMOUNT AS 'Sales Amount'
FROM CTRAIN.SQLDRILL_ORDERS 
WHERE Unit in(select mem_name from CTRAIN.unit where mem_id in(select mem_id from [dbo].unit_Hierarchy() where top_mem in(select mem_id from CTRAIN.unit where mem_name like '{Unit}')))
AND  Product in (select mem_name from CTRAIN.cust1 where mem_id in(select mem_id from [dbo].cust1_Hierarchy() where top_mem in(select mem_id from CTRAIN.cust1 where mem_name like '{Product}')))
AND Year = right('{Period Year}',4)
AND Period in(select mem_name from CTRAIN.period where mem_id in(select mem_id from period_ytd('{Accum Method}',replace('{Period Year}',right('{Period Year}',5),'')))) 
ORDER BY 'Order ID'

This example is the same as the previous example but references an unmerged period year dimension.

SELECT ORDERID AS 'Order id',
CONVERT(VARCHAR(8), DATESOLD, 1) AS 'Order Date',
UNIT AS Unit,
CUSTOMERID AS Customer,
PRODUCT AS Product,
QUANTITY AS Quantity,
UNITPRICE AS 'Unit Price',
SALESAMOUNT AS 'Sales Amount'
FROM CTRAIN.SQLDRILL_ORDERS 
WHERE Unit in(select mem_name from CTRAIN.unit where mem_id in(select mem_id from [dbo].unit_Hierarchy() where top_mem in(select mem_id from CTRAIN.unit where mem_name like '{Unit}')))
AND  Product in (select mem_name from CTRAIN.cust1 where mem_id in(select mem_id from [dbo].cust1_Hierarchy() where top_mem in(select mem_id from CTRAIN.cust1 where mem_name like '{Product}')))
AND Year = '{Year}'
AND Period in(select mem_name from CTRAIN.period where mem_id in(select mem_id from period_ytd('{Accum Method}','{Period}')))
ORDER BY 'Order ID'

This example is a second level query included in the CTrain sample application. Use {Order ID} because it is one of the fields defined in the first query. This example works for both merged and unmerged periods because no period placeholders are used.

SELECT ORDERID AS 'Order ID',CONVERT(VARCHAR(8), DATESOLD, 1) AS 'Order Date',
UNIT AS Unit,
CUSTOMERID AS Customer,
PRODUCT AS Product,
QUANTITY AS Quantity,
UNITPRICE AS 'Unit Price',
SALESAMOUNT AS 'Sales Amount' 
FROM CTRAIN.SQLDRILL_ORDERS
WHERE ORDERID LIKE '{Order ID}'
ORDER BY PRODUCT