Business Objects Help

Posted on 2004-04-13
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
understanding output of mysql version 2 65
Latency in .net app using DB in .net 21 74
Index Details in SQL Server 2012 4 57
Question about consuming GB from Comcast 5 61
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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