Preventing drill down after query runs

[Former Member]

I have an Order query with a hierarchy attached to the infoObject. I created select-option on the Hierarchy node with a node variable. When the query runs if we tell it to display 1 level it works as desired by showing the node name and total. The problem here is the user can then drill down on the hierarchy and we don't want that because we have three summary queries in a workbook and a grand total number at the bottom.

We like the summary number but can we turn off the drill down?

Thanks

[marcus_lnnendonker]

Dear Richard,

have you found a solution to your problem that does not count on the users not knowing how to reactivate interactive features. If yes I would appreciate to hear from you.

Thank you very much!

Marcus, marcus.kronen@thyssenkrupp.com

[Former Member]

Hi Marcus,

I do not know how to turn off interactive features once and for all. But, here are a couple of ideas (implemented in a workbook).

1. I need a certain characteristic displayed as "Key + Text". Each time query is refreshed, I check to ensure the correct display of that characteristic. If the characteristic is displayed correctly, OK. If the characteristic is displayed, but not correctly, I use the OLAP command "PC02" to display it correctly. If the characteristic is missing, I push the query all the way back to "start". Here is code for this:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim c As Range, myCell As Range

Set myCell = Nothing

For Each c In resultArea.Cells

If c = "Sales Area" Then

Set myCell = c

Exit For

End If

Next c

If myCell Is Nothing Then

Run "sapbex.xla!SAPBEXfireCommand", "STRT", myCell

Else

Run "sapbex.xla!SAPBEXfireCommand", "PC02", myCell

End If

End Sub

2. This example more directly addresses the question of restricting the interactive features. In a workbook, the "Enable Interactive Functions" checked is stored in the query repository sheet (SAPBEXqueries) in column "O". TRUE if checked, FALSE if not checked. You can check to see if this has been changed from FALSE to TRUE, and if it has been changed, you can take the query back to "start". Here is the code for that:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim bexWS As Worksheet

Set bexWS = Sheets("SAPBEXqueries")

numQ = bexWS.Range("A2")

For i = 1 To numQ

If bexWS.Range("F" & i + 3) = queryID Then

If bexWS.Range("O" & i + 3) = True Then

bexWS.Range("O" & i + 3) = "FALSE"

Run "sapbex.xla!SAPBEXfireCommand", "STRT", resultArea

End If

End If

Next i

End Sub

- Pete

[Former Member]

Marcus, another way that will work in limited situations. If you want to allow the user to refresh the query only once per session and then defeat ALL changes. And, if there is only one query in the workbook. You can do the following:

Put this code in the code window of the ThisWorkbook object:

Private Sub Workbook_Open()

Dim bexWS As Worksheet

Set bexWS = Sheets("SAPBEXqueries")

bexWS.Range("A2") = 1

End Sub

Put this code in a code Module:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim bexWS As Worksheet

Set bexWS = Sheets("SAPBEXqueries")

bexWS.Range("A2") = 0

End Sub

This will allow exactly one refresh of the query. After which, the query will be completely invisible to BW. Each time the workbook is re-opened, it is reloaded.

- Pete

[marcus_lnnendonker]

Hi Pete,

thanks for sharing your code with us! I hope to find a solution for that in BW itself, but if not I am going to use your hints.

Kind regards,

Marcus.

[mstrein]

Hi Richard,

in the workbook properties you have the option to disable the olap funtions for the users. If you choose to do that they will also not be able to filter, change the display etc. right mouse click on the wb go to properties and uncheck enable interactive functions. do not know if there is an option to prevent the user from activating the option again.

regards,

Michael