Here's what we did for a multi hop drill back that has two hops:
- 1st drill back is from MgtRpt cube back to FinRpt cube
- 2nd drill back is from FinRpt cube to Stage
Private Function GetDrillBackTypeList(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer,
ByVal args As ConnectorArgs) As List(Of DrillBackTypeInfo)
Try
'Create the SQL Statement
Dim drillTypes As New List(Of DrillBackTypeInfo)
If args.DrillCode.XFEqualsIgnoreCase(StageConstants.TransformationGeneral.DrillCodeDefaultValue) Then
'The 1st drill down is back to FinRpt Cube
drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("FinRpt_Cube","FinRpt Cube")))
Else If args.DrillCode.XFEqualsIgnoreCase("FinRpt_Stage") Then
'The 2nd drill back is to FinRpt stage
drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("FinRpt_Stage","FinRpt Stage")))
End If
Return drillTypes
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
In the Get_Drill_Back funciton, we define the 1st and 2nd drill backs. The system will determine which drill back to use by evaluating the drillBackName string (args.DrillBackType.NameAndDescription.Name)
Private Function Get_Drill_Back(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConnectorArgs) As DrillBackResultInfo
Try
Dim drillBackName As String = args.DrillBackType.NameAndDescription.Name
Dim sourceValues As Dictionary(Of String, Object) = api.Parser.GetFieldValuesForSourceDataRow(si, args.RowID)
Dim drillBackInfo As New DrillBackResultInfo
Dim TB_DataTable As New DataTable
Dim ddsArgs As New DashboardDataSetArgs
If drillBackName.XFEqualsIgnoreCase("FinRpt_Cube") Then
#Region "1st Level Drill Back"
ElseIf drillBackName.XFEqualsIgnoreCase("FinRpt_Stage") Then
#Region "2nd Level Drill Back"
Else
Return Nothing
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
The trick is to add a hidden column to the 1st drill back result that will have contain the name of the 2nd drill back. This column (DrillTpeCode) is not visible to the user but is visible to args.DrillBackType.NameAndDescription.Name. The first drill back looks like this:
If drillBackName.XFEqualsIgnoreCase("FinRpt_Cube") Then '<<-- Evaluate the name of the derill back to determine which drill back to use
#Region "1st Level Drill Back"
If (Not sourceValues Is Nothing) And (sourceValues.Count > 0) Then
'Get the data from FinRpt cube using a data set function into a temp table
TB_DataTable = Get_Cube_Data(si, api, ddsArgs, cubeName, entityDimName, entityName, scenarioName, periodName, filter, exportToFile)
'Add a column to the 1st drill back that will have the naem of the 2nd drill back
'Note: the new column will not be visible in the drill back result, but will be visible
' in args.DrillBackType.NameAndDescription.Name
Dim colDrillTypeCode As New DataColumn("DrillTypeCode", GetType(String))
colDrillTypeCode.DefaultValue="FinRpt_Stage"
TB_DataTable.Columns.Add(colDrillTypeCode)
'Pass the temp table into the drillBack
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid
drillBackInfo.DataTable = New XFDataTable(si, TB_DataTable, Nothing, SharedConstants.Unknown)
Return drillBackInfo
Else
Return Nothing
End If
#End Region
The 2nd drill back uses a query that gets data from the Stage table based on the values that are shown in the first drill back. This qury itself is written in a data adapter called SQL_Stage_Drill_Back and it uses dynamic parameters in the WHARE section. The first part of the function substitutes these dynamic parameters with the SourceValues (api.Parser.GetFieldValuesForSourceDataRow(si, args.RowID)). The 2nd drill down looks like this:
ElseIf drillBackName.XFEqualsIgnoreCase("FinRpt_Stage") Then '<<<--- The name of the 2nd drill back is saved in the hidden column added to the first drill back table (DrillTypeCode)
#Region "2nd Level Drill Back"
If (Not sourceValues Is Nothing) And (sourceValues.Count > 0) Then
'Create a dictionary of varables in the Adapter SQL that need to be changed
Dim customSubstVars As New Dictionary(Of String, String)
customSubstVars.Add("SnT", SourceValues.Item("SnT").ToString)
customSubstVars.Add("EtT", SourceValues.Item("Et").ToString)
customSubstVars.Add("TmT", SourceValues.Item("TmT").ToString)
customSubstVars.Add("VwT", SourceValues.Item("Vw").ToString)
customSubstVars.Add("AcT", SourceValues.Item("Ac").ToString)
customSubstVars.Add("FwT", SourceValues.Item("Fw").ToString)
customSubstVars.Add("OgT", SourceValues.Item("A9").ToString)
customSubstVars.Add("IcT", SourceValues.Item("Ic").ToString)
customSubstVars.Add("U1T", SourceValues.Item("U1").ToString)
customSubstVars.Add("U2T", SourceValues.Item("U2").ToString)
customSubstVars.Add("U4T", SourceValues.Item("U4").ToString)
customSubstVars.Add("U5T", SourceValues.Item("A5").ToString)
customSubstVars.Add("U6T", SourceValues.Item("A6").ToString)
customSubstVars.Add("U7T", SourceValues.Item("A7").ToString)
Dim AdapterName As String = "SQL_Stage_Drill_Back"
Dim AdapterSQL As XFDataSetInfo = BRApi.Dashboards.Process.GetXFDataSetInfoForAdapter(si, False, Nothing, "Default." & AdapterName, "SQL_Tbl", customSubstVars, 10000, True)
'Get the data from stage into a data table
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
TB_DataTable = BRApi.Database.ExecuteSql(dbConnApp, AdapterSQL.SubstitutedQuery, True)
'Parse the temp table into the drillBack
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid
drillBackInfo.DataTable = New XFDataTable(si, TB_DataTable, Nothing, SharedConstants.Unknown)
End Using
Return drillBackInfo
Else
End If
#End Region
