Dynamic Calculation using VBA in Bex Reports.
Applies to:
Bex Reporting using Workbooks in BI 7.0. For more information, visit the Business Intelligence homepage
Summary
This article is a step by step guidance for using VBA Code in a Bex Workbook Requirement is to calculate Ratings based on Ratio field in Bex Report. As Bex is not capable of filling with a constant Value, the Options we have are: 1) Add this field to Info Provider and use a Routine in the update Rule or 2) Write VBA code in the workbook. Author: Sukthi Sattenapalli
Company: Intelligroup Asia (P) Ltd. Created on: 11 November 2008
Author Bio
Author is BW-BI Senior Consultant having worked in MNC/multi Country/multi Currency environment (US, Netherlands, Malaysia) in all phases of BI/BW Project implementations and played a crucial role in Upgrade Projects from 3.X to BI 7.0.
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 1
Dynamic Calculation using VBA in Bex Reports.
Table of Contents
Bex Report: .........................................................................................................................................................3 Requirement of Constant values in Report:........................................................................................................4 Visual Basic Editor:.............................................................................................................................................4 VB Code:.............................................................................................................................................................5 Related Content..................................................................................................................................................8 Disclaimer and Liability Notice............................................................................................................................9
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 2
Dynamic Calculation using VBA in Bex Reports.
Bex Report:
There is a report with characteristics and KFs as Below Material Material Freight Class
And Key Figures Weight, FoB Cost and Ratio
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 3
Dynamic Calculation using VBA in Bex Reports.
Requirement of Constant values in Report:
Based on above report the requirement is to have another field calculated FC which should be based on below logic 0.00 < Ratio < 5.00 = A 5.00 <= Ratio < 10.00 = B 10.00 <= Ratio < 25.00 = C 25.00 <= Ratio < 50.00 = D 50.00 <= Ratio =E
To solve this first we need to a formula variable to Query and set its value to some value like 0
Visual Basic Editor:
Then goto Macro and Visual Basic Editor
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 4
Dynamic Calculation using VBA in Bex Reports.
Insert New Module.
VB Code:
Add Below Code there Set ws = ThisWorkbook.ActiveSheet lngLastRow = ws.Range("A65000").End(xlUp).Row
For lngRow = lngLastRow - 1 To 1 Step -1 If ws.Range("A" & lngRow).Font.Bold Then Exit For If IsEmpty(ws.Range("A" & lngRow)) Then lngRow = ws.Range("A" & lngRow).End(xlUp).Row + 1 End If Next lngRow ws.Range("A" & lngRow).Select Set rgScope = ActiveCell.CurrentRegion lngLastRow = rgScope.Rows.Count + rgScope.Row - 1
For i = 1 To 10
If rgScope.Rows.Cells(1, i).Value = "Calculated FC value" Then
For j = 2 To lngLastRow - 8 Select Case rgScope.Rows.Cells(j, i - 1).Value
Case Is >= 50 rgScope.Rows.Cells(j, i) = "E" Case Is >= 25 rgScope.Rows.Cells(j, i) = "D"
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 5
Dynamic Calculation using VBA in Bex Reports.
Case Is >= 10 rgScope.Rows.Cells(j, i) = "C" Case Is >= 5 rgScope.Rows.Cells(j, i) = "B" Case Is < 5 rgScope.Rows.Cells(j, i) = "A"
Case Else rgScope.Rows.Cells(j, i) = "X" End Select And call the Module Name in SAPBEXon Refresh.
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 6
Dynamic Calculation using VBA in Bex Reports.
When Report is executed we can see the new ratings calculated in the Result area:
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 7
Dynamic Calculation using VBA in Bex Reports.
Related Content
How to integrate Bex with VB http://help.sap.com/saphelp_nw04/helpdata/en/ba/45583ca544eb51e10000000a114084/frameset.htm https://www.sdn.sap.com/irj/scn/thread?messageID=1719655 For more information, visit the Business Intelligence homepage.
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 8
Dynamic Calculation using VBA in Bex Reports.
Disclaimer and Liability Notice
This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade. SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk. SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.
SAP COMMUNITY NETWORK 2008 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 9
