reset a datasource via VBA

[former_member58294]

Hello folks,

we are currently facing some performance issues regarding big data sets with IP and Analysis. We've been wondering if there is a possibility to reset a data source. Unfortunately the command "Call Application.Run("SAPSetRefreshBehaviour", "Off")" is only applicable for all queries and not able to select for one.

In Conclusion we would like to use one Query when pressing a button showing the second Query and disable the first one. Is there any functionality which will give us the possibility to do so´?

thanks for your help,

kind regards,

Alex

p.s.: We tried already with RRI but there is the Problem, that the user is locking himself during he has 2 open queries.   

[former_member245941 · Accepted solution]

Hello,

I am not entirely sure I understand your requirement about resetting a datasource. Should i have missunderstood your problem, please try to rephrase your requirement.

If you just want to refresh a specific datasource from your workbook while not refreshing the others, try the following:

Make sure the crosstabs you want to refresh are based on different datasource(s) than the ones you do not want to refresh.

Use Application.Run("SAPExecuteCommand", "Refresh", "<DataSourceOfYourChoice>") to selectively refresh only the desired datasource.

If you need the query prompt for the query use Application.Run("SapExecuteCommand", "ShowPrompts", "<DataSourceOfYourChoice>") instead.

(The "ShowPrompts" Option is only available since SP4 of Analysis.)

Regards,

Werner

[former_member58294 · Accepted solution]

Hello Werner,

actually we already fixed our issue with the following note:

Public Sub UpdatePauseState(Optional removePauseStates As Boolean =
False)

' This sets the state to "pause on" for all hidden crosstabs and to "pause
off" for all visible crosstabs
' The parameter removePauseStates = true sets
"pause off" for all crosstabs

  Dim lCrossTabs() As CrossTabInfo
  Dim lCrossTab As CrossTabInfo
 
Dim i As Integer
  Dim refreshList As String
 
  lCrossTabs() =
getCrossTabByVisibility()
  If IsNull(lCrossTabs) Then Exit Sub

  lCrossTab = lCrossTabs(0)
 
  For i = LBound(lCrossTabs) To
UBound(lCrossTabs)
      If lCrossTabs(i).isVisible = False And
removePauseStates = False Then
        ' pause on
        Call
Application.Run("SAPExecuteCommand", "AutoRefresh", "Off",
lCrossTabs(i).dataSource)
      Else
        ' pause off +
refresh
        refreshList = refreshList + lCrossTabs(i).dataSource +
";"
      End If
  Next
 
  If Len(refreshList) > 0 Then
   
refreshList = Left(refreshList, Len(refreshList) - 1)
    Call
Application.Run("SAPExecuteCommand", "AutoRefresh", "On", refreshList)
  End
If

End Sub

The Autorefresh for single Datasources is actually not documented but already "available" as an API.

thanks for your support,

kind regards, Alex

[former_member245941 · Accepted solution]

Hello Alexander,

good to know that there is an undocumented API Function to toggle the autorefresh behaviour of datasources.

Application.Run("SAPExecuteCommand", "AutoRefresh", <"ON"/"OFF">, <strCsvDSList>)

Thank you for sharing. That might be useful for me someday.

Regards,

Werner