How to do Drill-Through BW data from BPC MS 7 and BPC NW 7

[james_lim]

If you know BPC and had a chance to meet some customers, you might hear about a request or question that "Can we do Drill-Through BW data from BPC?"

Unfortunately, current BPC 7 NW and MS version doesn't support. In addition, even though user uses 7.5 version, BPC 7.5 MS version will not support it. (BPC 7.5 NW support it and BPC 7.5 MS supports URL based drill through in SP3.)

When I visit a customer site during last week, I heard a BIG complaint about this so I tried to develop a simple excel macro to mimic similar function as BPC 7.5 NW Drill-Through.

Here is the features of my prototype.

1. This is excel workbook macro so user should add this macro into each workbook that needs BW drill-through.

2. User can maintain URL and its parameter like EVDRE

Note : It is open source so anyone can modify and use it

 

 

Let's try it step by step.

1. Create a EVDRE Report. ROW : Account, Column : Time



2. After you create it, Press Alt + F11 key then it will open EXCEL macro as below screen shot.

3. Find workbook name that has EVDRE report and Double-click 'ThisWorkbook' then it will open macro edit window.



4. Paste macro code and save it

5. close macro edit window  (Alt + F4)

6. Make Drill Through Control panel as below screen shot. Under EVDRE control panel will be good location. Screen shot will open Yahoo website based on the selected account member.  parameter name is p and we need to get account dimension member in the same row so specify column name



7. Save  and close workbook.

8. Open workbook again and make enable macro




9. Select any value cell and right click then popup menu will be shown. Select NW_DrillThrough menu under original Drill Through menu.



10. It will open Yahoo web page with selected Account name query result.



You can extend it with changing control panel as below to execute NW query.



In conclusion, this can be a workaround to achieve drill-through NW data from BPC 7 MS and NW.

If you have any comment, please let me know.

 

=========== Macro code ========================

Const APP_SHORTNAME = "NW_DRILLTHROUGH"
Private iRow As Integer
Private iCol As Integer

Private Sub Workbook_Open()

Call setmenu

End Sub

Private Sub Workbook_Activate()

Call setmenu

End Sub

Private Sub Workbook_Deactivate()

'when workbook is deactivated, remove menu
On Error Resume Next
Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

End Sub

Public Sub setmenu()

'Developed by James Lim SAP RIG America  2010. Jan. 8'
'This macro should put it in the thisworkbook
'When workbook is opened or activated, macro will be called and menu will set.

Dim ctlNewMenu As CommandBarControl
Dim ctlNewGroup As CommandBarControl
Dim ctlNewItem As CommandBarControl
Dim iBPCDrillThrough As Integer

On Error GoTo Err_Trap

On Error Resume Next

Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

On Error GoTo 0

'Get position of default Drill Through menu
iBPCDrillThrough = Application.CommandBars("Cell").Controls("Drill Through...").Index

'Put New Drill Through menu under it
Set ctlNewItem = Application.CommandBars("Cell").Controls.Add(, , , Before:=iBPCDrillThrough + 1)

'Set menu caption
ctlNewItem.Caption = APP_SHORTNAME

'Assign macro to open browser
ctlNewItem.OnAction = "ThisWorkbook.ProcessData"

Err_Trap:

If Err <> 0 Then

Err.Clear

Resume Next

End If

End Sub

 

Public Sub ProcessData()
'get URL information from Control panel

Dim lcol As Long
Dim lrow As Long
Dim sParamName As String
Dim sParamValue As String
Dim sURL As String
Dim sURLHeader As String
Dim sURLTail As String

lrow = pFindPosRow("URLHeader")
If lrow = 0 Then Exit Sub
lcol = pFindPosCol("URLHeader")

sURLHeader = Range(numToAddress(lcol + 1) & CStr(lrow)).Value
sURLTail = Range(numToAddress(lcol + 1) & CStr(lrow + 1)).Value

sURL = sURLHeader

i = lrow + 2

Do While Trim(Range(numToAddress(lcol) & CStr(i)).Value) <> ""

sParamName = Range(numToAddress(lcol) & CStr(i)).Value
sParamValue = Range(numToAddress(lcol + 1) & CStr(i)).Value

If IsNumeric(sParamValue) Then
sParamValue = Range(numToAddress(Application.ActiveCell.Column) & sParamValue).Value
Else
sParamValue = Range(sParamValue & Application.ActiveCell.Row).Value
End If

sURL = sURL & sParamName & "=" & sParamValue & "&"
i = i + 1
Loop

sURL = Mid(sURL, 1, Len(sURL) - 1)
sURL = sURL & sURLTail

'open browser
ActiveWorkbook.FollowHyperlink Address:=sURL

 

 

End Sub

Private Function pFindPosRow(sText As Variant, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional SearchOrder As XlSearchOrder = xlByRows) As Long

Dim sResult As String, oRg As Range
Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=SearchOrder, _
SearchDirection:=SearchDirection, _
MatchCase:=False, SearchFormat:=False)

If Not oRg Is Nothing Then
sResult = oRg.Row
Else
MsgBox "Can't find " & sText, vbCritical + vbOKOnly, "Error"
GoTo Exit_sub
End If

pFindPosRow = sResult

Exit_sub:
Set oRg = Nothing
End Function

Private Function pFindPosCol(sText As Variant, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional SearchOrder As XlSearchOrder = xlByColumns) As Long

Dim sResult As Long, oRg As Range
Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=SearchOrder, _
SearchDirection:=SearchDirection, _
MatchCase:=False, SearchFormat:=False)

If Not oRg Is Nothing Then sResult = oRg.Column

pFindPosCol = sResult
Set oRg = Nothing

End Function

Function numToAddress(lAddress As Long) As String
Dim iCol As Long
Dim sColAddress As String

iCol = lAddress

While (iCol > 0)
iCol = iCol - 1
sColAddress = Chr(Asc("A") + (iCol Mod 26)) + sColAddress
iCol = iCol \ 26
Wend

numToAddress = sColAddress

End Function