Hi Sri
yes, I am automating using VBA (see code snippet below). There are prompts in the report and so I cycle through the data providers looking for conditions that are prompts. If I find one I replace it with a hardcoded condition. Once all of the conditions have been examined I unload the data provider and then refresh using bodp.refresh.
Observations:
- The problem occurs only when I run the report using VBA (no problems running manually)
- It is always the first data provider (suggesting that there may be a problem with all DPs in the report)
- Random reports fail (all automated using the same VBA)
- If I recreate the report (new file, make an exact replica) then the report is fine.
- If I just copy the report then there is no improvement.
Thanks very much for your help so far - is there any documentation on automating BO using VBA? I couldn't seem to find any.
Steve
Relevant code:
Note: MCC in the code below refers to a county in the UK and is the item that the report prompts for. We would call the function with a report and a county.
Function AutorunReport(Document1 As busobj.Document, sMCC As String)
Dim bodp As DataProvider
Dim boCond As Condition
Dim CondCount As Integer
Dim nConditions As Integer
Dim nDataProviders, nDataProvidersCount As Integer
Dim CondFormatCount As Integer ' counter for the number of conditions being added
Dim sMCCObjectName As String
Dim bDebug As Boolean
sMCCObjectName = "MCC"
nDataProvidersCount = Document1.DataProviders.Count
' Loop through each of the data providers and replace the MCC prompt condition with a hardcoded value
For nDataProviders = 1 To nDataProvidersCount
If bDebug = True Then MsgBox ("Data Providers : " + CStr(nDataProviders))
Set bodp = Document1.DataProviders.Item(nDataProviders)
'Count the number of conditions in the data provider
CondCount = bodp.Queries.Item(1).Conditions.Count
bodp.Load
For nConditions = 1 To CondCount
If (bodp.Queries.Item(1).Conditions.Item(nConditions).Object = sMCCObjectName) Then
' We have found a prompt - replace with a hard coded value
'remove conditions from data provider by calling the remove method the number to times equal to CondCount
bodp.Queries.Item(1).Conditions.Remove (nConditions)
'
'Add the condition for MCC
Set boCond = bodp.Queries.Item(1).Conditions.Add("Dimensions", "MCC", "Equal to", sMCC)
End If
Next nConditions
bodp.Unload
bodp.Refresh
Next nDataProviders
...
end function
