1 2 3 14 Previous Next

SAP BusinessObjects Web Intelligence

201 Posts

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.




Update - There is a sample WID file at O Meu Site - Home 

with the name eua_sales_ - merged.wid (direct link - http://rogerplank1234.weebly.com/uploads/3/8/0/6/38067671/eua_sales_-_merged.wid )

It is in BO 4.1 SP5.

It´s downloadable and free of charge



Hi All,


I recently attend to an event of another BI Vendor. He demonstrate a way of utilizing an Infographic into its application. I thought "Humm perhaps it can be done in WEBI", so I started to experiment and come to this solution.


I´m using an image of the map of the states of USA and plotted the anual sales revenue of the stores in the states, accordingly to e-fashion universe.




As you can see, when a hoover the mouse over california, it shows me the value of sales revenue for that state for all years (note the filter on [Year] on the filter bar).


The Infographic is dynamic in the sense of clicking on a state, the report will be filtered by that state.


The idea here is to use a buble chart on the top of the image and plot a dummy Excel file with the correspondmig coordinates of the desired states in the picture.


I used a picture, downloaded from the we,b with 800 x 501 pixels of size.


The hardest part was to get the points (x, y) of each state inside the picture. When doing it yourself bear in mind that, usually , the coordinates of the Y axis in the pictures is taken form the top to bottom, while for charts it´s taken from bottom up.


This was the Excel file used in this sample.





The resulting buble chart on the top of the picture is shown in bellow picture






To star to build your dynamic InfoGraphic, the first thing to do is to find the image and save it on the local machine. Once it´s done, drop a blank cell onto your report.

Right click this cell and choose Format Cell-> Appearance and check Image from Filee, like below :


cell format.png


Click on Upload. Set the hight and widht of the cell to accomodate the full picture.


Read your Excel file as personal data provider.


Create another query from the e-fashion Universe.That´s the query I use :query.png


Execute both queries and merge them on State into Merged State.


Now, drop a bubble chart on the report, and assign it´s data like bellow



assign data.png

Align your buble chart from the bottom left corner of the cell.


Adjust the maximu value  of the AXIS scale in the chart to the size of the Picture (AXIS 1 to 800 and AXIS 2 to 501).


Now, set the background transparency of the chart to 0



In Area Display unjcheck all visible information like below


area display.png

See how the "bubles" in your chart are positioned on top of the image, correct the positioning in the x and y columns of your excel chart.

To finally hide the bubles themselves, go to Palette and Style , set the color to transparente and uncheck line symbol,


pallete and style.png

Now, when hoover the mouse over any state that contains data, you should be abble to see the name of the State and its X, Y and Valor.


To add the actual Sales Revenue of each state, I create a detail variable ,[Sales State] = FormatNumber([Sales Revenue] In [Merged State];"US$ ##0,000.00"), and assigned it as below

sales det.png
When you hoover the mouse, now you should be abble to see the value for the state as well.


Hope you liked it,



Dear All,

Here is the document which gives the step by step procedure to redirect from BO Report to specified ECC T-Code to view attachments using Hyperlink.

Business scenario:

Some of the information users wanted to have the BO WEBI Report to redirect to ECC T-Code to view attachments for further analysis of Order details. The below step by step procedure demonstrates how this is accomplished.

1.     First understand about the Transaction code to be linked, Open SAP Business Suite and give the TCODE is IW32.


  1. 1.1.   Open (ECC) Business Suite and Logon using proper credentials.
  2. 1.2.   Go to transaction code: IW32. For example:



  1. 1.3.   Initial screen is displayed.Input order ID. Click on ‘Header Data’ or press ‘Enter’.
  2. 1.4.   Detailed description of the order is displayed.


  1. 1.5.   On this display, press ‘F1’ to show ‘Performance Assistant’.


  1. 1.6.   Click on ‘Technical Information’ button (1.6.png).
  2. 1.7.   Technical Information window is displayed.
  3. 1.8.   Collect the information for: Screen Field, Program Name, and Screen Number.




  1. 1.9.   Logon to BI Launchpad and give credentials.Go to ‘Preferences’à’Web intelligence’àUnder ‘Modify’, select ‘Web’ (‘HTML’ in higher versions).




  1. 1.10.Save and close. Open Webi report in ‘Modify’ mode.



  1. 1.11.Right click on order number column.



  1. 1.12.Select ‘Linking’à’Add Hyperlink’.Hyperlinks editor screen is displayed.


  1. 1.13.Enter the link address of ECC T-Code (Contact basis to get this URL)


  1. 1.14.Click on ‘Parse’. Address Path is automatically assigned and displayed.




  1. 1.15.Build a formula under ‘Customize the following dynamic elements of the addresses.



  1. 1.16.On clicking ‘Build Formula’ from the drop down, the formula editor is displayed.


  1. 1.17.Write your desired formula for concatenating the transaction code, system name, system field, and Column specific field (for example: ‘Order ID’).



  1. 1.18.Validate the formula and click ‘OK’ to save it. We get back to the hyperlinks window.



  1. 1.19.Click ‘OK’ to finish the configuration.


  1. 1.20.The column field value is converted to a clickable hyperlink with below formulae generated automatically.


="<a href=\"http://testbibo:8888/sap/bc/gui/sap/its/webgui?~transaction="+URLEncode(Concatenation("IW32 CAUFVD-AUFNR=";[Maintenance Order - Key]))+"\" title=\"Quality Management Record\" target=\"_blank\" nav=\"web\">"+[Maintenance Order - Key]+"</a>"


  1. 1.21.In the address bar, notice the syntax of the field has changed to the above format.




  1. 2.     Click on the order number hyperlink to navigate to SAP ECC Logon screen.



  1. 2.1.   Provide credentials for account with access to the T-Code as configured in the hyperlink (in this case: IW32).


  1. 2.2.   Click on ‘Logon’ or press ‘Enter’. You are logged on and automatically redirected to the T-Code with the Order Number automatically entered.



  1. 2.3.   Press ‘Enter’ to drill into the details.




  1. 2.4.   Details are displayed.


  1. 2.5.   From the top right corner of the screen, select the ‘Services for Object’ drop down button (2.5.png) and select ‘Attachment List’.



  1. 2.6.   Attachment List is displayed.


  1. 2.7.   Double click on an attachment to view it.



  1. 2.8.   Attachment is displayed.



Hyperlinks successfully added to BI Report to navigate to ECC transaction code and view attachments.

As you may of heard already, things are changing in BI4.1 SP06 when it comes to FHSQL (Free-hand SQL) and Stored Procedures. In BI4.1 SP05 we added the ability to convert Desktop Intelligence reports directly to Web Intelligence reports that contained FHSQL. This information was put together in this blog, First look at FHSQL reports in BI4.1.


My colleague, Felicia Shafiq, also documented FHSQL in BI4.1.5 - Limitations, Gotchas, and Troubleshooting steps she went through during her process.


Well, we went ahead and upgraded Web Intelligence in SP06 so you can now edit/create reports using either FHSQL or Stored Procedures. I was able to get SP06 and do some basic testing of the new feature. Here are the steps that I went through creating 2 tests reports. The first one is based off of eFashion using a very basic query. The second one is a report using a Stored Procedure with a prompt connecting to a MS 2008 SQL Server using a JDBC Connection.


1. Create new document

FHSQL New Document Screenshot.jpg

2. Choose relational connection

FHSQL New Relational Connection eFashion.jpg

3. Query Script Editor opens. Place FHSQL into editor

FHSQL Query Script Editor eFashion Basic.jpg

4. Click Validate

FHSQL Query Script Editor eFashion Basic Validate.jpg

5. Click Ok twice and then the Query Panel opens showing the objects

FHSQL Query Panel eFashion Basic.jpg

6. Click Run Query and the report returns

FHSQL eFashion Basic Query Report Results.jpg

As you can see, this is a very basic test showing the creation of a new Web Intelligence report using FHSQL. The next feature that we covered was the use of Stored Procedures. I myself have worked/used Desktop Intelligence for over 7 years and looked forward to when Web Intelligence would have this feature. Its HERE! Granted it works in a different way when comparing to Deski, but it works. You no longer have to use Deski. Here are the steps I went through when creating a new report:

1. Create new document

FHSQL New Document Screenshot.jpg

2. Choose relational connection

FHSQL New Relational SQLServer JDBC Connection .jpg

3. Query Script Editor opens. Place FHSQL calling Stored Procedure into editor

FHSQL Query Script Editor calling SP with Prompt.jpg

4. Click Validate

FHSQL Query Script Editor calling SP Validate with Prompt.jpg

5. Click Ok twice and then the Query Panel opens showing the objects

FHSQL Query Panel calling SP with Prompt.jpg

6. Click Run Query, prompt window displays

FHSQL Query Panel calling SP with Prompt Display.jpg

7. Fill in prompt value, click OK, report runs and completes

FHSQL SP with Prompt Query Report Results.jpg

As you can see you will have to call the Stored Procedure using FHSQL whereas in Deski it would behave differently. You will also have to add prompt syntax in the FHSQL to receive a prompt window as I demonstrated. Other than these minor changes, both FHSQL and Stored Procedures are now available in BI4.1 SP06.

You are no longer bound to Deski, Webi has moved forward!

During the writing of this blog, testing using and ODBC connection to SQL Server was found not be working correctly. This issue is currently with Development under SAP Note  2174240 - Error "A problem is encountered. The requested action cannot be completed." when creating Stored Procedure via FHSQL using ODBC connection

Before there was SCN for BusinessObjects products, the only reliable resource for BusinessObjects problems was BOB.


Jason Everly asked us to write a blog post to help publicise BOB on here so that report authors, universe designers and BO administrators are aware of the most successful long-standing independent resource for BO developers. He has joined BOB to publicise the SAP Webi Bulletin - The Webi Bulletin - New Webi Information Source


So, what is and what isn't BOB?


BOB is not affiliated to SAP in any way. It started years ago as a ListServ project and was migrated to become a forum in 2002 to become a home for BusinessObjects enthusiasts to help each other and seek help as well as share knowledge.


There are no fees involved - it's free to join and participate. The flip side of that is that all contributors are volunteers. As such, please don't flag something as urgent as they tend to get ignored. The contributor's work appraisal is not based on how quickly they have resolved your BOB issue!


There is a great search facility; many common issues have been discussed down the years, from contexts, to aliases to calendar tables.


BOB is not a job board, nor will it help you with interview questions. The purpose of BOB is to provide support for genuine BO problems that you are facing. The more details that you can give, the better the answers will be.


BOB does not offer support for other SAP products such as BW and Bex - you're much better off staying here for that. Below is a list of what it does offer, in addition to the standard forum areas like FAQs, Forum Support and General Discussion


If you follow the link above, it will take you to a post on the BOB Board within the Webi section. However, there's more to BOB than that!

There are dedicated sections for most of BO's core products (and in some cases versions). These include:

  • Building Reports - this has subsections for Webi, Deski, Crystal and older versions of each so that you can seek help whether you've just got stuck on a new version or if you've inherited a dinosaur in your new role!
  • Semantic Layer / Universe Designer - most of the questions in here focus on database-driven universes rather than BW-based IDT designs but this is mainly because of the historical nature of the product, with some of us having used the tool since the days of Windows 3.11
  • Security and User Administration - this has information about the legacy tool (Supervisor) and CMC information including security concepts, user management and general questions.
  • Server Administration, Installation and Updates - this is for the systems administrators out there who are tasked with looking after the SIA and other server-based parts of the BO deployment
  • Enterprise Scheduling and Distribution - this section includes legacy support for Broadcast Agent but is more focused on the current scheduling including dynamic recipients and task-based distributions.
  • Data Integrator - Most, if not all, aspects of BO's ETL tool are discussed in this area with its relevant sub-forums.
  • SDK (VBA/ASP/JSP) - one for the web developers who are looking to integrate BO reports into their website's front end rather than using the standard BO interfaces
  • Other Products - an area that covers the non-standard BO products, such as LiveOffice, Lumira, Xcelsius and Auditor


There are also sections on User Groups and Conferences as well as a Downloads section that has some useful utilities to help make life easier for BO developers and administrators.

Hi Everyone,


SAP BO Web Intelligence allows functions which help us to calculate cell value and formatting as per business requirement. Here I am explaining a simple step to write function and calculate value and percentage based on existing data column.I am assuming that query is added and now we have following data in in Webi report.



As per above report we have Sales Plan Qty, Gross Sales Qty and Sales return Qty and Now we have to calculate ‘Net Sales Qty’ and sales Performance based on Gross Sales, Sales Return Qty and Sales Plan Qty. follow below steps.

1. Add new column by right click on Sales Return Qty and insert column on right side.

2. Select new insert column and click on ‘Formula Editor’ on formula Bar or right click and Edit Formula.

3. Write formula =[Gross Sales Qty]-[Sales return Qty]’ to calculate Net Sales Qty.


4. Now add one more column to calculate Sales Ach % (formatting two digit decimal with %-symbol).  Go to Formula Editor then write formula ‘=FormatNumber(([Gross Sales Qty]-[Sales return Qty])/[Sales Plan Qty]*100;"##.00%")’



Now we are getting desire output by formula.


Hope this blog gives clear understanding to use Formula in webi reports. And it is helpful.




Kashif Ali Khan

Note : Before we start designing any of the details report , consultant and analyst should discussed this questions with the User first.

User Requirement Gathering Questions


Questions related to All reports :


  1. How many reports User required ?
  2. In which format User needs report and duration required ?
  3. Whether the User wants to implement hierarchy in reports or not ? If yes then it should be present in all reports or some reports ?
  4. Are there any prompts (drilldown, filter) which are common to all reports ?
  5. What are the security constraints common to all reports (i.e access and data displayed level for the user) ?
  6. How the User wants to analyse data based on time dimension (eg Year,Quarter etc) ?
  7. Specify the common formatting pattern (eg. color,table width etc) ?
  8. Any sorting condition which should be common in all reports ?


Questions related to Specific report:



  1. What are the fields and calculations reuired (i.e dimension, measure) ?
  2. How to represent data i.e how many charts and tables present in a report ?
  3. What are the mandatory filters required ?
  4. Any specific condition or filters required in report ?
  5. Whether the User wants to highlight specific data in reports or not ?
  6. Drill should be implement or not ?
  7. What are the security constraints related to access of reports and also the data displayed on report ?
  8. What are the prompts/optional prompts should be implemented User wants to see Hierachiral Data while choosing LOV (list of values) ?
  9. Whether User wants to apply any sort condition or customized sorting ?
  10. Do we need to create any hyper links in report or wants to link reports together. (i.e : link to dashboard) ?


Whats Next ?


1. Document User requirement .

2. Create a mock up in Excel .

3. Get the approval/sign off and start working

There are certain settings which we should follow while importing BO Webi reports into Live office PowerPoint. Some of those settings are as follows.


Best Practices while Importing Webi report tables into Live office PowerPoint:


  1. Always keep size of BO report tables exactly same as it is shown on the power point slide.
  2. After importing the report objects into ppt slide, make sure that you keep ‘Apply report format when refreshing’ check box checked for each table object on ppt slide.

Best Practices while Importing Webi report charts into Live office Powerpoint:


  1. Always try to refresh the chart structure from webi report.
  2. If this could not be possible then keep the size of the chart as close as it is in ppt. If the size is too large in the report then it will be blurred while shrinking it in ppt.
  3. While reducing the size of charts in ppt, make sure to check the ‘Lock Aspect Ratio’ check box for the charts. This will do the compression or expansion in same ratio and will keep font visibility.


Special Note:

  1. Always keep webi report on ‘Refresh on Open’. If this setting is not ‘on’ then ppt refresh will throw error.
  2. Please uncheck any other analysis settings like report level filter in SAP BO Webi report.
  3. Please do not edit the SAP BO Webi report while refreshing the objects in PPT. It usually results into Live Office errors.
  4. Merged cells in webi report don’t work correctly in LiveOffice Power Point. Hence design the report structure accordingly.

As a long time Webi user, and provider of maps integrations, I have always wished there were a proper SDK for integrating custom visualizations in Webi. My first exploration was through Xcelsius which I soon hope to release as a freeware component called "SAP Dashboard Viewer."

Now, I am thrilled that I can offer to the BusinessObjects ecosystem a new premium extension for Webi.


Full Disclosure


My goal is to continue pushing extension points for Webi and show designers / developers what is possible. Why? The more users who express interested and become reliant custom extensions, the better the SDK will get. The beauty of Webi Extension Points is that you only need JavaScript development skills and some patience.


This particular CMaps Analytics solution is not freeware, and for a good reason. This extension includes a supported Google Maps license which is required for any Google Maps integration, our premium maps JavaScript API and cloud services, with support for the extension.


My Approach

With that said, I had two goals to consider when creating a maps extension for Webi:


1. Ensure that someone with skills to create a Webi report, could just as easily enable a map.

2. Provide full support for all of the map visualizations offered through CMaps Analytics API.. We allude to Google Maps because it provides the base layers, but the Javascript API that powers this map is actually CMaps Analytics JS API, which provides hundreds of extra functions needed for Business Apps and analytics.

Here is another article I posted on Linked-In that highlights some of the interesting business cases


Here is the process for building a simple map in BI4 SP04 for the every day Webi Designer.




What's Next

In the next couple of weeks, we are pulling together all all of the lessons learned developing custom extensions. Webi Extension points is a great step forward but it is not perfect, so my hope is that as customer's reliance grow on this technology we can see new exciting ways for interacting with Webi.

Want to get the Extension

We do have the extension available as a test drive for BI4.1, though you will need to check for service pack availability. I would love to hear your feedback first hand so don't hesitate to contact me direct. CMaps Analytics for Webi

Hello Everyone,


I hope you all are familiar with the BW Manual prompt entry feature introduced with the release of BI 4.1 SP 5. If not, please go through my blogs written earlier, explaining the functionality and the steps to enable it.

BW Manual prompt entry functionality is introduced in Web Intelligence

How to enable multi value option in Web Intelligence for Selection option variables coming from BW

I am writing this blog post to clarify few confusions around this new functionality that has been brought to my attention by few users who tried using this functionality.

In order to explain the scenario more precisely, I created a Web Intelligence report before adding the switch: 


in registry (for webi rich client) or in the Adaptive Processing Server (APS).

In the below screenshot, you can see that a selection option variable defined in Bex showed up as a range with “Start Value”  &  “End Value”.





On refreshing the report, notice that Country “Aruba” shows up as the start and end value entered, acted as a range (Argentina – Australia), instead of individual prompt selections.



Later we added the switch to the registry and the APS.  The screenshot below was taken after the switch was implemented and the report refreshed.


Notice the prompt. It has changed to a multivalue prompt with 2 single values instead of a range as previously designed.



Here the report has completed, and Aruba has disappeared from the result set. If an end user is unaware of this change, it will result in incomplete data being returned as the values entered now are individual prompt selections and does not act as a range any more.  (Aruba is missing)




End users can encounter this change in data during migration (migrating to an environment with switch enabled) or if the switch is added to an existing environment with SP5 or above.



One workaround is to define the variable as “Interval” (as shown below) in the back end BEx instead of a selection option variable to ensure that the prompt still shows up as a range even after adding the switch.


While the workaround is viable, it doesn’t allow the ability to use the other conditions that are available with the switch (pattern matching e.g: !5, >7 etc).

Hope it helps clarify the confusion around this functionality.



Hi All,


recently I came across a requirement to merge two Queries where the values of one of the merged dims had leading spaces.

Values from Query 1 dim was :


id            value   

"123"     1

"345"     2

"567"     3


and the values from Query 2 was


id               dim1

"   123"      a   

"345"         b

" 567"        c


Simply merging this dimensions will lead to the following values for the merged dim


merged id

"   123"

" 567"





How to get the merged values as





and use the other objects from the Queries like ?


merged id     val     dim_det

"123"           1           a

"345"           2           b

"567"           3           c


Steps :


1 - merge both queries on [id] onto [merged dim],

2 - create a dimension variable [final merged dim] = Trim([merged dim]),

3 - create a detail variable for [dim],  [dim det] = Trim([dim]) whit associated dimension = [final merged dim],

4 - use [final merged dim], [val] and [dim det] on your report.





recently I came across a requirement that was to test if a value was in list where the list was in a variable. The variable, [list] = "123;234;456;1114678;768;999111", I I need to test if the value of a certain dimension , [numero], was in the list [list]. As you know, the InlList operator can be used only with a literal list (that´s it a text, like "123;234;456;1114678;768;999111", not in a variable. Then testing for [numero] InList([test]) will throw an error.

To overcome this situation, one can test for the Pos of [test] in [list], if the result is >0, [numero] is present in [list]. The downside is when [numero] is contained in another value for instance , "111" is not part of the list, but is contained in "1114678". To parcialy overcome this issue, you can test for ";"+[numero] and [numero]+";". So my formula will be :


If( (Pos([list];";"+[numero)>0)or (Pos([list];[numero]+";")>0);"InList";"NotInList")





Filter Blog

By author:
By date:
By tag: