Perform a Drill Back

The following snippet describes how to load data from a local gateway connection data source and how to perform a drill back. The example below has been updated from the Standard SQL Connectors business rule. If you do not have the Snippet Editor with the OneStream Application, you can find the Snippet Editor on the Solution Exchange.

  1. Download and install the Snippet Editor from Solution Exchange.

  2. Navigate to Application > Tools > Business Rules.

  3. Expand Connector and select a Business Rule.

  4. Navigate to Snippets > SQL Connector > Standard SQL Connectors.

  5. Copy the Sample Business Rule.

  6. Enter the connection name. In this example, “Northeast Sales” is the Gateway Connection Name as defined in the application configuration.

    Copy 
    ' Create a Connection string to the External Database (prior to using the gateway)
    Private Function GetConnectionString(ByVal si As Sessioninfo, ByVal globals As BRGlobals, ByVal api As Transformer) As String 
     Try
     ' Named External Connection
     ' -------------------------------------------
     Return "Revenue Mgmt System"
     Catch ex As Exception
     Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
     End Try 
    End Function
    ' Create a Connection string to the External Database (using the Gateway)
    Private Function GetConnectionString_Gateway(ByVal si As Sessioninfo, ByVal globais As BRGlobals, ByVal api As Transformer) As String
     Try
     ' Named External Connection - Gateway
     ' ---------------------------------------------
     Return "Northeast Sales"
     Catch ex As Exception
     Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) 
     End Try 
    End Function

  7. Enter the drill back information to your database.

    Copy 
    If args.DrillCode.Equals(StageConstants.TransformationGeneral.DrillCodeDefaultValue, StringComparison.InvariantCulturelgnoreCase) Then
     ' Source GL Drill Down
     drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.FileShareFile, New NameAndDesc("InvoiceDocument","Invoice Document"))) 
     drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("MaterialTypeDetail","Material Type Detail")))
     drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("MaterialTypeDetail_Gateway","Material Type Detail (Smart Integration)")))

  8. Edit the level of drill back information returned.

    Example: This example shows previously existing code that leverages a VPN based SQL connection and the Gateway based method shown in the second "Else If" block.

Copy 
Else If args.DrillBackType.NameAndDescription.Name.Equals("MaterialTypeDetail", StringComparison.InvariantCultureIgnoreCase) Then
 ' Level 1: Return Drill Back Detail
 Dim dri1lBackSQL As String - GetDrillBackSQL_Ll(si, globais, api, args)
 Dim drillBackInfo As New DrillBackResultInfo
 drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.OataGrid
 drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si, DbProviderType.SqlServer, connectionstring. True, drillBackSQL, False, args.PageSize, args.PageNumber) 
 Return drillBacklnfo
Else If args.DrillBackType.NameAndDescription.Name.Equals("MaterialTypeDetail_Gateway", StringComparison.lnvariantCultureIgnoreCase) Then 
 ' Level 1: Return Drill Back Detail
 Dim drillBackSQL As String = GetDrillBackSQL_Ll(si, globais, api, args)
 Dim drillBackInfo As New DrillBackResultInfo
 drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.OataGrid
 drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si, DbProviderType.Gateway, connectionstring_gateway. True, drillBackSQL, False, args.PageSize, args.PageNumber) 
 Return drillBacklnfo