Archive for the ‘Business objects Web Intelligence’ Category

Hide a column in Web Intelligence

February 23, 2012 1 comment

How to hide a column in WebI

In this post I would like share some information on how to hide columns using alternate and manual methods. The illustrative example report is created from Efashion sample universe in BOXI 3.1.

Let us take the following example,

Here we have a column named “promotion (y/n)” which might not give more information regarding the promotion, so we intend to hide this temporarily until the client/customer asks for it.

Now we are going to hide this column from the block using alerter option from Web Intelligence, following screenshot shows exact report layout before hiding any columns


Steps to implement the alerter:

1. Click the alerter option in the reporting toolbar, a new window will open as shown in the sample screenshot below,


2. In Sub – Alerter formula, provide some formula that is always true like the one shown below,

= [Promotion Cost USD] = [Promotion Cost USD]

= 1 = 1

Similarly you can come up with many formulas that returns true.

3. Now go to format option and format everything to match you Web Intelligence background like cell content, border and background colors as shown below in the following screenshot.


4. Now select the column that you wanted to hide and activate the alerter,


5. Go to alerters at reporting toolbar and enable it, then the selected column will be hided from the layout.


Hiding completed??


Did we notice the column header in above screenshot, why we missed to hide the column header?

In WebI, both are considered as separated layout since column headers are differently formatted from column values fields.

6. Now select the column header and again apply the alerter to hide it.


7. The same hiding process can be done by simply formatting the column to match the background color of WebI and resizing the column width.

8. This manual process and alerter based hiding will work only at the display level meaning when the report is exported as excel, we will be seeing the hidden fields. However PDF does the job, it won’t display the column that is hidden in WebI.


Frequently used Functions and Formula

Frequently used Functions and Formula

To remove the commas and currency symbols in the measure values,

=FormatNumber([Sales revenue];"#")

To get the measure values in decimal notations,

=FormatNumber([Sales revenue];"#,##.00")

To get the beginning and ending [year] values from a list of year values inside the reports

=Min([Year])+" to " +Max([Year]) In Report

=First([Year])+" to " +Last([Year]) In Report

To get the prompt names from the query,

=UserResponse(“Prompt Names:”)

To show "ALL" values in label , when selecting no values in Simple Filter. For example take year as an Simple report filter and there are values like 2002,2004,2006 … and "All Year". To bring these values in label , we can use two functions,



Both the functions works the same way when selecting ALL Year from List of Values shown in Simple Filter

IT will show like 2002;2004;2006 in the label, which becomes complex when more and more year values added in the tables. So we can show just "All Year" as label.

To show "All Year" as label, we can modify the functions as shown below,

=If DrillFilters([Year])<>"" Then DrillFilters([Year]) Else "All Year"

To get the report refreshed time and date in a cell, use the formula below.

="Last Refresh Date: "+FormatDate(LastExecutionDate();"dd/MM/yyyy") +" , "+LastExecutionTime()

Rolling Text in Report Title

Rolling Text in Report Title

Using HTML Scripts inside the reports for label and other format makes the report more dynamic from its interactiveness

For Example we can show a rolling text as an report title, We must ensure the following steps should be done so that html scripts can work in infoview ,

For a particular user account in BO, there are several preferences in using the infoview.

1. Logon to the Infoview,

2. There will be an option named "Preferences" at the top right of the window,click that option

3. you can find different sections, like general, desktop intelligence, Web intelligence etc..

4. Under the Web Intelligence Section,

Select a default view format: choose "Interactive"

Select a default creation/editing tool: "Interactive"

5. Click "OK" and save the preferences.

Then go to the report where you want to create a rolling text report title,

Create a Blank cell and create the HTML script,

Marquee tag is used for rolling the text form end to end in HTML,

for example, the sample script shown below is used for rolling the report title,

<Marquee> Report Title </Marquee>

After completing the script, select the cell and hit the properties tab in Web Intelligence Rich Client,


under Display options , read the content as "HTML" and finally export the report to the CMS, Now the rolling text can be viewed in infoview.

The same can be done from Infoview directly with corresponding Edit permissions,

select the cell, right click and select format and then cell


read the content as "HTML",


Click OK and rolling text report title is done, similarly we can do as many HTML scripts in the report and make it interactive.

Hyperlink to open mulitple Web Intelligence documents by Open document functionality

Open document syntax to open multiple reports in single hyperlink

we can link two or more reports in a single hyperlink, this can be achieved by injecting an javascript inside the open document URL.

let me first tell some basics of Open document functionality which is as follows,

The syntax for an OpenDocument URL is as follows:


The exact syntax of the <platformSpecific> parameter depends on your
SAP BusinessObjects Enterprise deployment:

• For Java deployments, use openDocument.jsp in place of the <platformSpecific> parameter.
• For .NET deployments , use opendocument.aspx in place of the <platformSpecific> parameter.

<parameter1><parameter2> can be the values that you are going to pass the next report.

we can briefly get a  knowledge on open document from the following link.

When we create an hyperlink from infoview by options, we will get a  “<a href >/…/…/ </a>” link , and its slightly different from this procedure of creating a hyperlink. Here we replace the auto generated link script by the open document syntax with the javascript.

Normal link to open a SINGLE document:

=<a href=’http://<servername&gt;:<port>/OpenDocument/opendoc/<platform
Specific>?<parameter1>&<parameter2>&…&<parameterN’>“+”Your Object or text here”+”</a>”

The javascript to open two documents,

=”<a href=’#’ onclick=\”‘Open document for Report 1’);‘Open document for Report 2’)\”>”+”Your Object or text here”+”</a>”

For example, i have implemented this using sample universe called Efashion, where i created three report, the first one is the main Report in which hyperlink is created and second, third reports are the connected reports,

the opendocument syntax for connecting two reports is shown below, we can also pass different set of parameters to different reports using this link.

=”<a href=’#’ onclick=\”‘http://<server name>:<port>/OpenDocument/opendoc/openDocument.jsp?iDocID=Afkjn4VJ.fqpsHqcigddiP5iOlwE&sIDType=CUID&sType=wid&sRefresh=N&lsSEnterYear%3A=”+[Year]+”‘);‘http://<server name>:<port>/OpenDocument/opendoc/openDocument.jsp?iDocID=AsdLLgIffJn7fXJCv85q2wssVZlOo&sIDType=CUID&lsSEnterYear%3A=”+[Year]+”&lsSEnterQuarter%3A=”+[Quarter]+”‘)\”>”+[Year]+”</a>”

Another Best Practice about creating prompts, especially while creating hyperlinks is,

when we create hyperlink sometimes there will be a need to pass parameters as a prompt to the next report, its better to create the prompt name without any spaces as like in above link (EnterYear and EnterQuarter)

you can see there are no spaces between ‘Enter’ and ‘Year’, this will reduce the risk on %20 and some notations for spaces on link because we are manually writing the open document syntax, so it will become more tedious when we try to add %3A’s and %20’s for spaces. so its better to avoid spaces in prompt names.

Now if we click on the object, it will open two Reports at a time with different or same parameters passed.

we can directly enter the open document syntax in the formula bar as shown in above screen shot.

Using this javascript, we can also add other mouse events like onclick(),ondblclick(),onmouseover() and functions like prompt(),alert() etc





1.      Create two reports in Web Intelligence and export both to the Central Management Server. Since the object has to be passed to the second report, create the second report with user prompts which is going to get the objects that are sent from the first report.


Fig1.1 Hyperlink1.WID (First Report)



Fig1.2 Hyperlink2.WID (Second Report)

The First report (Hyperlink1.WID) shows the sales revenue and quantity sold for all states and second report (HyperLink2.WID) shows the sales revenue detail for the particular selected state achieved using Query Prompts.

2.      To create Hyperlink in first report, open Business Objects Enterprise Java Infoview and open the WebIntelligence document (Hyperlink1.WID). Select the Object that is to be linked with the second report then RIGHT CLICK –> Hyperlink –> New…

Fig2.1 Creating Hyperlink

3.      A new window will open for creating hyperlink as shown below, select Link to document tab


Fig3.1 Create Hyperlink Window

4.      Click Browse, select the second report (Hyperlink2.WID) and select ok.


Fig4.1 Selecting Document

5.      Select Refresh on open option from Hyperlink properties in Create Hyperlink Window and select the options as in following screenshots below.


Fig6.1 options


Fig6.1a Options

6.      Then the report will look like what is shown below, as hyperlinks for the object that is selected.


7.      Click the link from Infoview, which leads to second report after a confirmation from default web browser. Click Ok.


8.      For instance, when Texas link is clicked it will go the second report, showing the state wise sales details of efashion for Texas alone.


Rolling calculations in web intelligence by row wise

In webi  we can calculate the rolling sum by using two functions as per my knowledge,

In the following example, i have used efashion  sample universe from Business Objects

They are RunningSum() and Previous(self) functions

while using RunningSum() function we have three arguments in this, which is RunningSum([Measure];Row|Col;([Reset Dimension]))

here we can control the flow of calculation by row using second argument (Row|Col)

=RunningSum([Sales revenue];Row;([Year]))


the alternate way of doing the same is by using Previous(self) function


=[Sales revenue]+Previous(Self)


there also should be some other way to achieve this one using formula.

if you find any,  share it here >….

%d bloggers like this: