Refreshing a report 1

[DatabaseAdmin]

Hi All...

I have been facing problems refreshing a report from a single machine, this machine has a full client.

The message is "THE FOLLOWING DATA PROVIDERS HAVE NOT BEEN SUCCESFULLY REFRESHED....(DMA0007)".

I tried the same universe, user, etc, in other machine with a thin client, the message NEVER appears...

Any idea?.....

 

[skrandel]

This tells me that their middleware is not configured correctly.

If it's ODBC, make sure the DSN's are exactly correct.

If it's Oracle, make sure the TNSNames settings are correct.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[DatabaseAdmin]

Thank you Steve...

We have configured tnsnames.ora correctly in this machine....

The point is that some reports fail (give a partial view of data) and others run perfectly...

 

[skrandel]

OK, now I get it.

Make sure your universes are not restricting the amount of data that can be returned from the database. By default, they can only return 5000 rows. You may have forgotten to turn that off.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[DatabaseAdmin]

Yes Steve, we have covered that too....

We have Business Objects version 5.1.6 and in the
File/Parameters/controls Box none of the query limits parameters are checked.

How can we be sure that an specific data provider is working appropiately.....an archive log?....a query?.....

 

[Subhashab]

When the error is diaplyed can you click on the detail button and reply what is the error description it is showing

 

[DatabaseAdmin]

Well, the message "THE FOLLOWING DATA PROVIDERS HAVE NOT BEEN SUCCESFULLY REFRESHED....(DMA0007)" comes with no DETAIL button. I have log files about Zabo, HTTP but no others.

 

[naga5566]

Well,
Try this.Go to the DAta-->view data.Click om the definition tab and point your report once again to the universe and refresh it.I hope this works.

 

[Sridharan]

1. How many DP's you have in the report???
2. Try refreshing the DP separately.
3. Is the problematic DP using Universe or some other
source for data??
4. Do you have any macros?

Sri

 

[steveru]

Hi,

Did you get to the bottom of this?

I'm having the same problem... I managed to fix one of my reports by saving it as another name, deleting the original and then renaming the saved file. This didn't work for the other report which had to be reimplemented.

Only seems to happen when the report is automated. I can run my reports manually without any issues.

Regards
Steve
PA Consulting Group

 

[Sridharan]

Do you have any Prompts in the Document??? Provide a overall desc of your DP's and what they do??? BTB when you say Automated are you using VBA ???? What does that code do ????

Sri

 

[steveru]

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

 

[steveru]

The problem, it appears, lay with the remove condition function call which corrupted the SQL - without the remove condition everything is fine - of course, I had to delete the prompts in my original reports but at least it works.

 

[DatabaseAdmin]

Hi All,

Finally, we applied a solution like steveru's (Thank you Steve) recommendation (manipuling conditions with VB), and all reports functions very well now.