Enable drill down in workbook using Macros

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Peter,

That was really helpful.

Unni