1 2 3 15 Previous Next

SAP BusinessObjects Web Intelligence

225 Posts

Sometime we may get requirement from User to select Free text for column where we don't have 'LOV's available.

Something like in attached screen shot

e.g: Running report for "Last Day" Or "Last Week" on specific Date column




We can't hard code for single selection as report will run only for that selection and re-usability can't be achieved.


Here I am going to explain how we can create custom filter condition at Universe (IDT or UDT) so that custom LOV's can be used to run report with multiple options. This filter condition can be used in report so user will get option to select period for which report should run. Below are steps.


I have used Oracle Database for this example, but same can be replicate with little modification with any Database.


Step1 : Login to IDT/UDT

Step 2: Create Filter With Name "Period Selection" (or give any Name)





Step 3: Write below formula in Filter Definition.

               In this formula you can add as many option you want based on requirement and modify formula. For this example i have used 4 options.


Filter Condition.JPG


Step 4: Validate the formula and you are ready to use in reports. You can create same Filter for all Date columns and use in report based on requirement.

            Once you refresh the report it will ask you for prompt where you can select any value from LOV's and report will run accordingly.  




You can make use this of feature to Schedule same report with different option and deliver to end user based on period.


E.g: You can use "Last Week" to send report on Every Monday with Last week Data.


        You can use "Last Month" to send report on Every 1st of Month with Last Month Data.  etc.


What you have to do is just select according option in Scheduling Parameter option and schedule report.


Sushil Padhye

We recently upgraded our BusinessObjects environment from 4.1 SP02 Patch 2  to 4.1 SP06 Patch 2.


One enhancement I was looking forward to is listed on Page 56 of http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41_whats_new_en.pdf. The content at that link seems to get updated with each support pack so here is the information related to the improved RelativeDate function...

You can now have more date options with the RelativeDate() function in your calculations, including Year, Semester, Quarter, Month, Week, Day and Hour, thanks to an additional period parameter to specify the period to add.


Previously, you could only calculate in days and had to convert every units (week, month, year etc) in days before using the RelativeDate() function.

Unfortunately, it doesn't work. The description of the function does not match the documentation.



I thought it was very odd that the "num" and "period" parameters were separated by a comma (,) rather than a semi-colon (;). I have tried all sorts of variations using the comma as shown, using a semi-colon like everywhere else and guessing what some possible period values might be all with no luck.


This sort of thing troubles me and calls into question SAP's quality and testing standards and practices. There is an SAP note on this stating that documentation on this function is not correct...




However, in my opinion this is more than just a documentation issue. I want to point out that the RelativeDate function still works if you use the RelativeDate(start_date;num_days) syntax. I hope they are able to fix this soon.



UPDATE - Attached is a .wid file , renamed to .txt so I coulkd attach it, with the proposed solution. Is's on BO 4.1 SP5. In order to work, rename it back to .wid



There was a particularly tough question (at least for me) posted by Michal Radomski in WEBI Running Count Distinct with tooke me some time to figure out how to solve it.


The question was how to implement a RunningCount of distinct elements The OP had a list of IDs and dates and he needs to count the distinc elements over time (a date), his data is somethong like this :





resulting in this :



Running Count Distinct




In order to do so,I proposed a solution using the following formula , which I´ll try to explain


[Running Count Distinct]  =RunningSum(Sum(If ( ([Id] ForEach([Id];[date])  In ([Id];[date]) = Previous([Id] ForEach ([Id];[date]) )) In ([Id];[date]);0;1) ) )


The idea behind this is compare , row by row, The [id] element with its previous [id] object  AS IF IT WAS ORDERED BY ID INSTEAD OF DATE , in

case it´s equal  I mark the row with 0 otherwise 1. In this manner, when I sum up the values aggregated by date I get the number of different IDs over time.


As the data is, in fact ordered by [date], I force the comparison to be evaluated as if was ordered by [id] and redefine the context to include [id].

This is accomplished by setting the correct order in the redefinition context ([ID];[date]).


In fact, when redefining context, the order in which the objects are set matters in terms of the final result.


Have ypu all a nice WEEK END.




If you were fortunate enough to attend this years Mastering Business Analytics event (Melbourne, Australia), you may have seen the Web Intelligence demo either at our Icon Integration booth or during my session on Taming the BI Tools Jungle!


The goal was to build an interactive report that demonstrates many of Web Intelligence's key features (Input Controls, Element Linking, Quick Filters, Render as Image/HTML, Hide When.. etc). Since this was a community event, I thought it would be fitting to base our report on community data specifically, SAP's Community Network metadata.


Below is a snapshot and video of the end result.


Earlier this year, we  were involved in an SAPinsider Q & A event with the title "Will Your Web Intelligence Journey  Lead You into Lumira?" The session featured numerous SAP subject  matter experts providing live answers to questions from an online audience. This  format proved to be an excellent means of getting input and insights on key  topics from both the Web Intelligence and Lumira teams.

View the SAPinsider Questions and Answers here.


The unfortunate side to this very positive  SAPinsider event was that, due to time constraints, many good questions were  necessarily given short answers. We decided to follow up with some of the Q  & A participants and explore some of the questions in greater depth.


WebI? Lumira? Both!


Naturally, given the  title of the event, one of threads that ran through the Q & A was the  relationship between Web Intelligence and Lumira, and most particularly whether  Lumira would replace Web Intelligence in the long run. Ty and Frank were most  definitely in agreement here:

    There is a notion with some of our  clients that Lumira will replace WebI in future? How true is that?
    Frank Prabel (from the Web Intelligence Team: Not true. :-) Web Intelligence and Lumira serve different needs.  Web Intelligence addresses the reporting market while Lumira covers the agile  visualization and data discovery markets. We expect both products to continue  to play a leading role on the SAP BI roadmap.
    Ty Miller (from the Lumira Team): Absolutely right on, Frank. Fully agree.

The SAP BI roadmap  lays out a convergence strategy for the SAP BI platform, and as the slide below  makes clear, the key to that convergence is interoperability:



Where Lumira and Web  Intelligence Fit in the SAP BI Convergence Strategy

Source: "Run Simple: Convergence of the SAP  BusinessObjects BI Product Portfolio"


When we followed up  with Gregory Botticchio (from the Web Intelligence team), he made it clear that  Web Intelligence would remain a key part of the convergence strategy:

We no longer talk as  much about Web Intelligence, simply because we are bringing new tools to  market, and they naturally get more marketing attention, but the fact is that  Web Intelligence remains by far our leading BI tool in terms of adoption by  companies and by end users. We continue to invest in the development of Web  Intelligence.

When we followed up  with Adrian Westmoreland (from the Lumira team), he made it clear that the  future would see Web Intelligence and Lumira complementing each other more and  more:

Web Intelligence  being the Swiss army knife of BI tools, it does everything reasonably well.  Lumira will not replace Web Intelligence, but it will evolve to assume some of  the use cases that are not in the Web Intelligence reporting sweet spot. It  will simply be easier for users to adopt Lumira for these use cases than to try  to force them into Web Intelligence workflows not designed to handle them.  Lumira opens up so many possibilities for user interaction that are just not  there for Web Intelligence reports.

You can do many things  with Web Intelligence, and if all you are going to do with Lumira is to create  graphs and charts, you might just as easily use Web Intelligence to build them.  However, Lumira has data preparation, composition and layout capabilities that  go beyond what are available in Web Intelligence, allowing you to accomplish  some of the tasks you would normally perform in Web Intelligence more quickly,  more easily, and with more flexibility.


Web Intelligence remains  the means of presenting your data discovery, exploration and visualizations to  targeted information consumers, while Lumira increases your ability to explore  data and create compelling visualizations. So, to answer the title question  from the SAPinsider Q & A, your Web Intelligence journey may indeed lead  you into Lumira, but your Lumira journey won't lead you out of Web  Intelligence. Moving forward, some of the "sweet spots" may shift, and  the interoperability between the two solutions will be one of the primary  factors in the evolution of your data discovery, exploration, visualization and  presentation workflows.


For those wishing to  explore these topics on their own, the online Lumira tutorials are a good place to start.


In our next post: The Future of Web Intelligence and Lumira  Interoperability.


Extract timezone from the result of built-in function CurrentTime().


Here below formula syntax doesn't work:


Error returned is the following: "The expression or sub-expression at position N in the 'Right' function uses an invalid data type. (IES 10037)"


Work around found consists in encapsulating the CurrentTime() function in an user-defined variable and update syntax as:



MyTime = CurrentTime()

SAP recently shipped SAP BusinessObjects Business Intelligence 4.1 (BI 4.1) Support Pack 6 (SP6). Support packs generally contain only bug fixes, but BI 4.1 SP6 probably represents the greatest introduction of new features since BI 4.0 Feature Pack 3. Many of the new capabilities are for Web Intelligence, such as Free-hand SQL and global input controls.

SAP Marketplace in Webi 400 05.jpg

There's a number of session management improvements, including the ability to cancel a Web Intelligence query.

SAP Marketplace in Webi 400 06.jpg


If you've been playing around with Web Intelligence 4.1 SP6, you may have noticed a new puzzle-piece shaped icon in the bottom left corner.

SAP Marketplace in Webi 400 01.jpg


It's a link to the new SAP Marketplace.

SAP Marketplace in Webi 400 02.jpg

The SAP Marketplace is present in all three editions of Web Intelligence: the HTML edition, the "Rich Web Interface" Java edition and the "Rich Client" desktop edition. The Rich Client is shown below.


SAP Marketplace in Webi 400 10.jpg

Clicking the puzzle-piece shaped icon opens a browser window to the SAP Analytic Extension Marketplace, where you can check out extensions to Web Intelligence as well as other SAP analytics tools such as Design Studio and Lumira.

SAP Marketplace in Webi 400 03.jpg

I think it's fantastic that SAP has created a space where we can see the variety of third-party products that are available to extend our BI landscapes. Clicking on a product takes you to either the vendor's web site or the new SAP Store. An example of the latter is Centigon Solutions' CMaps Extension for Web Intelligence. In addition to Centigon Solutions, there are other Web Intelligence extensions from APOS, BusinessGeografic, Datagaps, Galigeo, and NOAD. Some of these vendors were new names to me, so hopefully they'll benefit from the exposure in the SAP Marketplace.

SAP Marketplace in Webi 400 04.jpg

Not everyone is thrilled about putting the SAP Marketplace in front of users' eyeballs. I was asked to hide the feature on one of my current projects. There isn't an application right to set to "not specified" or "explicitly denied", but the SAP Marketplace is a feature that can be hidden using the Web Intelligence customization feature in the Central Management Console. The procedure is documented in SAP KB 2227110, How can I remove SAP Marketplace icon from Web Intelligence 4.1 SP06?


To begin, navigate to Users and Groups, identify the group that you want to customize, right click and choose Customization. In my example, I'll modify the Everyone group.

SAP Marketplace in Webi 400 08.jpg

From the User Interface Elements tab, open the Status Bar options and clear the checkbox next to SAP Marketplace.

SAP Marketplace in Webi 400 07.jpg

The next time users in that group log in, they'll no longer see the SAP Marketplace icon. Note that the icon is visible when the Web Intelligence Rich Client is launched, but the icon disappears when the user logs into a CMS that customizes the feature be hidden.


SAP Marketplace in Webi 400 09.jpg

I find the Web Intelligence customization feature a little tricky to work with, especially with inheritance from multiple groups. But until SAP adds a corresponding access right to the access levels, this procedure is the way to go. But before you do, be sure to take a look at the interesting ways you can extend the capabilities of Web Intelligence.


Are you enjoying the new features in Web Intelligence BI 4.1 SP6? What do you think of the new SAP Marketplace and SAP Store?

Toy Story 4, the fourth installment of Disney's popular Toy Story franchise, won't hit theaters until June 15, 2018. But with SAP BusinessObjects Business Intelligence 4.2 currently in beta and on schedule to be released in early 2016, it's time to start thinking now about whether there should be a fourth edition of the SAP Press title, SAP BusinessObjects Web Intelligence, the Comprehensive Reference. It's been my pleasure to work with Jim Brogden, Heather Sinkwitz, and Gabe Orthous on the second edition (written for BI 4.0) and third edition (written for BI 4.1) of the book. There are multiple sessions about Web Intelligence at next week's SAP TechEd in Las Vegas and Jim and I will be there to hear first-hand about Web Intelligence 4.2.


SAP Press authors Dallas Marks and Jim Brogden at SAP TechEd Las Vegas 2015


Jim and I would love to hear your feedback. Should there be a new book written for Web Intelligence 4.2? And if you're an owner of one of the previous three editions, what would you like to see changed or improved if a fourth edition is written?


Please share your feedback in the comment section below. And if you're coming to SAP TechEd next week, please say "hello" to Jim and I.

I almost always use alerters for hyperlinks (XIr3.x) instead of entering a hyperlink directly into a cell. Here's my reasons:

1. It's easy to turn them off.

If I'm developing, I might not want the hyperlinks active all of the time, for instance: experimenting with drilling.Using an alerter saves me having to remove the hyperlink from the cell and replace with the object instead.


2. It's easy to make them conditional.

If I want to make my hyperlinks conditional, it's easy to specify this via the alerter, rather than write a condition formula followed by the hyperlink text directly into the cell - the longer the condition, the harder it is to visually parse the text. Moving the condition to the alerter makes the cell cleaner (although it does obfuscate the mechanism, as with any alerter / conditional formatting)


3. Drilling

Using the above point, you can make hyperlinks which only appear at certain levels of your drill hierarchy. Putting this logic directly into a cell is potentially going to make very long formulae:



4. Sorting

Consider this example, a basic E-Fashion query:


I've applied a hyperlink on the [Sales revenue] column to open a child report using [Year] & [Store name] objects. No sorting is applied, it defaults to sorting by [Year] and [Store name]. If I sort descending on the [Sales revenue] column, see what happens:


The default sort is still applied - by [Year] descending, and [Store name] descending; this is because the hyperlink contains both the [Year] and [Store name] objects, hence the sort is based on these. If we remove the [Year] from the hyperlink, when we sort on the [Sales revenue] column we get this:


The sort is now only considering the [Store name] as that's all that's left in the hyperlink. Obviously this is not ideal. If we remove the hyperlink from the [Store name] object and use it via an alerter instead:



..then when we sort based on our [Sales revenue] column we get the desired result:


Hopefully this gives some good reasons why you might consider applying your hyperlinks via alerters or conditional formatting instead of directly to the report canvas . Maybe you have some good reasons for doing so, or not doing so?


Caveat: Based on XI R3.x java client; functionality may be different in 4.x.

Hello everyone,

I´m sorry if you all are aware of it, but I think it deserves some attention :



This is my query over e-fashion





What I´ll do here is calculating  the unitary price for a product in a Line in the dumbiest way :

Just dividing [Sales revenue] by [Quantity sold] for each [Year]. This gives me the following table







So far so good ...

But what if I took [Year] away from my table ?

Here´s what happens


report def calc.png


As you can see, the values are different but why this happens ?

BO adds another rule of precedence when calculating values, besides the * / + - precedence , there is the default aggregation of the measure that is with greater precedence.

So, when calculating =[Sales revenue]/[Quantity sold], what is really been calculated is =Sum([Sales revenue])/Sum([Quantity sold]).


In order to avoid this problem, you better add back the missing dimmension (in this case, [Year]) through the use of the ForEach operator


=Sum( ([Sales revenue]/[Quantity sold]) ForEach ([Year]))/3. Like in the table below


report full.png





So when dealing with multiplication/division of Measures, always remember  to use ForEach operator.



Hi ,



I want to calculate measures between days ex(1-30 days,31-60days, 61-90days like...) in webi report  for user given date.


now i am  using relative position to implementing above requirement but not sure it ll give right data . if you have any other logic please provide solution.


1-30days  Name called  V1

=RelativeDate([vUser Date];-30)


31-60days   V2


1- 30 days measure calculation


=[Hours Worked] Where ([Pay Period Ending Date]<=[vUser Date] And [Pay Period Ending Date]>[V1])


31- 60 days measure calculation

=[Hours Worked] Where ([Pay Period Ending Date]<=[vUser Date] And [Pay Period Ending Date]>[V2])


S Suryateja.

   Hi Everyone,


I would like o share two extra approaches to make use of persistent comments into a WEBI doc. I´m saying two extra ways because this subject has already been  discussed by WILLIAM MARCY  inEnabling Comments in Web Intelligence Reports using Input Controls


The first approach works only in design mode, the second ,albeit working on reading mode, can be better explored in design mode.


1) First approach - Use of blank cells.

    This approach is based in the use of blank cells and consists , simply in dropping blank cells at the places where you want the comments to be in the report. One can simply double click on the cell to edit its content. And, if the person wants the comment to have multilines, just edit the content using the formula editor.


2) The second approach makes use of HTML5 and Javascript, and saves the comments to a Database to further access. In this particular example, I´m using MongoDB (www.mongodb.com) as a dataprovider and Mongolab (www.mongolab.com) as a DaaS provider. I´m not sure about it, but I think that you can use SAP Hana as the dataprovider (to be confirmed), anyway, any Database that has a Javascript driver will do.


The reason why I choose MongoDB are two folded :

a- Flexibility - As a key-value NoSQL database, MongoDB can hold any kind of data in a very interesting way. You can literaly save a variable to Mongo without any e3ffort

b - Ease of use - Mongolab has a Rest API to perform Database operations, in this sample I´m using a Rest call to write to Mongo and a second one to write. Besides, mongolabs provids a free plan (the one used).


I used a query over e-fashion that returns [Lines]. My report consists on a vertical table with two columns :

- [Lines] and

- [comments] , a measure variable with the following formula :

        ="<div><textarea id='"+[Lines]+"'></textarea></div>" and its "Read as" property to HTML.


For those unfamiliar with HTML, variable will generate, for each [Lines], a small textbox, where the user will enter its comments.

There are three blank cells that holds the HTML for the click buttons and some Javascript code to read and write from Mongolabs.

The script are :

1 - Save button  - <input type = "button" value="Save Comments" onclick="saveComments()"

2 - read button - <input type = "button" value="Read Comments" onclick="readComments()"

3 - Script to read and write from the database




// array to hold the documments retrieved from the Database

var arrCom =[];


// the structure of the documment to be saved

function Comment ()










// function to save the comments to Mongo

function saveComments() {

var txt=""

// comments will be an array with the textarea elements

var comments=document.getElementsByTagName("textarea");


// loops through the array , creating a new document, and adding it to the array filling its values, and pushing it to the array

for (i=0;i<comments.length;++i) {

   var   com = new Comment();






var enq="";



//Rest call to save the comments to the Database

     var invocation = new XMLHttpRequest();

     var url = 'https://api.mongolab.com/api/1/databases/bobj/collections/bobj?m=true&apiKey=IZzAl7-Sd8i9SZMJ25lpSSsT_45xNNdW'

     invocation.open('PUT', url, false);


   invocation.setRequestHeader('X-PINGARUNER', 'pingpong');








// function to read the comments from the database

function readComments() {

var arrComments= [];

var invocation = new XMLHttpRequest();

// Rest call to read the comments into arrComments, an array

var url = 'https://api.mongolab.com/api/1/databases/bobj/collections/bobj?apiKey=IZzAl7-Sd8i9SZMJ25lpSSsT_45xNNdW'

     invocation.open('GET', url, false);


   invocation.setRequestHeader('X-PINGARUNER', 'pingpong');




   arrComments= JSON.parse(invocation.responseText)

  // loops through the array and, for each element, finds its correpondent textarea assigning its value

   for (i=0;i<arrComments.length;++i) {






Notice that :

1 - This sample was elaborated as a proof of concept, swo is lacking a lot of funcionality;

2 - The Rest API, in the way is used in this sample, isn´t a good implementation


I´m attaching a wid file to the post with this example. It has a txt extension so it can be uploade. It´s on 4.1 SP6, To use it, first change its extension from txt to wid, open it with WEBI Rich Client and exports it to BI Launch PAD,


cokments will be highly appreciated



Logical following:

Model A there are dimnesions: Manufacturing plant, ProductS ID, Amount ,  but there is no  Material classification

Model B there are dimnesions: ProductS ID,  Material classification .

The report need dimnesions: Manufacturing plant;ProductS ID;Material classification; Amount


I like use Excel formula  "vlookup" ,but the WEBI not.

The following steps realize the referenced in the WEBI report similar to Excel  formula "vlookup" :


BO 4.x webi universe


step1: creat report with two querys model A AND model B


step2: merge two querys dimensions  ProductS ID name  ID


step3: creat variable name <Class> (You defined the Name )

qualification :Detail

associated dimension : ID (step2  dimnesion )

formula : =[Material classification]  (your require  dimension which  from other query )


New dimension <ID> is ok.


The approach to solve the problem of the bothers me more than a year.

Hope can help you.



In China and English.for  the people with poor reading English like me in mainland China







模型a,有字段是 工厂 产品ID,数量

模型b,有字段是 产品ID 产品分类

报表需要的字段 是 工厂 产品ID 产品分类 数量




以下步骤实现类似EXCEL的 vlookup引用的功能:


系统环境 BO 4.x webi universe




步骤2:合并二个模型的 产品ID 字段,名称是 ID


步骤3:创建变量 Class(可以自己命名的)

分别选项是 Detail,ID(步骤二合并后的字段),

公式输入:=[Material classification] 需要使用的任一字段


新的 字段 ID 可以使用了,







Hello everyone,


I've seen few people requesting for a formula that shows the level of hierarchy we're currently on while drilling (down/up) and/or depending on that number, they can do some other actions.

Ex: We have a hierarchy State -> City -> Store Name, the Level (formula) should show 1 when we're on State (before drillingh), 2 when we drill down to City and 3 when we drill down to Store Name. Lets create a simple formula to achieve that.

BO 4.1 SP4 used for this walk through:


1. Create a sample Webi report using eFashion universe with just Year and Sales Revenue


2. Create a variable as below

Current Level of Hierarchy =Concatenation("Level ";If(DrillFilters([State]))="" Then 1 Else Length(DrillFilters())-Length(Replace(DrillFilters();"-";""))+2)


3. Drop that variable in a Blank Cell, the variable shows Level 1 before we drill on State.

Snap 01.PNG


4. Enable Drilling and drill on the state Texas, the formula shows Level 2.

Snap 02.PNG


5. Drill on the city Houston, the formula shows Level 3.

Snap 03.PNG


How the formula works is, it shows Level 1 when we don't have drilled on State and when we start drilling, it calculates the number of "-" in the result of DrillFilters() function and adds 1 to it.


Hope that helps someone, someday

Mahboob Mohammed

Hello everyone,


Did anyone notice the secret custom format (for Date) available/that works in Webi report?


Apparently, we can easily format the Dates to show Quarters as Q1/Q2/Q3/Q4, I just discovered it for myself. I don't know how many people knew about it or since which version is this available or is not a secret anymore, I'm using BO 4.1 SP5.


Step 1:

In a sample report, add Date column twice in a table.

Step 0.PNG

Step 2:

Right click on the 2nd column -> select Format Number -> in Format Number window, select Date/Time -> and from Sample select any one (screenshot has Sep 21, 2004 selected) -> click Custom

Step 1.png

Step 3:

Update the Custom Format


From (Mmm d', 'yyyy) (or whatever else)

Step 2.png

To (qq yyyy) as shown below and click Add to close this window and OK on the Format Number window to close it.

Step 3.png

Voila, we unlocked the secret custom format. Now the reports looks like below:

Step 4.png

Update to this post as of 10/02/2015 8:53 am (EST)

I just realized that this custom format (for Quarters) has been added in Information Design Tool's feature where we Edit Display Format of an object. Check it out, its great and very helpful.


Update to this post as of 10/14/2015 4:12 pm (EST)

This can be done only in Java Applet mode (and Webi Rich Client) as most of you already know that HTML mode doesn't let us create Custom Formats.



Mahboob Mohammed


Filter Blog

By author:
By date:
By tag: