[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Business Objects Help

Posted on 2004-04-13
Medium Priority
Last Modified: 2013-11-15
Hi all,
I am using business objects 5.1.3
I don’t have any experience, but I have to generate a lot of reports. The report is the same, what I have to do is refresh the report, change one parameter, and save it as pdf.
Tha guy in charge of that is sick, and nobody in the office knows business objects.

The report has a lot of querys, and is very slow
My questions are:
When one query has no data, there is a message “Fetching data. …. No data to fetch”. Is possible to quit this message. Sometimes in one report I have 10 o 12 messages and I lose a lot of time accepting messages.

Is possible to generate automatically the reports and save as pdf? I saw that you can use visual basic macros, but I do not how.

Please help

Sorry for my english, I am from spain
Question by:Jorgefa
  • 7
  • 5
  • 2

Expert Comment

ID: 10814795
if no data to fetch you will get that massage and you have to accept it

The only way I know to refresh the report automatically as scheduled is using the BroadCast Agent. You have to install that service on the server and schedule the task.


Author Comment

ID: 10814839
but I have to change one parameter , so I think that BroadCast Agent is not a solution.
when I refresh the report, there is prompt and I change one code, then I save the report as pdf.

Expert Comment

ID: 10819487
so if you have to enter the code when refreshing you can not make it run automatically
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 10820825
So that is my question, I think that It has to be a way to program that
LVL 26

Accepted Solution

Hilaire earned 2000 total points
ID: 10821243
Yes there's a way to program that
First you need to create a new (very small) report that will get the list of all the possible values for the promt
Then you'll have to loop through these values and refresh and export to pdf for each and every possible values.

I can provide VBA code but it will be easier if you provide more information
Could you post prompt definition ?
(I need to see variable names and promt definition (mono/multi selection, ...))

Do you have several data providers in the report ?
Could you post the generated SQL ?


Author Comment

ID: 10822085
Thanks for your help hilaire.

I find a solution for myself (i am sure that your solution more or less goes in the same way, so I'll give you the points)
Maybe is not the best solutions, but it works and as I need to generate de report urgently, it is ok for me.
I put here the solution, maybe it will be useful for somebody:

To skip the "No data fetch" messages, you can write a macro with the following code in the DocumentAfterRefresh event:

to generate automatically the reports and save them as pdf I use the following code:

'Declare Variables
Dim doc          As Document
Dim sPDFData     As String
Dim sPdfTarget   As String

sPdfTarget = "<Documentname>.pdf" 'Targetname for PDF
sPDFData = "<Path>" & sPdfTarget 'Name Report with .pdf Suffix

'Refresh Document
Set doc = Application.Documents.Open(ActiveDocument.FullName)
doc.ExportAsPDF (sPDFData)

to pass the variables values I use:
Application.Variables("Var1").Value = Param1 ' where var1 is the variable name, and param1 is the value

Hilaire, if you see problem on that, or you think that is a simplier way please tell me.

Many many thanks

LVL 26

Expert Comment

ID: 10822245
My macros do basically the same thing
- set interactive mode off
- set prompt variables programmatically

The only improvement I see is that when you have a finite set of values for a promt that match the list of values of an existing object, you can use a report to get the list of values (with a filter if need be)
eg get the list of main customers
then loop through customers
to generate a pdf for each and every customer

    Dim col As Column
    Dim doc As Document
    'Set interactive mode off
    Application.Interactive = False

    'Export des tables, résultats par pays
    On Error GoTo finreset  ' set Application.Interactive =true in error handling code
    Set col = ThisDocument.DataProviders.Item("Country").Columns(1)
    Set doc = Application.Documents.Open(myreport.rep")
    For i = 1 To col.Count
        'set values for document variables (prompts)
        doc.Variables.Item("Choose country").Value = col.Item(i)
        'hard-code if need be
        doc.Variables.Item("Choose Year").Value = "2003"
        doc.ExportAsPDF (myreport & replace(col.Item(i), " ", "_"))

set doc = nothing
set col = nothing


Author Comment

ID: 10823877
Thanks for your suggestions Hilaire.
Now I have another problem, I have to put a title to the report, and this title depends on the value of the variables, for example the title could be "Sales in Germany" for the report1, and "Sales in Spain" for the report2.
I try to create a variable call "aux" and change the value in the same way that we done before:
doc.variables.item("Aux").Value = "Spain"
But it doesn't work.
To create the variable, I went to the variable editor, choose new, and put "aux" in the name and in the tab formula I write ="xxxx".
By default, BO says that the variable is a "detail" variable.
I try to create another and put as "dimension" variable, but the result is the same
What i am doing wrong?

LVL 26

Expert Comment

ID: 10824095
>>I have to put a title to the report<<
do you mean save the file with a dynamic filename ?

the document.exportaspdf allows you to specify a file name for the file you want to save
The only constraint (for PDFs) is that you can't use file names that have spaces enclosed
So "Sales in spain" is not a valid name

'concatenate report name with promt value
doc.ExportAsPDF (strReportName & replace(col.Item(i), " ", "_"))
doc.saveas("Sales in " & Application.variables("anyvariable").value

or do you mean have a cell to display a dynamic title in the report itself ?
if so, you need to create a new variable in the report itself (not in the macro)
open the report in BO reporter
check menu View-->Report Manager
right-clic variables-->New variable
in the definition tab, type "report title"  in the name field
in the formula tab, type
="Sales in " & UserResponse(DataProvider(<Choose an object>) ,"Prompt text")

<Choose an object> = you must actually choose an object that you used to build the query
"Prompt text" : the BO formula editor should help you and detect te prompts for this data provider

Hope I'm not completely off-topic
Feel free to post the code you have it you think it can help




Author Comment

ID: 10824562
as you say, my need is a cell to display a dynamic title in the report itself.
The problem is that I don't have a query to put in <Choose an object>, i try to explain it better.
The value that I want to put in the title is really the filter that I use in the querys, so I don't have a query which give me a value.
Maybe we can see the problem in another way.

How can I put a value in a cell using VBA? It sounds simplier but I don't know if it really is.

Best Regards
LVL 26

Expert Comment

ID: 10824993
<Choose an object> is not used as a query, it's merely there to help BO to find out which DataProvider (some reports have several DPs) the filter belongs to.

I guess your report is based on a BO Universe rather than a custom SQL.
If so, chances are that the prompts/filters are defined in the Universe (rather than in the report itself)

That would be more simple I think

Author Comment

ID: 10826072
Sorry, but I don't know exactly what you mean. I think that I am explaining bad

It is imposible to put or change the value of a cell using VBA?

I just need to put a text in a place of a report, i don't want to change the values of sql or universes or things like that.

Imagine that I have a blank report without querys, and I want, using a VBA macro to put some text dinamically in the report.
For example I have a table with to cells, in one cell a Label, and in the other cell I want to put some text using VBA, is this posible? How can I do that?

Best Regards
LVL 26

Expert Comment

ID: 10831007
>>It is imposible to put or change the value of a cell using VBA?<<
I guess it's possible but it's not a good solution.
In BO you can't get a handle to a cell/VBA control to change its caption/text property

>>For example I have a table with to cells, in one cell a Label, and in the other cell I want to put some text using VBA, is this posible? How can I do that?<<
- Create a new variable in the report , name it "ReportTitle", formula :      ="ReportTitle"
- Right-clic on the cell --> Variables --> Choose ReportTitle , press Insert
- The cell should display "ReportTitle"

To change cell value, you just need to change ReportTitle formula
ThisDocument.DocumentVariables("ReportTitle").Formula = "=""new title"""  
'change is visible after refresh
'now the cell should display "new title"


Author Comment

ID: 10831078
Many many thanks!!
Best Regards

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question