Skip to Content

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 

image

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.

image

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

image

7. Save  and close workbook.

8. Open workbook again and make enable macro

image
image

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

image

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

image

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

image

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Anupam Gupta
    I’d like to know how would the BI authorization apply as you  click on a BI report link as mentioned in the above example.

    Regards
    Anupam

    (0) 
    1. James Lim Post author
      BI authorization still applys here.

      It means when user clicks link, it will show BI login screen.

      Thank you.
      Regards,
      James

      (0) 

Leave a Reply