How to do Drill-Through BW data from BPC MS 7 and BPC NW 7   |
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
James Lim SAP RIG America - BPC MS platform architect & Customer Care
