Business Objects Help

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
Who is Participating?
HilaireConnect With a Mentor Commented:
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 ?

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.

JorgefaAuthor Commented:
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.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

so if you have to enter the code when refreshing you can not make it run automatically
JorgefaAuthor Commented:
So that is my question, I think that It has to be a way to program that
JorgefaAuthor Commented:
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

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

JorgefaAuthor Commented:
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?

>>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



JorgefaAuthor Commented:
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
<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
JorgefaAuthor Commented:
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
>>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"

JorgefaAuthor Commented:
Many many thanks!!
Best Regards
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.