1 2 3 15 Previous Next

SAP BusinessObjects Web Intelligence

212 Posts

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.



Mahboob Mohammed

Hello everyone,


Here are couple of Webi tricks that are so easy to implement and much easier to forget about, when developing a report and spend hours of work later fixing/redoing/testing them over and over. I've learnt this the hard way by spending a few additional hours whenever there was an update request for such reports.


Multiple variables to address custom sort reset issues:

Let's say, we've a variable v_XYZ that we created in the report, used it to display data in a table/crosstab and custom sorted on. Everything is perfect in this report until, there is a request to update the logic in v_XYZ by adding/removing conditions which may result in increase/decrease of the LOVs. That update will mess up custom sort in the report and you'll have to go back and custom sort again, we feel miserable if we have more than 15 or 20 LOVs in that variable. If that's not enough, what if that variable’s logic changes frequently! We wouldn’t want to imagine that. The solution is to create 2 variables, 1st is "v_XYX_logic" which has the actual logic with If Then Else statements, and 2nd is "v_XYZ" that is just referring to the logic variable we created earlier as "=[v_XYZ_logic]". Now, we use the variable v_XYZ to display data in table/crosstab etc, and also to custom sort on. Voila, the custom sort won't reset now, no matter how many times you update the logic in v_XYZ_logic variable.

Dummy (label) objects in the universe to use in combined queries:

I've worked on quite a number of reports which were created using multiple data providers (say 3) and those data providers had (say 4) combined queries each. In my case, each combined query in a data provider had measures that would call different context (subject area), even though most of the dimension objects were same and there was no flag objects that helped me to differentiate between the data, if its from combined query 1/2/3/4. This was great, until a user said that the numbers were not correct and I had to trouble shoot and find which combined query is the culprit. To trouble shoot the issue I had to duplicate this report 12 (3*4) times and create as many Test reports. Each Test report would have only one combined query from one data provider (as shown below) and then, I would try to find the root cause.

Abbreviations used:

DP = Data Provider

CQ = Combined Query

TR = Test Report

  1. DP 1
    1. CQ 1
    2. CQ 2
    3. CQ 3
    4. CQ 4
  2. DP 2
    1. CQ 1
    2. CQ 2
    3. CQ 3
    4. CQ 4
  3. DP 3
    1. CQ 1
    2. CQ 2
    3. CQ 3
    4. CQ 4

Test Reports creation:

  1. TR 1 was created using the combination CQ 1 from DP 1
  2. TR 2 was created using the combination CQ 2 from DP 1
  3. TR 3 was created using the combination CQ 3 from DP 1
  4. TR 4 was created using the combination CQ 4 from DP 1
  5. TR 5 was created using the combination CQ 1 from DP 2

so on till TR 12.

Relax, the way to get rid of this issue is create dummy (label) objects in the universe and add one each in the combined queries. I created 4 dummy objects as below:


NoObject NameDefinitionAdd to
1Combined Query'Combined Query 1'CQ 1 (in all the data providers)
2Combined Query 2'Combined Query 2'CQ 2 (in all the data providers)
3Combined Query 3'Combined Query 3'CQ 3 (in all the data providers)
4Combined Query 4'Combined Query 4'CQ 4 (in all the data providers)

Make sure that the data type of all the objects is the same (string), they won't parse, but will work fine. Notice the name of first object is "Combined Query" and not "Combined Query 1," I did that on purpose so that name of the object resulting from this query will be "Combined Query." Now, add one object each in the combined queries as I said in the "Add to" column.

Voila, now can filter the data using Combined Query object, so, testing is a whole lot easier. You can also use this object in the logic if it simplifies things (I did, it helped me a lot in some crazy logic.)

Hope this helps people save many hours of work! Any feedback to improve this post is appreciated.


Mahboob Mohammed



I´m writting this post as a extension of How to set a WEBI document to autorefresh each n seconds

One of the questions raised was if I knew how to dinamically jump from one report to another programatically.

Digging a little, I found out a solution.

It´s a simple script. So, here it is

I´m assuming that the document in which you´re setting the script has 3 or more reports.

1 - Drop a blank cell in your main webi report(the one from which your´re jumping to the other);

2 - Set its value to


<input type = "button"  value ="Go to Report2" onclick="gotoReport(1)">



<input type = "button"  value ="Go to Report3" onclick="gotoReport(2)">


function gotoReport(n) {





3 - Set the cell Reading as property to HTML


Your document will render like this :


mudar report.png



Attached is a sample WEBI file (.wid), in which I demonstrate the script. It´s on WEBI 4.1 SP5. The extension was renammed to .txt so I can post it here.




The problem


As with crosstabs in XI3.1, so with cross tables in BO4 (note to SAP: why, oh why do you keep changing the names of things?). It is not possible to create a centered heading as part of a cross table using the format cells/table option.


The solution


The solution is simple, once you know it. Say you had a crosstab of sales, within a product group by region matrix. You may want to add a table heading above your column headings that spans them all. Instructions are below in italics with explanations in plain text around them.


So, you start off by adding a row above the column headings:

Right-click on the headings row and choose Insert -> Insert Row above


Now add ="Sales by Region/Product Group" in the first cell. You'll see that it will add it to every column heading cell but that you cannot merge these cells.

So, what next?

Firstly, turn the formula above into a dimension variable:

Click on the Create Variable icon at the left of the formula bar, give it a suitable name and set its qualification to Dimension.


Now's the key part.

Right-click on the object in the cross table heading and choose Break -> Add Break


And for the final stage:

Right-click on this object again and choose Break -> Manage Breaks. Within the Manage Breaks dialog box, clear the tick on Break Footer and change the Duplicate Values behaviour to Merge

You now have a centered header above your column headings.


Thanks for reading, happy to answer any questions.


is it possible to disable the Data Preview Panel in Query Panel by default?

Jason Everly

The WebI Social Hour!

Posted by Jason Everly Sep 18, 2015

Hello everyone!


The WebI Social Hour is a new initiative that the Web Intelligence team is piloting. Its intent is to gather customers together and allow WebI engineers and Product owners to answer general customer questions and concerns, allow customers to discuss issues amongst themselves, and discuss the hot issues of the day pertaining to Web Intelligence. Specific technical questions, existing incident issues, and the like are not in scope for this chat.


It will be in a chat only format with the resulting questions and answers archived for future reference.


The first meeting will be held on October 15, 2015 at 11:30 AM Eastern.


You can join the chat here:


WebI Social Hour Chat


The resulting questions and answers will be stored on our Social Hour Jam page located here:


WebI Social Hour Jam


Note that the Jam Page is invite only so please email jason.everly@sap.com for an invite.

Be sure to include WebI Social Hour in the title.


Thanks and see you there!!



SAP released SAP Business Objects Business Intelligence 4.1 Support Pack 6  and It has some cool new features. I have outlined the new variant feature for webi below.. The variant feature allows users to save frequently used prompts on a web intelligence report as variants.

Prompt selection box opens up with the “Available prompt variants” option at the top1..jpg

Enter your selection criteria and click on the “create prompt variant button” (Disc button with the green plus sign)



In the dialog box that appears, enter a name for the variant you are trying to create and click OK. The variant has been created


To use the created variant, when you open a new report, on the prompts screen, click the drop down arrow on the available prompts variant box and select the variant you created.


The prompt become prefilled with your selection...


You can then hit OK and run the report.

You can also alter the values in any of the prompts and hit the save button (Disc without the green + sign) and it would save your new values. You can also hit the X sign to delete a variant you no longer need.



Certain Webi query panel features are not available when using BICS connection however these features are available  with relational Universe.

These are I have tested in BI 4.1 SP02.


Query filters on BEx Navigation attributesQuery FiltersAdd filter condition in BEx query or add filter at report level
Webi query filters on Measure ObjectQuery FilterAdd filter condition in BEx query or add filter at report level
OR condition between the promptsQuery FilterAdd filters at report level and add OR condition.
Matches Pattern in PromptQuery FilterAdd filters at report level
Object from this query in PromptQuery FilterCreate variables at report level.
Result from another query in PromptQuery FilterCreate multiple queries and restrict the data at report level
IS Null in PromptQuery FilterAdd filter condition in BEx query or add filter at report level
Not Null in PromptQuery FilterAdd filter condition in BEx query or add filter at report level
Retrieve duplicates RowsQuery Properties
Add a Combined QueryQuery PanelCreate multiple queries and use merged dimension.
Both in PromptQuery Filter
Except in PromptQuery Filter






Software used:

SAP BO V 4.1 SP2

Universe : eFashion.


I have shown the way we can aggregate the values on report level.


I have dragged State & City objects


My initial report looks like below:

First report.png


Now I want to aggregate city names as per state.


So I want result as shown below:

Final report.png



To achieve the result follow the steps:


1) First drag State & City objects on report and sort result by State and City in ascending order.

2) Create dimension like

Cities =[City]+";"+Previous(Self;([State]))

3)Create measure like


4) Create another measure to calculate rank


Ranking =Rank([length_cities];[City];([State]))


5) Apply report level filter using newly created measure Ranking


where Ranking = 1


6) Create another dimension


Final_Cities  =NoFilter([Cities])


7) Drag this Final_Cities to report & then hide the column City.


Check your report you got your desired result.


I hope this will help while developing reports.




I know it's a strange question, one which I suspect not many of you have asked.


There is a way to answer this question though


Download the free Audacity audio editor.


From the File menu select Import>Raw Data:


Choose a file:


Select 'U-Law' as the Encoding:


Your setting should look like this:


Channels may be mono or stereo - doesn't really matter much. Finally click on Import and have a listen to your file. This will work with pretty much anything you throw at it - .wid, .exe, .unv, etc. For instance, here is the waveform for the E-Fashion universe:


Sadly .mp3 and .wav attachments are not allowed here, so you'll have to try for yourselves . The results are rarely musical but are nonetheless often quite interesting.

Hi All,


In this blog post I´ll show how one can take n measure names and turn them into values of a dimension.


The main reason behind this post was this thread , here at SCNhow to create stack chart in webi ? . In which was asked how to create a stacked bar chart when you have 1 dimension and 4 measures.


In order to create a stacked bar chart, as explained in the thread, you need 2 dimensions and 1 measure.


Let me give you a real sample.


Query e-fashion for State, Sales Revenue, Margin and Quantity Sold. Then let´s create a stacked bar chart showing all objects on it. You can´t  do it because you got just one dimension and three measures.

But what if you could create a second dimension object, [type], with values : "Sales revenue", "Quantity Sold" and "Margin" and a measure variable, [Value], which value would be :

-- [Sales revenue] for [type] = "Sales revenue",

-- [Margin] for [type] = "Margin" and

-- [Quantity Sold] for [type] = "Quantity Sold"


The challenge here is to create the [type] dimension.

For this case what I did was :

1 - Brought another dimension in my original query, one that has 3 values that exists for all values of the first dimension, for this, I used [Year];

2 - Create a dimension variable , [type] = If ([Year]="2004";"Margin";If([Year]="2005";"Quantity Sold";"Sales Revenue"));

3 - Create a measure variable [Value] = If ([type]="Margin";Sum([Margin] In ([State])) ForAll ([Year]);If ([type]="Quantity Sold";Sum([Quantity sold] In ([State])) ForAll ([Year]);Sum([Sales revenue]In ([State])) ForAll ([Year]))),


4 Create the stacked bar chart using [State], [type] and [Value].





First of all, I want to thank SAP team for listening to its customers and making possible the so long-awaited feature of Dynamic Default Values for Universe Prompts.


I've just tested it from SAP Business Intelligence 4.1 SP6 and let me tell you, this new feature works like a charm.


Let´s see how it works.



1. Suppose you have a report with the following query and you want it to automatically bring yesterday's date as first parameter and tomorrow's date as last parameter:




You could transform the date values in standard prompts but you'll have to type the dates manually.


Or you could transform them in the new dynamic prompts.



2. To achieve this, open Information Design Tool, open your universe and go to Parameters options.


Create the following parameters: MyStartDate and MyEndDate



2.1 Configure MyStartDate:




As you can see we have a formula like CurrentDate()-1 in Default Values area.  We were able to insert this formula because when you press the button


ScreenShot003.jpg a new Formula Editor window is displayed. This Formula Editor comes with a wide range of predefined functions that certainly will help you save a lot of time creating formulas.




Press Ok. Then, uncheck "Keep Last values" checkbox, because we don't want the same static value every single time, we want it to change when we refresh our report.




Apply following settings to the other parameter



Create your formula with the formula editor and uncheck this option:





5.Save and publish universe


6.Open your webi report


7. Select Prompt:



Select following option to configure our first prompt:



Select "Parameter from universe" option, then choose "MyStartDate".






8. Repeat step 7 for the second prompt, just that you have to choose "MyEndDate".





Then our query will look like this:





10. Execute. You will see that by default Parameter 1 brings yesterday, and Parameter 2 bring tomorrow's date, without losing the ability of introducing other dates if you want to do that.




11. Save your report.

This is it. Now you are ready to Schedule your report.

With the right formula you will be able to say goodbye to that boring situation when you have to change the dates manually every single month.

UPDATED in July 15


Hi there,


Today I saw a interfesting question posted on BOBJ [Webi 4.x] Find a character in the string.

The OP questioned on how one can get the numeric value (characters from 0 to 9) that lies inside a text field .

For instance , the value of the field [Address Field] is "Houston77047TX" and "Zipcode" variable should report "77047" . You can deal, as well, with formatted numbers (like 12,345.78) as well as dates (like 07/15/2015) assuming that "." , "," and "/" only appears in the Number/Date to be extracted.


So, how to accomplish it ?


I came  up with one solution that consists on :

1 - Replace all numeric characters with another character, for instance "?" , so the previous [Address Filed] will become [Replaced] = "Houston?????TX"

2 - Find the position of the first ocurrence of "?" inside [Replaced]

3 - Find the position of the last ocurrence of "?" inside [Replaced]

4 - Extract [Number] from [Address Field] given the [Start] and [End] position of [Number] (given in steps 1 and 2.



To accomplish that, I create 4 variables,

[Replaced] =Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([var];"0";"?");"1";"?");"2";"?");"3";"?");"4";"?");"5";"?");"6";"?");"7";"?");"8";"?");"9";"?");",";"?");".";"?");"/";"?")


[Start] = Pos([Replaced;"?")


[End] =If(Substr([Replaced];[Start]+1;1)="?";If(Substr([Replaced];[Start]+2;1)="?";If(Substr([Replaced];[Start]+3;1)="?";If(Substr([Replaced];[Start]+4;1)="?";If(Substr([Replaced];[Start]+5;1)="?";If(Substr([Replaced];[Start]+6;1)="?";If(Substr([Replaced];[Start]+7;1)="?";If(Substr([Replaced];[Start]+8;1)="?";If(Substr([Replaced];[Start]+9;1)="?";[Start]+9;[Start]+9);[Start]+8);[Start]+7);[Start]+6);[Start]+5);[Start]+4);[Start]+3);[Start]+2);[Start]+1)-1


For [End], I assumed that the maximum size of the number would be 9 characters long (from 0 to 999999999)  but the size is expandable, you can set the maximum number to whatever you want.


And, finally

[Number] = =Substr([Address Field];[Start];[End]- [Start]+1).


The idea behind these variables is the following :


[Replaced] : Search [Address Field] and recursevely change the characters from 0 to 9 with ?

[End] Trhough the [Start] position of "?" inside [Replaced], I test the [Start] +1 searching for "?", if it´s found, I search at [Start] + 2 and so on, until [Start]+9. If "?" is not found at [Start]+n, returns [Start]+n.


I will post the wid sample of it later.




Hi All,


I've seen many cases with displaying non-latin characters in Web Intelligence or other reporting tools.

It's not always so easy to identify where is the issue coming from, so I'd like to help you with an easy testing method.

Prerequisites for this test is to make sure that the your database is configured to UTF-8 and you can also display non-latin characters using other tools.

  1. Please make sure that URIEncoding is set to UTF8
    in Tomcat’s server.xml file.

    You can validate this by following the steps written in KBA 1497582.
  2. Open Notepad++ and insert the following code
    which gives back the content of a form using the GET method:

    <%@ page contentType="text/html; charset=UTF-8"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <title>Character encoding test page</title>
    <p>Data posted to this form was:




    <form method="GET" action="test1.jsp">
    <input type="text" name="mydata">
    <input type="submit" value="Submit"/>
    <input type="reset" value="Reset"/>

  3. Save the file as "test1.jsp"

  4. Navigate to Tomcat's ROOT directory

  5. Copy "test1.jsp" into this folder
  6. Open "test1.jsp" using IE. In my example the URL is: http://localhost:8080/test1.jsp

  7. Type in some non-latin characters, e.g. Chinese letters (形声字 / 形聲字)

  8. After clicking on the Submit button, you should get back the results.

    Note: In the current example Tomcat's UTF-8 configuration seems to be correct.
  9. If you remove URIEncoding="UTF-8" parameter from server.xml you should get something like this:

  10. Another possible output of a wrong configuration:



Please note that this is not a fail-safe method to make sure that the configuration is correct/incorrect, but it might help you to narrow down the issue.



I hope it will be useful for you.





Filter Blog

By author:
By date:
By tag: