{"id":32,"date":"2011-04-12T21:14:00","date_gmt":"2011-04-12T21:14:00","guid":{"rendered":"http:\/\/www.sapbpc.sapag.co.in\/?p=32"},"modified":"2011-04-12T21:14:00","modified_gmt":"2011-04-12T21:14:00","slug":"how-to-do-drill-through-bw-data-from-bpc-ms-7-and-bpc-nw-7","status":"publish","type":"post","link":"https:\/\/www.sapbpc.sapag.co.in\/?p=32","title":{"rendered":"How to do Drill-Through BW data from BPC MS 7 and BPC NW 7"},"content":{"rendered":"

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?" <\/p>\n

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.<\/strike> (BPC 7.5 NW support it and BPC 7.5 MS supports URL based drill through in SP3.) <\/p>\n

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.<\/p>\n

Here is the features of my prototype.<\/p>\n

1. This is excel workbook macro so user should add this macro into each workbook that needs BW drill-through.<\/p>\n

2. User can maintain URL and its parameter like EVDRE<\/p>\n

Note : It is open source so anyone can modify and use it<\/p>\n

Let’s try it step by step. <\/p>\n

1. Create a EVDRE Report. ROW : Account, Column : Time <\/p>\n

\"image\"<\/p>\n

2. After you create it, Press Alt + F11 key then it will open EXCEL macro as below screen shot.<\/p>\n

3. Find workbook name that has EVDRE report and Double-click ‘This Workbook’ then it will open macro edit window.<\/p>\n

\"image\"<\/p>\n

4. Paste macro code and save it<\/p>\n

5. close macro edit window  (Alt + F4)<\/p>\n

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<\/p>\n

\"image\"<\/p>\n

7. Save  and close workbook. <\/p>\n

8. Open workbook again and make enable macro<\/p>\n

\"image\"
\"image\"<\/p>\n

9. Select any value cell and right click then popup menu will be shown. Select NW_Drill Through menu under original Drill Through menu.<\/p>\n

\"image\"<\/p>\n

10. It will open Yahoo web page with selected Account name query result.<\/p>\n

\"image\"<\/p>\n

You can extend it with changing control panel as below to execute NW query. <\/p>\n

\"image\"<\/p>\n

In conclusion, this can be a workaround to achieve drill-through NW data from BPC 7 MS and NW.<\/p>\n

If you have any comment, please let me know.<\/p>\n

=========== Macro code ========================<\/p>\n

Cost APP_SHORTNAME = "NW_DRILLTHROUGH"
Private iRow As Integer
Private iCol As Integer<\/p>\n

Private Sub Workbook_Open()<\/p>\n

Call setmenu<\/p>\n

End Sub<\/p>\n

Private Sub Workbook_Activate()<\/p>\n

Call setmenu<\/p>\n

End Sub<\/p>\n

Private Sub Workbook_Deactivate()<\/p>\n

‘when workbook is deactivated, remove menu
On Error Resume Next
Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete<\/p>\n

End Sub<\/p>\n

Public Sub setmenu()<\/p>\n

‘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.<\/p>\n

Dim ctlNewMenu As CommandBarControl
Dim ctlNewGroup As CommandBarControl
Dim ctlNewItem As CommandBarControl
Dim iBPCDrillThrough As Integer<\/p>\n

On Error GoTo Err_Trap<\/p>\n

On Error Resume Next<\/p>\n

Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete<\/p>\n

On Error GoTo 0<\/p>\n

‘Get position of default Drill Through menu
iBPCDrillThrough = Application.CommandBars("Cell").Controls("Drill Through…").Index<\/p>\n

‘Put New Drill Through menu under it
Set ctlNewItem = Application.CommandBars("Cell").Controls.Add(, , , Before:=iBPCDrillThrough + 1)<\/p>\n

‘Set menu caption
ctlNewItem.Caption = APP_SHORTNAME<\/p>\n

‘Assign macro to open browser
ctlNewItem.OnAction = "ThisWorkbook.ProcessData"<\/p>\n

Err_Trap:<\/p>\n

If Err <> 0 Then<\/p>\n

Err.Clear<\/p>\n

Resume Next<\/p>\n

End If<\/p>\n

End Sub<\/p>\n

Public Sub ProcessData()
‘get URL information from Control panel<\/p>\n

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<\/p>\n

lrow = pFindPosRow("URLHeader")
If lrow = 0 Then Exit Sub
lcol = pFindPosCol("URLHeader")<\/p>\n

sURLHeader = Range(numToAddress(lcol + 1) & CStr(lrow)).Value
sURLTail = Range(numToAddress(lcol + 1) & CStr(lrow + 1)).Value<\/p>\n

sURL = sURLHeader<\/p>\n

i = lrow + 2<\/p>\n

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

    sParamName = Range(numToAddress(lcol) & CStr(i)).Value
    sParamValue = Range(numToAddress(lcol + 1) & CStr(i)).Value<\/p>\n

    If IsNumeric(sParamValue) Then
       sParamValue = Range(numToAddress(Application.ActiveCell.Column) & sParamValue).Value
    Else
       sParamValue = Range(sParamValue & Application.ActiveCell.Row).Value
    End If<\/p>\n

    sURL = sURL & sParamName & "=" & sParamValue & "&"
    i = i + 1
Loop<\/p>\n

sURL = Mid(sURL, 1, Len(sURL) – 1)
sURL = sURL & sURLTail<\/p>\n

‘open browser
ActiveWorkbook.FollowHyperlink Address:=sURL<\/p>\n

End Sub<\/p>\n

Private Function pFindPosRow(sText As Variant, _
                             Optional SearchDirection As XlSearchDirection = xlNext, _
                             Optional SearchOrder As XlSearchOrder = xlByRows) As Long<\/p>\n

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)<\/p>\n

If Not oRg Is Nothing Then
  sResult = oRg.Row
Else
  MsgBox "Can’t find " & sText, vbCritical + vbOKOnly, "Error"
  GoTo Exit_sub
End If<\/p>\n

pFindPosRow = sResult<\/p>\n

Exit_sub:
Set oRg = Nothing<\/p>\n

End Function<\/p>\n

Private Function pFindPosCol(sText As Variant, _
                             Optional SearchDirection As XlSearchDirection = xlNext, _
                             Optional SearchOrder As XlSearchOrder = xlByColumns) As Long<\/p>\n

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)<\/p>\n

If Not oRg Is Nothing Then sResult = oRg.Column<\/p>\n

pFindPosCol = sResult
Set oRg = Nothing<\/p>\n

End Function<\/p>\n

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<\/p>\n

    numToAddress = sColAddress
End Function<\/p>\n

SAP BPC Tutorials | SAP BPC Training | SAP BPC Interview Questions |SAP BPC Books<\/a> <\/p>\n

Technorati Tags: sap bps tutorials<\/a>,sap bcs tutorials<\/a>,sap bpc tutorials<\/a>,sap bps help<\/a>,abap bps tutorials<\/a>,sap bpc examples<\/a>,sap bpc examples sap bpc definition<\/a>,sap bps examples<\/a>,sap bpc list<\/a>,sap bpc types<\/a>,sap bpc applications<\/a>,sap cpm examples<\/a>,sap bpc help<\/a>,sap bpc download sap bpc material<\/a>,sap bpc guide<\/a>,sap bpc cheats<\/a>,sap bpc problems<\/a>,sap bpc tips<\/a>,sap business planning and consolidation help<\/a>,sap bpc training sap bpc training<\/a>,sap bpc training material<\/a>,sap bpc overview<\/a>,sap bpc training courses<\/a>,business objects bpc<\/a>,sap bpc 7.0<\/a>,sap bpc certification<\/a>,sap bpc documentation sap bpc training in india<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-32","post","type-post","status-publish","format-standard","hentry","category-sap-bpc-tutorials"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts\/32","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=32"}],"version-history":[{"count":0,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts\/32\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=32"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=32"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=32"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}