BusinessObjects Board

FAQ: Reporter

:?: How do I get rid of or change the message ‘No Data To Fetch’?

:!: The VBA code below refreshes the report and supresses all messages from the application, including ‘No Data To Fetch’…


Dim DP As DataProvider

Private Sub Document_BeforeRefresh(Cancel As Boolean)
    Set DP = ActiveDocument.DataProviders(1)
    Application.Interactive = False
    DP.Refresh
    Application.Interactive = True
End Sub

Here is a report level solution from Andreas for supressing the message…

In your reports you would have to filter out these additional rows.

Another report level solution doesn’t supress the message box but provides a user note on the report…


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: How can I import data from Excel and keep the numbers numeric?

:!: Submitted by Dave Rathbun. If you work with reports that include data from Excel, there is a common problem where numeric data that should be treated as character data is imported as numeric. If it comes in with the wrong type, it may be a problem for aggregation, linking, or other variables. So I wrote the following really quick macro that can be used in any Excel sheet to convert a numeric value to a text formula.

Sub Set2Text()
    
    ActiveCell.Select
    While Not (IsEmpty(ActiveCell))
        ActiveCell.FormulaR1C1 = "=" & """" & ActiveCell.Value & """"
        ActiveCell.Offset(1, 0).Select
    Wend
        
End Sub

Simply enter this macro in your worksheet, highlight the first cell, and run the macro. It will go down the column until it reaches a blank cell replacing 1234 with =“1234”. This data will be recognized as character data when the Excel data is imported into BusinessObjects.


:!: Submitted by Brie Anne Clark :

I wrote a macro much like yours a while ago, but mine just adds an apostrophe to the beginning of the cell value to convert it to text.


Sub AddSymbol()
    Do Until ActiveCell.Formula = ""
        ActiveCell.FormulaR1C1 = "'" & ActiveCell.FormulaR1C1
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

:!: Submitted by Dwayne Hoffpauir on 3/17/04:

This is the method that I use for Excel personal data providers, and at the risk of jinxing myself, it never fails:
Instead of using a “live” Excel file to build the data provider, create a sample file. Same column headings, but use dummy data.

Put alpha characters in the columns you want to be alpha, numerics in numeric, and so forth. Maybe 20 rows, but every row the same. It’s usually columns that have ambiguous data (could be alpha, could be numeric) that confuse BusObj.

After creating the data provider, you can now put “live” data in the Excel file, and the definitions will “stick” from now on.


Anita Craig :us: (BOB member since 2002-06-17)

:?: Help, my Slice and Dice panel or my Query panel has disappeared.

:arrow_forward: Press and space. This displays the standard windows menu. Now select Maxsimize and the appropriate window should now be displayed.

If you are using Citrix you may need to edit the registry in order to alleviate the problem. The appropriate key to delete is:

HKEY_USER\Software\Business Objects\BusinessObjects\5.0"UserName"

where “UserName” is the BusinessObjects login of the user encountering the problem.


Nick Daniels :uk: (BOB member since 2002-08-15)

:?: Is it possible to make a copy of a data provider in the same or in a new document?

:arrow_forward: The current versions of BusinessObjects (up to and including version 6.0.0) do not include this functionality. However, we do have some code on this site which will allow you to achieve this. See here for details.

:?: What about 6.1.x?

:!: In 6.1a (or later) if you already have 1 data provider and want to create a similar second data provider then you can do the following: Go to Data > New Data Provider in the menu. Then on the options displayed select “Use an existing query to build a new one”. A list showing all the available data providers is displayed. You can select the one which you want and click OK and a similar new data provider is created.


Nick Daniels :uk: (BOB member since 2002-08-15)

:?: Why do I get a Syntax Error (DMB0007) when I try to Sum() Where ( > 2000)?

:!: The syntax of WHERE Clause is:
measure Where (dimension = (number or character string or date, dimension = …))

This means that the WHERE Clause in Business Objects Reporter only allows the “=” operator, and the right side of the equation has to be a constant as well (and not another report variable etc.).

For example:

Sum(<Sales>) Where (<Year> = 2000)

will work

Work around for original problem:
Create another local report variable “VAR Flag Is Year greater than 2000” defined as:

= (<Year> > 2000)

This will be a boolean variable returning 1 or 0.
Make sure that the universe object is of Data Type Number.

Now modify your original formula to use:

Sum(<Sales>) Where (<VAR Flag Is Year greater than 2000> = 1)

Cindy Clayton :us: (BOB member since 2002-06-11)

Limiting an object based on two variables being equal
variable in where condition
Where Clause
Using more than one argument in "Where" function..
CountIF
Formula for adding from from the same filed
Inlist or OR
Difference in Measure based on User Response
Litteral and variable
Syntax error DMB0007 using Where
How can display subtotals in report area
Conditional summing syntax?
Data display
Dynamic Data bucketing using formulas
Where/inlist workaround
Problem with a Variable
If/Where Condition
cut in many variable an object indicator
Using UserResponse in Where clause?
#COMPUTATION error...please help!
About Yearly report
Variable using Last Months revenue
Help me - summing question
#computation Error
#CALCUL
Count with If Else statement
Use of "Where" syntax within a report variable.
How to get an interpolated result in a report?
Use of where operator and nested functions with variables
Problem with WHERE and InList
Problem with WHERE and InList
Formula changes to specific segments of Columns
Creating a variable using not equal to?
How to get data for Prior Year Full Month
using in condition in the formula
Cells showing Summation values from tables
Using value from user response to make the report dynamic
Syntax Error in where clause using UserResponse function
Sorting in Cross tab report for top 100
Report Issue
Variable condition from user response
Variable condition from user response
RunningSum -- Don't want to display 'future"
Where operator with Measure
How to define Quarter days
Reporter Query
Query Help
Totals in Section
How to count the number of &lt;whatever&gt; between two date
Use UserResponse in Formula
why can't i use a not equal condition on a where clause?
previous month/week values for more than a YEAR.
Last four week average
To create Variables that conditions measure variables
Using Min and Max in Where statement
What's wrong with this variable??
Combining where function and detail
Syntax Error
Not in operator
Multivalue error
UserResponse in a formula
crosstab sorting issue
WHERE operator
Variable in Formula
sum is not working properly
Using Where() Function to Compare Dimensions and Variables
Trying to get count of Doc id's where recieveddate = duedate
DMB0007 error using where /Computational error using If.Then
converting a string to a number
Caluculated Period from Current Date in WEBI
Does Where operator work different in DeskI and WebXI?
VAriables not working in WEBi XIR2
Convert using values from another table
How to work with Aggregations
What's wrong with this Count statement?
Where
Or condition in variables
Section calculating
Year and Quarter
formula problem
Calculate in Dynamic cross Tab
this year and last year as a variable
this year and last year as a variable
usage of the "where" clause with a variable...
YTD Calculation Variable

:?: How can I supress the ‘No Data To Fetch’ message?

:!: There are multiple methods of supressing the ‘No Data To Fetch’ message.

Programmatically…
If you copy this code into the ThisDocument module, it should work. To implement the code, you will need to have your Report which gives you the No Data to fetch message open. Click on Alt + F11, and the Visual Basic Editor will open. On the left hand side you should see a list of all the documents you have open. Under the name of the document which gives you the message you will see a icon called ThisDocument. Double click on it and on the main window, a blank sheet should appear. It not only suppresses the “No Data to Fetch…” message, but any prompts and so forth are also suppressed. It even turns off the hourglass cursor, so it can be confusing to the user.

Private Sub Document_BeforeRefresh(Cancel As Boolean)
    Application.Interactive = False
End Sub

Private Sub Document_AfterRefresh()
    Application.Interactive = True
End Sub

Note that this code is executed only when a full refresh is performed. If you run queries individually (from the query panel or Data Manager) the above events are not triggered.

By creating a union query…
One trick used to get around this problem is to create a class of “report objects” in the universe consisting of blanks and zeroes not attached to any table. Also create at least one of these objects that references a dummy table such as SYSDUMMY1 in DB2 (v5 and higher) or DUAL in Oracle. The objects that don’t reference a table will not parse in Designer, but they work fine as long as at least one object in the query does reference a table.

You can then take a report where you don’t want to see the “No data to fetch” message and create a union query using the blanks, zeroes and the object referencing the dummy table. This will guarantee that at least one row will be returned by the query. The zeroes added to numeric columns will not influence the result. Create a report filter to hide the union row. This technique can be used for long-running reports that contain multiple data providers where processing should not stop if one of the queries does not return data.

Using a dummy table and contexts…

The way to get the No Data to Fetch message to disappear is to put a dummy table in your database with a single row with a single column with a value of 1 or use a system table such as Oracle’s dual table. In your universe, add your dummy table as a class with a single object. Put a self-join on the dummy table. In the object definition put in the where clause that the column equals 1, so that the value will always be found by the sql. Put the self join in a separate context. It should be the only join in the context. Do not include the join in any of your other contexts.

Include the dummy object in your result set in your dataprovider when you don’t want the No Data to Fetch message to appear. The dummy object in its own context will cause B/O to create two datacubes for the query because two different contexts are used. Because of the self-join always being true you will always get some result. Just hide or delete the dummy column in your report.

Displaying a friendly user message if no rows are returned…
If you simply wish to display a friendly message on the report when no rows are retrieved, check here for one method.


Cindy Clayton :us: (BOB member since 2002-06-11)

Disable Prompt Message when No data found
Bypass "no data to fetch" messages
How to supress the "No Data to Fetch" using Macro
AfterRefresh and No Data To Fetch
Displaying a report when no data is returned
Keep Prompt, Suppress No Data to Fetch
Data provider yielding no data to fetch return msg
How do I turn off warning messages?
"No Data to Fetch" problem
how to erase the default message?
how to erase the default message?
Displayalerts = False
"no data from dataprovider" message
Insert "no data to retrieve" message into a report
Multipule Data providers
"No Data to retrieve" Pop up block
How to display 0 in one row when there is no data in query
Hiding prompt window
Is there a way to not display 'No data to retrieve'
Prompt defaults and empty query results
Supress
"No data to fetch" message in WEBI
how to hide 'there is no data corresponding to this query'
Supression of error message "DataProvider empty"
How to display a custom message in the report ?
Need Yes/ No option in the prompt.
How to Suppress No Data to fetch error in Deski
No Data To Fetch
How to implement a message box for an error message
Handling Message box
No Data to Retrieve
How to hide the No data message box in WebI XI R2?
Returning a zero if no records found
Avoid "There is no data corresponding to this query&quo
Yes
in deski report how to suppress the no data to fetch message
How to generate Excel file even there is no data to fetch.
Getting Rid Of Prompt No Data To Fetch
FETCHING DATA Window
How do you prevent the pop up info boxes on full fc reports
unwanted pause on no data to fetch
Acknowledgemen on DeskI schedule results
Disable Message Upon Retrieval of Empty Record Set
no data to fetch
supress Message "NO data to Fetch"
Suppress No Data Fetch
Setting conditions on values in Prompt
How to disable pop-up window, "No data to fetch"
Returning 'NO Products Available' in one,many or ALL prompt
can we customize the error message?
Help Needed--&gt;No Data to Fetch
No Data To Fetch
Command Line Run but No Data to Fetch
Pop-up message for "No data available"
Removing 'no data to fetch' messages
Prompts Questions
How can i remove "No data to fetch" error message

:?: How do I make an object that selects the current date?

:!: Create an object in Designer that uses your databases’ method of selecting the current date. In Oracle, your object would select ‘sysdate’. The object will not be attached to any table and as a result will not parse successfully. Never fear :smiley: . It will work when combined with other valid objects attached to tables.


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: What is synchronization?

:!: Synchronization happens when there are no (or not enough) common dimensions in each query. The desired result, when running queries across multiple contexts, is a join not a synchronization operation. You can find out the exact operation being performed by looking at the SQL from the query panel, you will see a number of “folders” on the left side of the SQL window, and at the top, the operation used to combine them. If you see “synchronization” then your queries don’t have enough in common.

When does BO “Synchronize”?
Rule of thumb: When the Data provider spans 2 or more contexts, and some dimensions do not exist in every context used.

Note (by Andreas, Feb 2008):
Consider using the universe parameter JOIN_BY_SQL (available since BusinessObjects XI R2), if a data provider generates multiple SQL statements, whose microcubes are “joined” to push down the merging of the microcubes from the BOE server to the database server/DBMS.


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: Is it possible to point a dataprovider from one universe to another?

:!: Yes it is possible. In order to do so, the target universe must have the same class and object name structure as the source universe. BusinessObjects uses class name and object name when pointing dataproviders from one universe to another.

Instructions submitted by beloved BOB member Andreas…

You can change the universe a data provider is pointing to by going to BO Reporter, menu Data| View Data…, in the window “Data View” select your data provider from the left pane and switch to tab “Definition”. Notice the entry under Universe, click on the “…” button to display the available universes (universes your BO user ID has been granted access to via BO Supervisor). Note that the current universe that the data provider is pointing to is not listed in the Window “Change Universe”.
Now select the new universe from the list you want the data provider to use, select OK and save the BO document. Repeat the steps for every single data provider.

Possible problem:
The universe that a data provider is based upon has already been removed (your access to the universe has been denied, or the universe has been renamed or deleted). In this case if you try to point the data provider to a different universe you might get the error message “Cannot find the ‘UniverseName’ universe. DA0011”.

Solution:
Ask the BO Supervisor or a BO Designer to re-point the data provider to another universe (they might have a copy of the old universe or they might still have access to the old universe).

Do NOT try to open the BO document with a text editor and modifying it in the text editor, because this will most likely lead to a corruption of the BO document.

Advice:
Always re-point all your data providers of all your documents to the new universe BEFORE you remove/re-name any existing universe used by data providers.


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: Can I sequentially number report pages across all tabs?

:!: Yes.

Scott Bowers came up with this programmatic solution…

First, create a variable for each tab that is named exactly the same as the tab name. Then, use this code:


Sub PageNumberTabs()

    PageNum = 0
    
    For i = 1 To ActiveDocument.Reports.Count
                
        If i <> 1 Then
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() + " &amp; PageNum &amp; ", ""#"")"
        Else
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() , ""#"")"
        End If
        
        ActiveReport.ForceCompute
        
        PageNum = PageNum + ActiveDocument.Reports(i).NumberOfPages
        
    Next i
End Sub

Have this called from the afterrefresh() event. It has been tested it and it numbers all the tabs correctly. Even if you move the tabs around, there are no code changes that have to occur.

Here is another version of the code…


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: How can I calculate the number of hours between two dates?

:!: The best way to do it (and the fastest) is to do it in the database. If you simply subtract the two days, you will get a number in the format D.T, where “D” is the number of days, and “T” is the fraction of days in decimal form. In other words, day 2 and noon minus day 1 at midnight will yield 1.5 as the result.

Take this number and multiply by 24, that gives you the number of hours. Take the decimal portion of that and divide by 60 and you get the number of minutes. So if you have 1.5 (earlier example) and multiply by 24 you get 36 hours and 0 minutes.


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: I had a power outage and now BO is telling me that I cannot open my report because it is corrupt. How can I save my work?

:!: Search your computer for a temp file saved at the time of the outage. The file may not have a name anything like the original filename. If you can locate this file, it should open and contain the work you had saved prior to the outage. Read this thread for more detailed info…


Cindy Clayton :us: (BOB member since 2002-06-11)

:?: What is the difference between Full-Client Reporter and ZABO (Zero-Admin BusinessObjects)?

:!: The highlights:

  1. Installation is done via InfoView, so no workstation by workstation installation is needed.

  2. As far as reporting functionality, ZABO and full-client are identical, with the exception that ZABO cannot use either personal connections nor FreeHand SQL.

3.) One difference is database connectivity. With ZABO, all database connections are managed by the ZABO/WebI server. With full-client, database middleware and related configuration is on each workstation.

So, Full-Client Reporter is a “2-tier” tool – the Reporter module sends the query to the database, and the results are brought right back to your computer.
Reporter <–> Database

ZABO is a “3-tier” tool – the ZABO Reporter module formulates the query, and sends it to the ZABO/WebI server, which then sends the query to the database (using the database middleware and configuration on the server) – the ZABO/WebI server receives the data cube back – and then forwards the data cube to the ZABO client.
ZABO Reporter <–> WebI Server <–> Database

4.) Another difference is the installation.

With Full-Client Reporter, someone has to install/upgrade each individual workstation.

With ZABO Reporter, the initial installation is done by logging in to the WebI server (via Infoview) – and setting the WebI options to edit full-client documents with BusinessObjects. Then, the first time you try to build a query, or edit an existing one in the repositoy – the WebI/ZABO server launches the installation of a mini version of the Reporter module on the client computer.

Afterwards, for upgrades, only the WebI/ZABO server is updated directly. Users need to log into WebI via Infoview, and create/edit a full-client document – and ZABO will detect that an upgrade is needed – and will launch the upgrade.


Anita Craig :us: (BOB member since 2002-06-17)

:?: How can I include data for all months in a crosstab, even if a particular month has no data?

:!: One way to do this is to create a second data provider using only the months. Then link (or “merge”) the second data provider (query) with the first data provider. Use the month column from the second data provider in the cross tab. This should bring in all months even if there were no data for that month. However this technique does not work if you have sections in your report.


Dave Rathbun :us: (BOB member since 2002-06-06)

Fill empty cells in pivot table with 0
No sales report
BO Report needs to null value included and does not
Date not showing when using Sections.
Show rows with zero values
How to visualize user's prompt response with no data?
Repeating a date in crosstabs
date
How to echo rows with empty data
Help required for this unique requirement on date
How to show blank dataset on graph
Webi Function - Relative Month
Null Value also needed in Column
Displaying month values
Graph - problem in the X axis
Show saturday and sunday in my cross tab
Variable not showing when null
table with no info
Webi 4.0 "Structure with all Quarters"
How to show zero
Linear Chart - Empty Data
Hard coded null values
How to fix the coulumn of a cross tab
NULL values are not displayed in Webi
Cross tab display empty columns
Running Dates for each row
Displaying empty Monthly buckets
Returning Null Values
keep empty column in a crosstab
How to show all days of month in lines?
All values of filter "In List"
CrossTab - Force a 3rd col when data for only two vals exist
Column Display
How to display columns with null values in deski6.5 crosstab
cross tab
Non-events / coverage table
Forcing Colums to appear
Urget - PLease help!! How to show all headers/ columns
Desktop Intelligence 12.1.0.882 - Show Fields in Report
Problem Displaying Header
How to display months with 0 measure values
Displaying future quarters in chart
add the column in crosstab
Merge Problem
display the column with no data to zero.
Dynamic Field/Column
Dynamic Field/Column
crostab with missing columns after setting a master
cross-tab feature
month formula not working
All Quarter Display
Filling in the blanks!
How can I shown all the coulmns....
Prompt Dates
Show Breakdown title without data
Display all Entries in a Report
How to display missing data as broken line in the chart?
Date Objects
Day needs to display when there is no data
SQL Case statement ranges and literals
Getting no data in a category to show up as zero.
Data display
extra colom is displaying in crosstab report
Show row when there is no Values
Displaying Null Record
Not suppressing rows
Missing months
Force display on grouping
Show All Field In A CrossTab Summary
Align Cross Tabs to the Right
Cross Tab Report

:?: How do I migrate documents between repositories?

Note (by Andreas, Feb 2008, updated Feb 2011):
For “SAP BusinessObjects XI 3.x” and “SAP BI BusObjects 4.0” use the LifeCycle Management Tool (LCM) to promote content from DEV → TEST → PROD.
For BusinessObjects XI R2 use the import wizard, a tool that comes with XI R2 to promote BI content (such as reports, universes, universe connections, user groups, ACLs, etc.) from DEV → TEST–> PROD.
If you are dealing with Performance Management (Note: discountinued with SAP BI BusinessObjects 4.0) you must use the PMDT (Performance Management Deployment Tool) in addition to the import wizard.


:!: Steve Krandel describes this process the best (BusinessObjects version 5.x/6.x):

Moving the universe:

  1. Login to Designer using the BOMain key for your QA repository
  2. Import the universe you wish to migrate. Change the connection of the universe to a shared connection.
  3. Go to File > Save As. Save the universe in this folder: C:\Program Files\Business Objects\BusinessObjects 5.0\Universe (Note: There may or may not be another folder named ‘Universe’, it is important to save the universe in the above referenced location as this will release the security permissions)
  4. Go to Tools > Login As. Login using the BOMain key for your Prod repository.
  5. Go to File > Open. Open the copy of the universe that you saved in step 3.
  6. Go to File > Parameters. Select a secured connection for your data source.
  7. Go to File > Save
  8. Go to File > Export

Moving the report(s):

  1. Login to Reporter using the BOMain key for your QA repository
  2. Go to File > Retrieve from > Corporate Documents
  3. Select the report you want to move, click retrieve.
  4. Go to File > Save As. You can use the default User Docs folder, or any folder of your choice.
  5. Make sure that you check the ‘Save for all users’ option, then save the report.
  6. Go to Tools> Login As
  7. Login using the BOMain key for your production repository.
  8. Go to File > Open and select the report that you saved in step 4 above.
  9. Go to Data > View Data (or click on the “Cube” button in your toolbars)
  10. Click on the Definition tab.
  11. Click on the button with three dots next to the universe name.
  12. Select the universe that you moved in the universe migration steps above. (This is also the same process you would do if you want to change universes for any document).
  13. Go to File > Save.
  14. Go to File > Publish To > Corporate Documents

Here are some shortcuts to the Moving Reports steps above as suggested by many of the people from the forum:

Open document and do Save As and use Save for all users. Log into the second repository and Publish the document. When you open the report it will automatically switch itself over to a universe with the same name in the new repository. WebI reports apparently work the same way if you save them as personal documents first.

The only caveat to this is the original universe from repository “A” needs to be deleted from your local directory. Otherwise, when you switch BOMain key to point to repo “B” and open the report after doing a “Save for all Users” you will get the error “A connection required to refresh this document is unavailable”. This is because it still recognizes the original universe (or so it would seem). When you delete all local instances, it is forced to import the universe from Repo “B” and therefor does the seamless switch. This is of course only if your universe short names match.

Here are some important considerations and issues encountered when you migrate documents (or universes) between repositories (as posted by our Forum Moderators):

  1. Moving repositories (from one database to another) is easy for everything but documents. For some databases (Oracle to SQL Server) it seems to be pretty straightforward. For Sybase to anything, I think the security and universe domains will be fine. The document domains likely won’t work. Sybase treats BLOBs differently that Oracle. They simply do not convert correctly. I’d make sure you do a BIG test first.

  2. If you cannot change the repository connection from secured to shared/personal or visa versa as this box is disabled, it is because that is not possible. You must create a new shared connection. You need to make sure that you have rights to do this from the Supervisor Module.

  3. After moving the updated universe to production first (if needed), open each document in production, open each data provider and click “save and close,” before finally saving the document and publishing to corporate documents. The “save and close” is the only we have found to guarantee that the connection information is updated properly.

  4. After you created the new repository and imported the old info – make sure you go into the new repository and change all of the connnections to point to the new repository domains.


Dave Rathbun :us: (BOB member since 2002-06-06)

:?: Why is my report so slow?

:!: Submitted by kmahler, additional comments by Andreas (July 2009).

What makes up the total time a report runs from hitting the Refresh button to being displayed:

  1. Query execution time (database side)
  2. Transferring results across the network to the client/web browser (network bandwidth & latency)
  3. Computing local report variables, formulas, local report filters, alerters, etc. within the report (BOE server/ DesktopIntelligence workstation)
  4. Rendering the page (page layout, page margins, page numbers, etc.)

1. Optimizing Query Performance (tuning universes and databases):

There are a number of things you can do to speed up your query. The first things I will do is to review your universe to minimize the un-necessary joins, using aggregate awareness, and shortcut joins if you can. Creating appropriate indexes definitely will speed up the query performance also. Make sure all of your measure objects have aggregrate functions.

Your Oracle database may be using either “Cost based” or “Rule Based” optimization. You need to find out which your system is using. If your system is using Cost Based, (which is the more recent offering), you must make sure the “Statistics” are up to date. Oracle under rule based oprimization uses row counts etc to determine the fastest path to resolve queries. Once you are happy that the stats are up to date you can use “Hints” to alter the approach the db might take.
If your system is using Rule Based optimization (old technology!), the order of the the tables in the “From” clause are important, altering these by manually assigning row counts in designer is how you do this.

In particular for Oracle:
If you are using a star-schema for your data mart turn on the STAR TRANSFORMATION option in Oracle, prefer bitmap indexes for your dimension tables. Check also for your block size and if your (fact) tables are fragmented (tablespace).

For large data volumes consider partitioning your fact table for example by time dimension (Year, Month), country dimension, etc…

If you are able to look at the Plan of execution, this may give you an insight on how you can make improvements. Most of DBMS (Oracle, IBM UDB, etc.) will have some sort of utility to find out what index the SQL statement are using, what execution path they go through. You might need to use this kind of tool(s) to find out what’s wrong with the SQL first. Investigate your SQL statement in SQL Plus (Oracle) or a similar tool. In a past job, I was given the task of performance tuning SQL statements. Minor changes to the SQL can have tremendous impacts on retrieval times. Just because a statement is using an index does not mean that it is using a good index. Especially investigate selected objects that are also used in table joins. Which table you are pulling the object from can make a big difference but still yeild the same results.
You must have someone do an Explain Plan on your query. The result will provide a starting point for your performance tuning. If you have the knowledge to do this yourself, I recommend using TOAD, SQL Station, DBArtisan, etc. These tools will also let you monitor Server Session statistics to see what your Server is doing while your slow query is running.

Consider playing with the value of the array fetch size on your universe/database connection (see also 2.). The optimum value is different for everyone.

Consider building summary/aggregate tables (in the database) to speed up queries, for example: Sales transaction precaculated at the granularity of Month (instead of daily sales transactions). As a rule of thumb your summary table should contain 10% or less rows than your original fact table to produce measurable performance gains.
To utilize these summary tables use the Query Rewrite Option (Oracle, etc.), which is totally transparent to BusinessObjects or if needed use @Aggregate_Aware in the universe/Designer.

Universe Design

  • Make sure all your universe measures are using a SQL aggregate function (as a rule of thumb) to reduce the size of your result set by aggregating the data at the database level (instead of projecting the data in your report). Note: SQL aggregation and projection for measures (set via Designer) are two very different things.
  • Evaluate shortcut joins or Index Awareness.
  • Consider using the universe parameter JOIN_BY_SQL (available since XI R2) (see B) below) if a data provider generates multiple SQL statements, whose microcubes are “joined”.

2. Network performance
Make sure you are using a fast network connection with appropriate bandwidth (between database/DBMS hosting your source data/data mart and the BOE server/DesktopIntelligence workstation), check for latency, required hops, and packet loss (if any) from the database to the BOE server, using the command PING or NETSTAT for example.
Again, adjust the Array Fetch Size Parameter (universe connection via Designer, see also 1.). Rule of thumb: if your network is reliable (no packet loss) choose a higher Array Fetch Size.

3. Optimizing Computation Time (Reporter):

Suggestions:

  • Reduce formulas/local report variables and complex filters (reporter side), etc.

  • Use variables instead of formulas

  • Remove Auto-sizing (auto-height/width) if possible

  • Look at drilling and applying drill filters to your data provider

  • Parameterize your documents using Prompts

  • Limit the amount of data, if you can limit rows further that will help

  • Crosstabs and complex formulas will slow it down

  • Complex filters, breaks, everything will slow it down a little

  • Charts can slow the report tabs down

Here are some other ideas:

A) Is the result set of data in the report very large? Perhaps you should add more conditions/prompts to your query to reduce the number of records returned from the database.
Do not expect a report containing 100ks of rows to be fast, as it has to be rendered as well.

B) Is the report very complicated, eg. does it contain several data providers/queries or many calculations? You might try to see if you can reduce the complexity of the report. Data providsers are executed sequentially, and the merging of the microcubes will cost performance.
Consider using the universe parameter JOIN_BY_SQL (available since XI R2), if a data provider generates multiple SQL statements, whose microcubes are “joined”.

C) Is the query on the database side taking a long time to execute? Then you should work with your DBA to see what can be done on the database side to optimize query performance (show the generated SQL to your DBA).

D) Consider optimizing your BOE cluster architecture (XI R2): For example configuring additional Webi Report servers, adding cache, etc.


Chris Pohl :us: (BOB member since 2002-06-18)

What does BO do in the background?
Performance tuning tips in Business Objects reports
Highly-used CPU due to long-running report.
Compress a Business Object Report
Report Tuning
Running Bo Report - performance
Make it faster
How can we apply Performance tuning at BO level
Reports Taking long to time to come back
Trade of for number of report variables.
computing calculations
Increase performance
how to check the performance of the report
report performance
Report is taking long time to populate data into webi Report
Performance tuning of DeskI reports
Performance tuning techniques
Reports Performance.
Queries
performance improvement
Universe and Report Tuning on ODS
Webi reports are extremly slow
Business Objects Performance Tuning
OLAP Universe on MSAS2005 cubes
Report 'performance'
Restrict inputs in In List prompt
Universe
Performance issue while running a report
Report running slow
webi Report taking time to refresh (xi3.1)
Webi report takes time to open / refresh
Some advice on how to performance tune SAP BI BusObjects
Tuning Report Performance
report performance
Universe optimization
Report taking extra time to get refreshed
Improve performance when Viewing Report
Report Level Performence
Webi Report Performance
200 million records in Fact table !!
Reg webi report
How to optimize Universe performance
Performance Tuning of Webi reports
Views from OWB to Designer Universe?
Performance issues........
Webi & Better Performance
Data fetch message
Performance Issues
Suppress "No Data To retrieve"
Report Level Performance
Do you know where I can find it?
Need Help.....For performance
Webi Reports taking long time to refresh
Measuring performance of user interaction
"Flat" versus Dimensionally modelled data.
Performance issues
Report Performance
Report performance tuning and Universe optimization
Performance : 500k rows in 7 min.
improve the report performance
Optimization in Universe
Best Practice for Report Performance ??
How to make universe Performance faster?
Improve Performance of report any suggestions
how to improve the performance of the report
How can we tune the free hand sql DeskI report
Reducing table columns increase the performance of a query?
Performance Tuning of reports
Report is very slow
BUMP: Performance of reports - UPDATE
Web-I Performance Issues
LOV
Query Time
Report performance

Is it Possible to Query the BusinessObjects Repository and Build Reports?

Please have a look at these 2 universes from BOB’s download section:


Anita Craig :us: (BOB member since 2002-06-17)

How can I include a Wildcard Character in a wildcard search when I use “Matches Pattern”?

Member DaveO had asked:

Todd Hamrick suggested this:


Anita Craig :us: (BOB member since 2002-06-17)

Why can’t I filter on a Report Aggregate?

Answered by Dave Rathbun:

It’s done that way to prevent an infinite loop. You can filter on the report level, you just can’t filter on an aggregate. That’s because the filters define the aggregate, not the other way around.

Process:

  1. Return data to the cube
  2. Apply filter
  3. Display data
  4. Calculate aggregates

If you tried to apply a filter after displaying the aggregates, you would loop back to step 2 and have to recalculate your aggregate. At which point you would have to loop back to step 2 and apply your filter all over again.

One work-around is to use the “rank as a filter” trick that has been documented many times on the listserv / Bob. Or you can do a sub-query so that only the values you are actually interested in are returned. Or - as you suggest - BusObj could rewrite their report engine to be smart enough to avoid the loop and provide this feature. But that will take a while.


Anita Craig :us: (BOB member since 2002-06-17)

:?: How to show and hide reports based on user selection.
For Example : You have four reports TABULAR, PIE, DETAIL, CROSS TAB and user wants to see only one report in a single tab on its selection.

Solution:
:arrow_forward: 1. Create a Prompt for the user input in universe.
:arrow_forward: 2. Generate your report query and execute it.
:arrow_forward: 3. Create different tables(blocks) in the report to display different reports. Here in your case you want to display 4 reports, so you want 4 table blocks.
:arrow_forward: 4. Create one condition based on the User response for Table 1
:arrow_forward: 5. Add the condition to the Table 1.
:arrow_forward: 6. Repeat Steps 4 & 5 for other report Tables.

  1. If possible create a prompt for this report in universe like below:
=@prompt('Select your report',{'TABULAR','PIE','DETAIL','CROSS TAB'},,MONO,FREE)
  1. Generate your report in DeskI using the above prompt condition

  2. Now, Create your report with 4 different report tables. (As you say: Table 1 displays Tabular, Table 2 displays PIE, Table 3 displays DETAIL, Table 4 displays CROSS tab)

  3. Lets say user selects option ‘TABULAR’, For this you need a condition to show Table 1 and hide other tables. So lets create a condition to show Table 1 and hide others. The syntax would be:

=UserResponse("Data Provider Name", "Prompt Text") = "PIE"  or UserResponse("Data Provider Name", "Prompt Text") = "DETAIL" or UserResponse("Data Provider Name", "Prompt Text") = "CROSS TAB"
  1. To add the above condition on Table 1, Click Format Table → Appearnace → Hide Block → Click Edit Formula and the paste the above formula.

Like wise repeat the same for the other tables.

Hope that helps.


BO_Chief :us: (BOB member since 2004-06-06)