[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 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