on 2005 Jul 13 3:25 PM
We have used macros extensively in reports for some calculations. How can I enable drilldown for these workbooks without affecting the Macros used for calculations. Macro calculations are currently hardcoded to specific cells.
Any ideas?
Thanks
Request clarification before answering.
Hi Unni,
Impossible to answer without more specific information.
But ... in general, the answer is to write the macros without hardcoding to specific cells ... right?
The problem is in knowing where the results are located, if someone changes the layout, right?
I use two things:
1. you should find in your workbook a subroutine named SAPBEXonRefresh. (If you do not, then you are using a new version of Excel ... add the subroutine manually as follows:
Sub SAPBEXonRefresh(queryID as Sring, resultArea as Range)
This subroutine will return a Range object that tells you exactly where to find the result table.
2. format Style. Each cell that is part of a query (characteristic filter cells, characteristic result cells, structure element headings, Key Figure results, et cetera) has a unique Style associated with it. To view these, drag the Style box to one of your Excel toolbars, then click around in the worksheet that contains your query. This helps a great deal in determining things like how many header rows there are (in case user added a drill across) and if any more or less characteristics are in the drill down.
3. if there are certain Characteristics that are REQUIRED and you want to allow others to be OPTIONAL, then add
SAPBEX.xla!SAPBEXsetDrillState, 1, [characteristic]
for each of the REQUIRED Characteristics. If the user did not remove a REQUIRED Characteristic, all is well. If they DID, this will put it back in.
4. Add instructions. I like to put the instructions in PowerPoint and then embed the PowerPoint onto a worksheet. I tell the user exactly which Characteristics or combinations are required and which are optional.
I hope this helps.
- Pete
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Peter, sorry for the delay in reply.
I have my macros under subroutine SAPBEXonRefresh.
Right now my macros are almost hardcoded, except to figure out the last row for which calculation is to be done. How can I make columns also dynamic?
Can you pls explain me further the importance of defining result area, and how to use the format style to identify the cell which I need to do the calculation.
I would appreciate if you can give an example.
Thanks
Unni
Hi Unni,
Here is an example that illustrates:
1. using resultArea variable in SAPBEXonREfresh
2. locating column headings
3. using format Style to locate Key Figures columns
Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
Dim ws As Worksheet, myCell As Range, docWS As Worksheet
Dim firstRow As Long, lastRow As Long
Dim firstCol As Integer, lastCol As Integer
Dim myRange As Range, ProdFam As String, SubFam As String
Dim firstResultCol As Integer
'indentify / select worksheet containing query results
Set ws = resultArea.Parent
If ws Is Nothing Then
Exit Sub
Else:
ws.Select
End If
'locate first and last row and column in query results
Set myRange = resultArea
firstRow = myRange.Cells(1).Row
firstCol = myRange.Cells(1).Column
lastRow = myRange.Cells(myRange.Cells.Count).Row
lastCol = myRange.Cells(myRange.Cells.Count).Column
'find columns for Product Family and Sub Family,
'customer Number and product name, and first result column
pfCol = 0: sfCol = 0: custCol = 0: prodCol = 0: firstResultCol = 0
custNum = 0: custName = 0: prodCol = 0
For j = firstCol To lastCol
If Cells(firstRow + 1, j) Like "Product Family" Then pfCol = j
If Cells(firstRow + 1, j) Like "Prod Sub-Family" Then sfCol = j
If Cells(firstRow + 1, j) Like "Customer" Then custCol = j
If Cells(firstRow + 1, j) Like "Fin Prod" Then prodCol = j
If firstResultCol = 0 And Cells(firstRow, j).Style Like "Item" Then
firstResultCol = j
Exit For
End If
Next j
'ensure that we found all required columns
If pfCol = 0 Or sfCol = 0 or custCol = 0 or prodCol = 0 Then
MsgBox "Unable to locate all requuired columns." _
& vbLf & vbLf & "Routine will now terminate.", _
vbCritical, "Routine did not update successfully"
Exit Sub
End If
'now work on each row in result table
For i = firstRow + 1 To lastRow
'do some things based on results found in each row
Next i
'eliminate X from Key Figures
If firstResultCol > 0 Then
Set myRange = Range(Cells(firstRow + 1, firstResultCol), Cells(lastRow, lastCol))
myRange.Replace What:="X", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
End Sub
I hope this helps.
- Pete
Hi Unni,
Some more comments on using format Style:
Be aware that user can defeat you here by changing local query properties so that format is NOT updated when query refreshes.
One way to locate areas WITHIN the result table is to note the way that BW formats cells in the Results Area. Excel provides a technique to define a particular format that can be applied to a series of cells; this is called format Style. A formatting Style is a set of font, font size, color, and patterns. Excel is equipped with categories of styles. You can use those ones or create your own. SAP has created its own custom Styles.
The best way to view these Styles is to
add the Style box to your Excel menu.
On the Excel menu, select
View >> Toolbars >> Customize
Select the Commands tab.
Select Format in the left window
Locate the Style box in the right window and drag it to an Excel toolbar.
By clicking on different cells in the BW workbook and studying what shows up in the Style box, we can deduce the names of Styles that BEx assigns to various query elements.
+ every Style name starts with SAPBEX
+ Characteristics names are SAPBEXchaText
+ Characteristics filter cells are SAPBEXfilterItem if + the Characteristic is not in the results table
+ Characteristics filter cells are SAPBEXfilterDrill if the Characteristic is in the results table (the cell appears as shaded).
+ Text element names are SAPBEXheaderText
+ Text element contents are SAPBEXheaderItem
In the Results Area:
+ Characteristics results are SAPBEXstdItem
+ Totals and subtotals are SAPBEXaggItem
+ Names of Key Figures are SAPBEXstdItem
+ Key Figures results are SAPBEXstdData
+ Key Figures lack of results (shaded light blue and displayed as X) are SAPBEXundefined
+ Highlighted Key Figures are SAPBEXstdDataEmph
+ Highlighted totals and subtotals are SAPBEXaggDataEmph
I hope this helps.
- Pete
User | Count |
---|---|
78 | |
29 | |
9 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.