Solved

Business Objects Help

Posted on 2004-04-13
14
7,356 Views
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
0
Comment
Question by:Jorgefa
[X]
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
14 Comments
 
LVL 8

Expert Comment

by:baonguyen1
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.



0
 

Author Comment

by:Jorgefa
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.
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10819487
so if you have to enter the code when refreshing you can not make it run automatically
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

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

Accepted Solution

by:
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 ?

Hilaire
0
 

Author Comment

by:Jorgefa
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:
Application.Interactive=False
ActiveDocument.Refresh
Application.Interactive=True

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



0
 
LVL 26

Expert Comment

by:Hilaire
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.Refresh
        doc.ExportAsPDF (myreport & replace(col.Item(i), " ", "_"))
    Next
    Doc.close

set doc = nothing
set col = nothing

Hilaire
0
 

Author Comment

by:Jorgefa
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?

0
 
LVL 26

Expert Comment

by:Hilaire
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), " ", "_"))
or
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

Regards

Hilaire

0
 

Author Comment

by:Jorgefa
ID: 10824562
Hilaire,
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
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10824993
OK
<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
0
 

Author Comment

by:Jorgefa
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
0
 
LVL 26

Expert Comment

by:Hilaire
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
ThisDocument.Refresh
'now the cell should display "new title"

Hilaire
0
 

Author Comment

by:Jorgefa
ID: 10831078
Many many thanks!!
Best Regards
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

690 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