1 2 3 12 Previous Next

SAP BusinessObjects Web Intelligence

178 Posts

Hi All,


In this blog post Getting to know about the Context Operators (IN, ForEach and ForAll). First let me explain on this; IN, ForEach and ForAll these three operators are called Context operators. Do you know, why today i would like to share my knowledge about this topic because context operators mostly used in webi report.


Even you know by using (IN, ForEach and ForAll) operators; you just like Kill Two words with one stone, in other words to solve two problems at one time with a single action.


Have you ever noticed that IN, ForEach and ForAll these three webi functions like poweful keywords which most/ mostly serachable in Google search engine. Also in this blog post i covered all aspects about context operators; when any why required at the time of creating webi report.


I explain on these three operators below using an example:

In context operator - when / why we use?

For to me using In context operator to specifies dimensions explicitly in a context.

Example:- you have a report showing Year and Sales Revenue, but our requiremnent is to add one more column which showing maximum revenue by quarter. also you have the Quarter object in query panel but you do not include this dimension in the report block. Instead, you want to include an additional column to show the maximum revenue by quarter in each year.

Solution:- By Using the In context operator, you can achieve the Max Quarterly Revenue using below formula.

Max ([Sales Revenue] In ([Year];[Quarter])) In ([Year])

Finally Your report looks like this:




ForEach context operator - when and why we use?

For to me using the ForEach operator to add dimensions to a context.

Example:- How To show the maximum revenue for each Quarter in a report which having the Quarter dimension in query panel but does not include it in the block:

Solution:- Using the ForEach context operator, you can achieve the same result with using below formula:

Max ([Sales Revenue] ForEach ([Quarter])) In ([Year])

How does work/ Above formula explanation:- You know Year dimension is the default input context in the block. So, by using the ForEach operator, you add the Quarter dimension to the context, by giving an input context of ([Year];[Quarter]).

Finally Your report looks like this:



ForAll context operator - when and why we use?


For to me using the ForAll context operator to remove dimensions from a context.


Example:- You have a report showing Year, Quarter and Sales Revenue and you want to add a column that shows the Yearly total revenue.


Solution:- Using the ForAll context operator, you can achieve the same result with using below formula:


Sum([Sales Revenue] ForAll ([Quarter]))


How does work/ Above formula explanation:- By default you have input context (Year; Quarter), for total revenue by year the input context needs to be (Year); Therefore, you can remove Quarter from the input context by specifying ForAll ([Quarter]) in the formula, which looks like above.


Note:- You can also achieve the same result by using the the In operator; in this case the formula is below:


Sum([Sales Revenue] In ([Year]))


Finally Your report looks like this:




I hope you enjoyed lots and learned something new while reading this document.




In this blog post I  would like to share a way to set user level security to a WEBI report.

By user level security I mean  restricting the visualization of certain partsa of reports to some users, based on a user secutity level.

For the sake of demonstration I  will work with three users and three levels of security.

This are my users and their security levels


userlevel sec


I will use an Excel sheet as a DP to hold these data.

There are some restritions on the use of this approach. In order this to work, users can only visualize the documents, not being able to edit it.


I will use e-fashion with the following objects in the result of my query :

State, City , Lines , Category and Sales revenuee

My user profile will be :

level sec = 1 - Users can see State , City and Sales Revenue;

level sec = 2 - Users Can see State, City, Lines and Sales Revenue;

level sec = 3 - Users can see all data.



To start, I create a document with my query from e-fashion :






Which gives me the following table






After that, I import my Excel sheet as a personal DP, which gives me the following tables :



both tables.png


Create a variable global_user_sec = =([level sec] Where ([user]=CurrentUser())) In Report,

This will give me the user´s security level according to my users table.


Then I can create two dim variables :

{Lines security] = If( ([ global_user_sec] >=2;[Lines]) and

[Category security] = If ( [global_user_sec] >=3 [Category]).


Dropping [State], [City] , [Lines security] and [Category security] and [Sales revenue] will do the trick.




Happy Holidays!  Hope you've all had a good 2014. Before closing out this Year, I'd like to put up a reminder:


Revisit the sizing of Web Intelligence Processing Servers on your deployment of SAP BusinessObjects BI 4.1


If you've done so in the past eight months or so, that's great!  But pencil that task into your calendar for 2015.


If you've haven't done so in a year, then I strongly recommend scheduling a sizing exercise for 2015Q1.


I support Web Intelligence.  I've supported it since before BI 4.0 Ramp-Up, through its General Availability on September 16, 2011, and to now with BI 4.1


It's been over four years, and through all the years, the three most important considerations that affect the smooth operation of BI 4.x WebI on your system is (1) sizing, (2) sizing, and (3) sizing.


If the sizing of your BI architecture is wrong, then you're going to face issues with performance, stability and availability, if not now then in the near future as demand on your system steadily grows. You might even find yourself spending quality time with me on the phone, and one of the first things I'll be asking you is "When did you last size your system?"


If the answer is before February 2014, then I'll be pointing you to the Business Intelligence Sizing and Deployment page, where you'll find the BI 4 Sizing Guide, updated February 2014. There's been changes to the recommended configuration of Web Intelligence in the newest edition of the Guide.


Rule: Always start your sizing exercise using the most recent Sizing Guide


When you get to the Sizing page, I recommend you click the "Follow" page, so that you'll be notified whenever the page is updated. Or go to https://service.sap.com/sizing and find the BI 4 Sizing Guide, where you can "Subscribe" to the Guide and be notified when it's updated.

There were changes to the recommended configuration of Web Intelligence services in the latest update, so I highly recommend reading through the entire Guide, just to make sure your BI 4.1 deployment is following recommended practices.


I've spoken with the Platform Product Owner, Sada, and he anticipates no changes to the Sizing Guide until at least BI 4.2.   But don't take my word for it.  Before starting any sizing exercise, make sure you go and download the latest version of the Guide.


Rule:  Do not use XI 3.1 sizing guides or best practices to size BI 4.1


One time, a customer pulled up their architecture deployment sizing document for their BI 4.0 system and in there was a references to XI 3.1. Turns out they had been keeping the same sizing architecture since their XI 3.1 days.  That definitely did not work, and they had lots of issues that mostly disappeared once they had redone their sizing exercise anew.


XI 3.1 and BI 4.1 are entirely different beasts, and trying to fit BI 4.1 components into a XI 3.1 architecture plan is like harnessing thoroughbreds to a dog sled. No matter how strong the beasts, they're just not gonna perform well when they keep tripping over each other and getting into each other's way.


The major differences between the two versions:


  • BI 4.1 WebIPS is a 64-bit process.  XI 3.1 runs as a 32-bit process, even on 64-bit machines.
  • BI 4.1 WebIPS delegates query and chart generation to a different Service.  XI 3.1 the generation is in-process.


Because of these differences, the sizing best practices developed for XI 3.1 is inapplicable, even harmful, to BI 4.1


Let's walk through the best practices we had for XI 3.1, and compare to BI 4.1:


XI 3.1 - deploy one WebIPS for each CPU core on the machine.

BI 4.1 - deploy one WebIPS per machine.


There wasn't a real good reason why you'd want to tie the number of XI 3.1 WebIPS to the number of CPU cores - the WebIPS threading model parallelized concurrent report processing requests just fine across multiple cores. Each thread would process a single document request, but the threads can run on different CPU cores.  WebIPS does not impose CPU core affinity.


XI 3.1 being 32-bit meant that memory resource limited how many processing jobs a single WebIPS could efficiently handle. Back then, proper management of memory utilization was an important part of sizing WebI.  Increased usage would quickly demand more processing power to be added to the mix, and because of the memory limits, the only reliable way was to add additional WebIPS to the deployment.  Having one WebIPS per CPU core was just a pretty good rule-of-thumb when it came to deciding whether you needed to add additional machines to the system.


But BI 4.1 WebIPS is a 64-bit process, and has a much expanded memory space. If I had a nickel for every time I've had to recommend customers alter the memory handling setting for their BI 4.x WebIPS, then I could afford, well, not even a stick of gum.


As you can see in the Sizing Guide, the BI 4.1 WebIPS is limited by IO.  You want to ensure the WebIPS have access to as much IO bandwidth as possible, and that means, especially on Virtual machine environments, having just one WebIPS per machine.


Very early on in BI 4.0, there was an issue with WebI failover across different machines (a problem with the Document Recovery Service), so I used to recommend two WebIPS per machine (failover of WebI Sessions on two WebIPS on the same machine doesn't require use of the Document Recovery Service).  But that time is long past.


XI 3.1 - set the Maximum Connections at 50 to start, increase slightly if necessary.

BI 4.1 - set the Maximum Connections at 200 to start, increase slightly if necessary.


Maximum Connections is generally determined by the number of active users you have on the system.  If you anticipate about 200 active users on the system at any one time, then you can set the Maximum Connections to 200.  But that might be problematic, since the WebIPS will keep a Connection open till idle timeout.  So even if you have at most 200 active users, there might be slightly more than 200 open connections.  Thus the recommendation to increase the number slightly above the number of active users.


The change from the older Sizing Guide is that, with testing, the "sweet spot" for the BI 4.1 was determined to be 200 maximum connections - that the WebIPS for 4.1 was more than capable of handling beyond 50 connections.


If you do have more than 200 active users, then bringing in a new machine into the cluster, to deploy new WebIPS, should be a consideration.


XI 3.1 - configure Output Cache Directory for all WebIPS on a common network share.

BI 4.1 - configure Output Cache Directory for WebIPS on local disk and do not use network share.

When the WebIPS first opens a WebI document, it has to unzip the wid file and parse the contents to build an internal representation of the document.  It then caches this representation to disk, that it uses subsequently for further processing. This opening/unzipping/parsing does take a bit of processing.  The good thing is that the WebIPS, when asked to open the same document, first checks to see if there's already a cached representation from when it opened the document earlier.  If there is, it proceeds to process the cached version, so that it doesn't have to spend the time opening/zipping/parsing the wid.

Now, all WebIPS that share the same Output Cache Directory can share any cached representation stored to disk by any other WebIPS.

This improves performance on opening a WebI document on deployments where there are many WebIPSes running.  A given WebI doc needs only be parsed once, then the cached representation can be used on subsequent requests for the same doc, regardless of which WebIPS the request goes to.

In XI 3.1, there were usually a lot of WebIPS running around.  One per CPU core, one each for 50 active users.  It wasn't surprising for me to work on systems with more than 16 WebIPS, clustered.  If there were no cache sharing, then the high likelihood of hitting a WebIPS that hadn't already cached the document was high enough to bring down the performance.


But for BI 4.1, there would be far less number of WebIPS running on the system, since it's recommended to have one per machine, one per 200 active users. Instead of 8 XI 3.1 WebIPS, there would be 2 BI 4.1 WebIPS.  Far less chance of a cache miss. Furthermore, since what limits BI 4.1 WebIPS is IO, every request to the cache would have a performance hit if the cache was located on a network share.

Because of this, the recommendation for BI 4.1 WebIPS is to configure the Output Cache Directory to fast local disk. 

There are other considerations that may discourage setting the Cache Directory to a network share.  There's an issue with older builds where, if the Cache Directory points to a network share, cleanup is not done and can fill up the disk (SAP KBase 2050700). Network access issues can cause the WebIPS to hang (SAP KBase 1757824). Network issues affecting cache access can even cause the WebIPS to shut itself down intermittently (SAP KBase 2057341) - I've resolved intermittent WebIPS shutdown issues by moving the cache to local disk.

All in all, it's better in BI 4.1 to have Output Cache Directory point to local disk.

If you do have a very complex and large WebI document that takes significant time for the WebIPS to parse the wid on open, what you can do is create a Server Group containing a specific WebIPS. Then all processing request for that WebI doc would preferentially go to that WebIPS and no other, so would consistently open from cache.

Rule: Revisit Sizing Regularly.  And Remember that the Sizing Guide is a Starting Point - a Guide and not the Rule

Remember that usage of your BI system will change with time - new documents will be created, the amount of data reported on will multiply, and the number of users consuming reports will increase.  That's a good thing, since that means your users think the BI system so good that they want to use more of it.  But that also demands the necessity of continuously revisiting sizing: determining the number and size of documents being processed by reporting off of auditing and monitoring the load on your WebIPS. 

Resize your system, at least once a year.

And remember that the BI 4 Sizing Guide is a starting point. The people who wrote the Guide are pretty sharp, and have done a lot of testing to make sure what they recommend is reasonable.  But the tests they ran, the level of activity for an "active user" that they considered, the typical size of a "large" WebI document they used, will all differ from your actual deployment.  Only you know what's going on in your system, and ultimately, sizing means tuning. Although you might size the system once a year, monitoring the heath of your system must be done continuously.

Rule: Split and Size the Adaptive Processing Server

This is a very important rule, and a subject unto itself.  I'll leave this subject for my next blog entry.

I will say this: the out-of-the-box default deployment of BI 4.1 has a single Adaptive Processing Server that is inadequate for any purpose other than a POC on a single machine.  If the APS isn't split and sized correctly (SAP KBase 1694041), your deployment will encounter issues.



The most important consideration that will ensure a stable and performant processing of Web Intelligence on SAP BusinessObjects BI Platform 4.1 is sizing.  In this blog, I briefly covered sizing of the Web Intelligence Processing Server.  Next blog, I'll briefly cover the sizing of the Adaptive Processing Server.

Requirement- How to Create Hyperlink in SAP Web Intelligence Report or, How to link between two documents in (3.x or 4.x)




·        Create two reports in Web Intelligence and export both into the Repository.

·        Take on example, the First report shows the sales value and quantity for all the states (in aggregate) and second report shows the sales values detail for the particular selected state.

·        To create Hyperlink in first report, open Infoview or LaunchPad and open the WebIntelligence document .

.        Just click on the report elememts tab which shown above > click on document or hyperlink tab.
.        Select the Object that is to be linked with the second report then RIGHT CLICK –> Hyperlink –> New
·        A new window will open for creating hyperlink as shown below, select Link to document tab

·        Click Browse, select the second report and select ok.

·        Select Refresh on open option under Hyperlink properties in Create Hyperlink Window and select the options like: (you want to open in same window or new window).

·         Then the report will open, as hyperlinks for the object that is selected.

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

Today I would like to show a way to merge two queries with a 1xN  relationship.

WEBI is good on merging queries ( DP1 and DP2 ) in which for one row of DP1 there’s, at most, one row in DP2 and vice versa.

When you come to a situation in which for each row in DP1 you can have more than one row in DP2 it’s a problem.

Let’s take an example. Suppose you have a query with students I will call it DP1. In DP1 you got: Student code, Student Name, Mother’s name and a measure (meas1). A second query DP 2 contains the course in which students are enrolled, DP2 has Student code, Student Name, Course code and a measure meas2.

Each student can be enrolled in zero or more courses

I will populate this queries with the following data :


Student code

Student Name

Mother's Name

















Student code

Student Name

Course Code






















My task here is to bring all data in a single table, like :


Student code

Student Name

Course Code

Mother's Name
























The data is on two MsExcel sheets , so, firstly, I need to import it to WEBI Rich Client.


After importing the sheets, that’s how my data looks like.tables.png




I will merge the two queries on Student name and Student code, which are the common objects.

A table with the merged Student name and Student code will present the data from both data providers.


To show the not merged dimensions coming from DP1 and DP2, I must create detail variables. Let’s do it with Mother’s name


mothers name detail.png




For Mother’s name detail I choose , for the associated dimension the merged Student code. Dropping it on the table containing merged Student code and merged Student name :


table with mothers detail.png



The challenge now is to bring course code into the table. If I create a detail  variable over course code , this will generate a #MULTIVALUE error since , for example, Student “Rogerio” is enrolled in 3 courses.


So, we need to , force the relationship to be reflected on the table. We can achieve it by dropping the Course code dimension in the table, which will result in the following table :


course code.png


The problem here is that Student “Cecil” that isn’t enrolled in any course won’t show up.

In order to show “Cecil”, select the table , right click and go to “Format table” and check “Show rows with empty dimension values”

Now the table shows the value for “Cecil”shows up


merged final.png


As a rule of thumb , when trying to merge DP’s with a 1xN relationship :

1 – Merge the common fields;

2 – Use the dimension coming from the N side query;

3 – Create detail variables from the 1 side query for each dimension needed with associated dimension equal the merged dimension;

4 – Check “Show rows with empty dimension values”on Table formatting for each table using dimensions coming from both queries.


Any comments will be much appreciated.






this blog post is about creating Set Operations , at Report side,

There are a few restrictions on using Combined Queries to get Union , Intersection or Minus queries. Some of them are

1 - One cannot perform Combined Queries over different DP;

2 - The objects used on the Result Set couldn´t appear on the query panel;

3 - The objects for both queries must be in the same order, be of the same type  and be on the same number (if you retrieve 3 objects from the first query, you must retrieve the same numbger on the other(s) query(ies).


The approach suggested allow users to combine different DPs, with different number of objects and with different types.


As I cannot demonstrate properly how it works using e-fashion, I will use two excel sheets . The Excel sheets represents some People registered for 2 different course, let´s say Course 1 and Course 2.


Course 1 contains the following information :

Student Code, Student Name , Student Addres, specialization and a measure variable val

Here´s the data for Course 1


student codestudent namestudent addressspecializationval1
1RogerSt 1 13Cooking1
2MariaSt 2 14Engineering2
3NadiaSt 3 15Writing3
4RobertSt 3 16Cooking4
5MarcusSt 3 17Engineering5
6AnaSt 4 18Writing6
7EmersonSt 4 19Cooking7
8HumbertoSt 5 20Engineering8
9ClaudiaSt a 21Writing9
10RobertaSt b 22Cooking10



Course 2 is composed by

Student Code, Student Name , Student Addres, Interest and a measure variable va2l


and here´s the data for Course 2


student codestudent namestudent addressInterestval2
1RogerSt 1 13Repolrting1
4RobertSt 3 16Big Data4
5MarcusSt 3 17Visualization5
6AnaSt 4 18Repolrting6
7EmersonSt 4 19Big Data7
11PauloSt 4 20Visualization11
12SergioSt 4 21Repolrting12
13MargaridaSt 4 22Big Data13
14NairSt 4 23Visualization



All objects except val1 and val2 are dimension, both are measures.


The challenge here is to determine, at report side :

The Union of both queries and display it on a table .


The same for Intersection, Course 1 - Course 2 and Course 2 - Course 1. Based on student code.


Firstly I will import both Excel sheetsa into WEBI Rich Client which gives the following tables



Then I will merge both DP in [student code], [student name] and [student address] , and Create detail variables for the other dimensions ([specialization] and [interest]) based on the merged [student code] dimension


Create two measures variables [Test 1] = If( Not ( IsNull([val1]));1;0) and [Test 2] = If(Not(IsNull([val 2]));1;0)



The Union is given , by default droppoing the three merged dimensions and the detail variables on a table




{Intersection] = If ([Test 1] = 1 AND [Test 2] = 1;1;0)

[Course 1 - Course 2] = If( [Test 1] = 1 AND [Test 2] = 0;1;0)

[Course 2 - Course 1] = If([Test 1] = 0 AND [Test 2] = 1)


Filter by the "1" on the desired Operation ( Intersection , Course 1 - Course 2, or Course 2 - Course 1 ).

Heres the table with all the test.


final table.png




Hi All,


Today I would to share a way to pass a parameter to a WEBI document through a prompt..

Here´s the cenário :

I would like to display in a chart with data from e-fasion a threshold of [Sales Revenue] so that, if the sales are above that threshold, I could give na alert..

Let´s work wth a threshold of 6,000,000.

Here´s how my query looks like




And my Report




One can easily achieve it by setting na alarm on the column like






But what if this threshold value is dynamics so users wants to alter it whenever they need it. ?


This solution gets the user this ability by simply setting a prompt when refreshing the document.


To do so, I create a “dummy” query filter. What I call a “dummy” query filter is a filter that always evaluates true, independen of the value entered.


For the puspose of this example , I will create a “dummy” filter on State.


Here´s how it Works :


1 – Create a filter on State with a prompt






2 - In your report , create a measure variable [Threshold] = ToNumber(UserResponse(‘Threshold’);"00000").


Since  a string starting with a number character is always less than any string starting with a letter, the “dummy” filter will always evaluates, independent of the value enterered.



Running a query with a threshold value of 1500000



running the query.jpg


3 Set the alert to







And that´s it




Comments are mostrly welcome

Using the values applied in a Input control among reports



Would like to share a method for using the values applied to na Input Control in a Report (let´s say Report1) in another Report (Report2).


This is not entirely my idea, I try to improve the discussion in here


and in Dave's blg

Web Intelligence - Input Control that affects all tabs | David Lai's Business Intelligence Blog

Let´s construct our report based on a query over e-fashion like this :


I will assemble Report1 as a cross-table containig [Line], [Year] and [Sales revenue] .

I´ll also create na Input Control over [Year].

That´s how my report looks like






And this is Report2





The whole idea is, when I alter the input control in Report1, Report2. Should be filtered by the same values.


To do so  lets first take a look at ReportFunctionSummary(). This function returns a string containing all filters applies on each report in a Document, so in our report, this is how ReportFilterSummary() will appear when I set the years 2004 and 2005 in the Input Control.








What we need to do is thentext between the brackets, which is the list of the values used by the Input Control.


I will use the following formula to separate the string list of values . Create a measure variable [list_of_values]

[list_of_values] = =Substr(ReportFilterSummary();Pos(ReportFilterSummary();"Year In List {")+14;Pos(ReportFilterSummary();"}")-Pos(ReportFilterSummary();"Year In List {")-14)


The blue part of the formula is where the string of the filtered Year begins and the red part is the Length of the string list.Then, that’s how I filter  Report 2 based on the string list.

[tst] = =If(Pos([lista];[Year])>0;1;If(Pos(ReportFilterSummary();"}")=0;1))


The blue part is where I test for [Year] being in the string list. In the red part of the formula, I test for the existence of the [Year] filter. If there is no [Year] in ReportFilterSummary() it means that an All selection was made in the input control.


Filter Report 2 by [tst] = 1 and that’s it.


There are somethings that one have to keep in mind while implementing this solution :


1 – As the search for [Year] filter ([list_of_values] variable), it is dependent on the language used. So, if you have an environment where you can have more than language selected, you must pay attention to that;

2 – This approach works , not only for the In List operator, but altering the variable you cant test for the other operators;

3 – Up to BOXI 3.1 SP3 this approach didn’t work because the Report had to be refreshed in order to it work. Apparently, in BOXI 4.1 SP3, this restriction was taken away







I would like to share a way to filter a table by the line numbers of its rows.

Firstly, there are some restrictions on it´s use :


1 - The line numbers in this solution is based on the default ordering and the position of each object in the table elements;


2 - If you re-order or re-arrange the table , you´ll have to alter the formula.

That´s said, , let´s start!


I´ll use a query based on e-fashion that returns [Year], [Month] and [Quantity sold] , which gives me the following table :






I´ll need three variables :

[linenumber], a measure variable . = 1 +Previous(Self), this will give me the number of lines

[ln]  a dimension variable = [linenumber] and another dimension variable [LineNumber] =  =NoFilter([ln] ForEach ([Year];[Month];[ln]))


The redefinition of the context must follow the order of the dim objects inside the table , if the table is re-arranged, the contexto redefinition in the formula must be altered to consider it.


The report should be filtered by [LineNuber]


The next Picture, shows the table filtered by [LineNumber] between 5 and 10

table 2.jpg



The Timedim function was introduced to Webi 3.1 (SP3?) as a way to create a consecutive range of dates when the source date dimension returns a non-consecutive range.


This is more easily demonstrated than explained!


Using the Island Resorts sample Universe, create a simple report which returns the Reservation Dates for the customer ‘Piaget’:


As we can see, this customer has made two reservations in August 2007, and the dates are non consecutive.


By using the TimeDim function, we imply a range of dates between, or filling in, the range of dates returned by the date dimension:


When using TimeDim we can provide one of four operators – DayPeriod, MonthPeriod, QuarterPeriod or YearPeriod, to determine the range of dates returned. The default (which can be omitted as above) is DayPeriod and returns a consecutive range of dates by day. MonthPeriod is best explained with another Customer – choose ‘Baker’:


When we use the MonthPeriod operator, a range of dates consecutive by month is returned:


Choose customer ‘Oneda’ to demonstrate QuarterPeriod:


.. which, as you’d expect, returns dates by quarters:


Finally, the YearPeriod operator should hold no surprises by now:


It’s simple enough to wrap the TimeDim function in another function. For example, we can dispense with the dates as above and just show the year by using:


…and we can fill in any blanks in our missing columns by using a function like:


So what are the caveats for using TimeDim? The dimension you use needs to be a Date Object which is from a data provider – it can’t be a variable.


Let's see how this can work in real life. If we remove the Customer filter from the example query and just return all data, we should get something like:


If we graph a count of customers by the Year and Quarter of the Reservation Date:



=year([Reservation Date])

=Quarter([Reservation Date])


...we get:


As you can see, we have missing quarters in 2008 and 2009.


If we replace the Year and Quarter date objects with their TimeDim equivalents:



=Year(TimeDim([Reservation Date];YearPeriod))

=Quarter(TimeDim([Reservation Date];QuarterPeriod))


...then our graph is a lot more acceptable:


I hope this helps de-mystify the TimeDim function a little and helps to demonstrate its usage!


I would like to share a trick about setting 'alerts' on a Column Chart.

Consider the following cenario :

A query over e-fashion that gets State and Sales Revenue , the requirement is to show these values in a Column chart so that , (if sales revenue]/1,000,000) the bar chart should be red, if the value is between 3 and 7, yellow, otherwise green.

So , this is how thew bar chart should be shown



bar chart.jpg


These can be accomplished by usnig a stacked Columnr chart instead of a simple Column Chart.


Create n measure variables, one for each desired color (in our case will be 3)

[red] = If([Sales Revenue]/1,000,000 < 3;[Sales Revenue])

[yellow] = If([Sales Revenue]/1,000,000 Between(3;7);[Sales Revenue])

[green] = If([Sales Revenue]/1,000,000>7;[Sales Revenue)


Then set your chart to stacked column chart and assign each measure variable as an Value axis.


bar chart2.jpg


It will render the chart with the defined pallette. In order to change the color without changing the original pallette, do as follows :


bar chart 3.jpg


1  Click, on the legend, in the color to be alteres;

2 - Choose Formatting->Font->Style;

3 - Choose Background Color;

4 - Choose the desired color.


Repeat this process for the  other colors.






Sometimes we face a requirement of concatenating values from an object., like below screen :




This can be achieved with 3 variables :


[conc] , a dim variable

[conc] = ([State]+","+Previous(Self));

[final] a measure variable


[final] =Last ([conc] ForEach ([State])) In Report


[No_ending_comma] a measure variable


[No_Ending_Comma] = Left([Final];Length([Final])-1)


How does it work :


The [conc] variable, will concatenate the value of [State] and the previous value of [conc], since the Previous value of 'California' doesn´t exist, it returns null and the the first row of [conc] is justa California. Refer to the above image, column [conc] , to see the values of [conc] as [State] is shown.


The  second variable [final] is where the 'trick' works :


It takes the last values of [conc] in the report calculated in the context of [State]. I use the ForEach operator so I can get the last value independent of the context used.


This example shows the values concatenated in descending order. To get it in ascending order, use [conc_asc] = (Previous(Self)+","+[State])

[final_asc] = =Last ([conc] ForEach ([State])) In Report

[No_ending_comma_asc  =  Rigth([final];Length([final]-1)


And what about the following cenario :



The requirement is to concatenate the stores name for each state



Here, I queried e-fashion for [State] and [Store Name]






I altered [conc] to :


=(Previous(Self;([State]))  ForEach([State];[Store name]) +","+([Store name] ForEach([State];[Store name])))




[final] to :


=Last ([conc]ForEach([State];[Store name])) In ([State])






the stores name for each state

Hello Everyone,


In my last blog, I mentioned about the supported input formats for selection option BW variable for the newly introduced feature of manually entering BW prompts.


I tested the new feature further in webi rich client and after some effort was able to make it work.


Here is the step by step instruction on how to make this work.



  1. I started testing this feature by first creating a BW Query with a Selection option variable prompt as shown in the screenshot below:



Image 1.PNGImage 2.PNG






2. This is the most important step. Add a registry key for a client machine running webi rich client at the location below:



Image 6.PNG


The Value Name is the next number in sequence. Depending on your configuration, it may be different then what you see below. The Value Data for the key is “–Dsap.sl.bics.variableComplexSelectionMapping=multivalue”.


Image 7.PNG


3. I then created a webi report based off the BW query using Webi rich client. I tried using the input formats as mentioned in the last blog (intervals, Expression with operator and so on).


Image 3.PNG


4.  I added multiple prompt values for Country Key  as shown below:





Image 4.PNG

5. Ran the report and checked the results:

Image 5.PNG



Above dataset in the report shows that data brought back consist of countries with key value 1,2,3,4,6,25 and 33 excluding 5 (as NOT INCLUDING 5 was one of the conditions.)


I hope this is helpful.




We often get questions regarding strange calculation results when using decimal numbers in Web Intelligence.



  • Why don't I get 0 when I do A + B - C, although C is supposed to be equal to A + B?
  • Why does the result of this running sum is different when I change the sorting of my table?
  • Etc.


These strange calculation results are due to the fact that, internally, Web Intelligence represents decimal numbers using the 64-bit precision version of the Floating Point data type defined by the IEEE 754 standard.


A detailed explanation of that standard can be found in the following Wikipedia page: http://en.wikipedia.org/wiki/IEEE_floating_point.


With the IEEE 754 standard on floating point arithmetic, decimal numbers cannot be perfectly represented in binary form, since not all their digits can be trusted. In fact, the number of digits which can be trusted actually depends on the size of the representation.


With the 64-bit representation (also called "double precision") used in Web Intelligence, the number of bits used for stocking the data is 53 and, as explained in the above Wikipedia page, the number of digits which can be trusted is: log(2^53), rounded to 15.


IMPORTANT TO REMEMBER: In Web Intelligence, the maximum number of digits which can be trusted in a decimal number is 15 and that takes into account digits both before and after the decimal mark.


  • 100,000,000,000 + 0.001 will correctly result in 100,000,000,000.0010 because this decimal number only requires 15 trusted digits to be represented (12 digits before the decimal mark and 3 after).
  • 100,000,000,000 + 0.0001 will wrongly result in 100,000,000,000.0000 because the correct result would require 16 trusted digits to be represented (12 before the decimal mark and 4 after)

With this limitation in mind, summing decimal numbers might not always give the expected result, especially if the expected result is 0...!


For example, summing 19000.2, 0.123, -9100.3, -0.000000000002543, etc. will allow for only 10 digits after the decimal mark to be trusted. Indeed, 5 digits before the decimal mark are already used by the largest number 19000.2. Consequently, if the result of that sum is something like: -0.000000000013327, it will be indistinguishable from 0, by the IEEE 754 standard.


Therefore, if a 0 result from a decimal numbers calculation is used as a condition for further processing in a Web Intelligence document, it is highly recommended to convert the decimal numbers into integers before that condition is evaluated. This rounding operation can be done with the Round(number; round_level) formula, using 0 for the round_level parameter.


Now, why is changing the sorting of a table might also change a decimal calculation result?


Well... Another major limitation of the IEEE 754 floating point format is that the integer part of decimal numbers is represented by a fraction and is therefore approximated. As a consequence, there will be a rounding error which will be propagated along the calculation, leading to different results according to the way the values are sorted.



Say, we have A = 1,000,000, B = 1.2 and C = -1,000,000

  • A + B + C will result in 1.19999999995343E0
  • A + C + B will result in 1.2E0

This is because the rounding error does not propagate the same way through A + B + C, than it does through A + C + B.


I hope these "strange calculation results" will be clearer now!


If not, please do not hesitate to ask questions below.


Best regards,



Hi Team ,

It's simple trick to display totals of stacked column like below


On top


at bottom (along with category axis)


How to do !

On Top

I have Business area and fiscal period wise Revenue stocked column chart.

1.Create a stacked column chart with Business Area in category axis ,Revenue in Values axis  and Region Type : Fiscal Period

2.Now we need totals to display on top .Create a detail variable to get Business are wise totals .

Total =FormatNumber([Capital  Cost FC] In ([Business area]);"#")

Note : If you have big numbers ,then it is better to show them in Millions (if Value)/MT (if Quantity) .So that we can adjust totals to look like for each column.


3.Create a variable with dummy measure .This is to have measure against Detail total variable

Dummy =100


4.Create a column chart (any chart ) with Total variable in X- axis , Dummy measure variable in Y-axis.Fiscal period in region type to have context (if any to measure)

5.Show only category axis only .Unchecked value axis, title,legend ,..

6.select palette for chart : create a custom palette with all white colors

This is to don't show columns in chart.Format column chart category axis to show numbers in bold color .Decrease height of column chart to get the category axis and some space for columns .By doing this we should get values like below.

7.This is important part .Align this column chart on top of stacked chart so that it will appear as total of the column.


Cons :

* we need to have another chart , it will heavy our report .

* we should be very concise on formatting and aligning.

* It is not possible to show very outside of each column .All totals are at same height.


at bottom

Assign Total -detailed variable in category axis .Insert a blank cell , name it Totals and align it as to represent category axis total .


Totals are shown at category axis itself .We should inform to user .


Hope somebody will find it helpful.


Filter Blog

By author:
By date:
By tag: