Random Thoughts

Views on life

Archive for the ‘WEBI’ Category

Linking Dashboards and Reports in BOE Dashboard Builder

Posted by Hemanta Banerjee on January 13, 2011

One of the common requests I have found on the BOB board relates to how can we link WEBI/CR reports with XCelsius dashboards. The common scenario is where we are showing some summary information in an XCelsius dashboard and I want to show some related detailed report using WEBI or CR on the same dashboard.

In BusinessObjects Enterprise XI 3.1, Edge XI 3.1 and Crystal Reports Server 2008 v1, the new Interportlet Communication (IPC) feature in Dashboard Builder allows the transfer of information between distinct analytics. Now you can pass parameters from an Xcelsius dashboard to another XCelsius dashboard or to Crystal Reports and Web Intelligence. Using this framework, I will show 2 simple ways to link between:

  1. Two XCelsius dashboards
  2. Between an XCelsius dashboard and Web Intelligence/Crystal Reports

XCelsius to XCelsius linking

An XCelsius dashboard used as a source document has to be configured with a Portal Provider Connection. The target analytic also has to be configured with the Portal Consumer Connection. We will send parameter data between two analytics with the use of a simple example: sending information from a list box selector in one analytic to drive a chart in another dashboard. The steps are

Create the provider analytic

1. Create the list box control for the years: As you can see below I have created a simple list box control with the years.


The selected item from the list box is placed in cell B2 in my example (marked in yellow). I have also set the size of the canvas to fit the component.

2. Create a new portal data connection as shown below.


Choose provider as the Connection Type and give a name to the range (in my example Year). Since we are passing a single cell of data, select Cell  as the range type and the cell B2 which is the target of the list box selection. Also go to the usage tab and instruct the data connection to communicate with the consumers when the value in the target cell (B2) changes.


This will ensure that whenever the user makes any selection on the list box, the label will be passed to the target/consumer of the portal connection.

Create the target analytic

1. In the target dashboard I have created a simple dashboard where I have a chart combo box.


In the properties of the combo box I have set the combo to read the current selection from a cell. When the selection in the cell changes it will read the row of the data corresponding to the selected year and place it in the target row which drives the chart.

2. Define the portal data connection of type consumer as shown below.


Keep in mind that the Range name has to be the same for both the provider and the consumer. Also since the provider is going to pass a single cell I have selected the range type as Cell and have it update cell B2 which drives my drop down and chart.

Combine them in the dashboard

Now log on to InfoView and create a new Corporate Dashboard. Drag your “Provider” and “Consumer” analytics onto the dashboard. Also activate “Content Linking”.  This can be done by setting the properties of the Provider analytic. Choose the Provider analytic as the “Source Analytics”, and Consumer as the “Target Analytics” as shown below.


XCelsius to WEBI/CR linking

An XCelsius dashboard used as a source document for Crystal Reports and Web Intelligence has to be configured with the FS Command connectivity. The parameters are then assembled in an OpenDocument URL by the Dashboard Builder framework. For more information about the OpenDocument syntax, see the documentation:

To illustrate this example I have a summary report in XCelsius and use WEBI to show the detailed month wise breakdown.

Create the provider analytic

The steps to create the provider are the same. In fact I have used the same analytic that I used in the previous example. The only difference is that since the dashboard builder framework uses the OpenDoc URL format to pass the parameters I need to massage the parameter so that it can sent to WEBI as shown below.


In cell D2, I take the selection from B2 and use excel formula (="&lsSYear="&B2) to create the parameter for the data connection.

Now go to the data connections and add a new FS Data connection as shown below.


The trigger also has to be set in the usage tab, so that the data connection sends the new value whenever the value in the cell D2 changes.


Now export this to SWF and save it in the infoview portal.

Create the WEBI Report

The steps to create the WEBI Report is fairly simple. As shown below I have created a simple WEBI report which takes in the year as the prompt.


The only thing to keep in mind is the name of the prompt should correspond to the formula set in the provider analytic. For example in my case the open doc param formula in the provider analytics is ="&lsSYear="&B2 which corresponds to the Year prompt in WEBI.

Save WEBI report in the infoview portal and test the content linking as shown earlier.

If you want more details you can check out the user guides at

OpenDocument User Guide: http://help.sap.com/businessobject/product_guides/boexir31/en/xi3-1_url_reporting_opendocument_en.pdf
Dashboard Builder User Guide: http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_dashboard_user_en.pdf

Posted in BusinessObjects, Dashboard, WEBI, XCelsius | Tagged: , , | 4 Comments »

Creating live dashboards using QAAWS

Posted by Hemanta Banerjee on January 7, 2011

Happy new year to everyone. As the first post for the year I wanted to put something that was simple and common. In the last couple of months I have run into several scenarios of customers asking me the best mechanism to create live dashboards in XCelsius. As you know the only way to create live dashboards in XCelsius is by using Web Services. While you can create web services on your own the hard way, with the BOE platform there is a very nifty utility called Query As A Web Service (QAAWS) which allows you to create web services very easily from the universe.

QAAWS is a web service generator. It uses the query builder to essentially to build a query and publish the query to the platform and makes it available as a web service. Here is how you can use it.

Create the Query and Publish to the platform

1. Launch QAAWS and create a new query. Give it a name that is user friendly.


2. You can set additional parameters such as authentication mode and timeout at this stage. Usually we would keep them as default.


3. As you can see in the steps the next step is to select a universe. Based on your security setting you will be presented with a list of universes that you can use for building the query. The process of selecting the universe and building the query is the same as you would do when using WEBI.


In my example I have purposefully chosen a query which has a prompted filter. I can drive this prompt using a drop down box or other selectors from the dashboard.


As you can see my query has 1 input parameter and 4 output parameters. Now all I need to do it click on the publish button to publish the webservice to the platform.


The URL shown above is the URL for the webservice and I can use this in XCelsius to build my dashboard.

Using the Query in XCelsius

In the data connections dialog of XCelsius you can add a new live office connection. Here you need to enter the URL that we got from QAAWS.


We also need to bind the cells to both the input and output parameters. For the input parameter I have bound it to cell C3 of the spreadsheet as shown below.


The same way you need to bound the output to your spreadsheet. The only thing to keep in mind is that if your query will return multiple rows of data you would need to select a range of cells bigger than the maximum possible range as shown below.


For debugging purposes you can also get the number of rows returned by the query and bind it to a cell as shown below.


And after all of this voila you have a live dashboard…


Posted in Dashboard, QAAWS, WEBI, XCelsius | Tagged: , , | 6 Comments »

Drill down and Drill across in WEBI

Posted by Hemanta Banerjee on November 27, 2010

While BusinessObjects provides a fairly simple way to drill down using hierarchies there are times where this is not good enough. For example if we take the Motors example, lets say when the user drills down from the showroom, they want to see a report that displays the demographic information and models for that particular showroom. This is usually hard to do just by using hierarchies and this is where hyperlink drill down is very useful.

But before we go to Hyperlink drill down (or as I call it drill across), let us quickly cover hierarchical drill down. The screenshot below shows 2 ways to define hierarchies in the universe.


The natural hierarchies which is automatically defined by the universe is based on the order or objects within a class. For example in my universe I have Year->Qtr->Month->Date as my time hierarchy. Now if I enable drill in my report I can drill down to specific dates as shown below.


I can also define a custom hierarchy as shown in the figure 1 where I have combined the showroom and the model into a single hierarchy such that when the user drill from the showroom they can see the models in the showroom.


Now coming to drill across. For example when I click on the revenue below I want to jump to another report that shows the sales by model in that country for the selected year as shown below.


Setting that up is fairly simple. I have to create 2 reports as shown below.


The 2nd report accepts the showroom country and year as prompts. Now I have to go to the summary report i.e. Showroom by year and setup the drill across. You can setup a hyperlink drill down by right click on the cell and selecting “New Hyperlink” as shown below.


When setting up the hyperlink you would need to associate the objects from the summary report to be sent as parameters to the prompts in the detailed report as shown above.

Posted in BusinessObjects, WEBI | Tagged: , , , , | 1 Comment »

BOE Universe: Generating list of values based on data

Posted by Hemanta Banerjee on November 19, 2010

Let us take a simple scenario. Let us say we have a countries dimension which has all the countries. If we want to use this dimension table to get a list of countries where we have customers, as well as use it to get a list of countries where we have offices we can create 2 aliases COUNTRY_OFFICE and COUNTRY_CUSTOMER and create objects from the aliases. No issues till now, except if we try to get a list of countries where we have offices. Since we have used the master country table it will list all the countries irrespective of whether we have office there or not.

Let us see how it works below.


In the example above I have a country table and I use it for both client and showroom country. Now if I query for showroom country i.e. countries where I have showrooms here is the query produced.


As you can see it lists all the countries in the countries table which is not what we wanted. In order to get the correct list of countries I have to join with the SHOWROOM table so that the countries list is restricted based on the SHOWROOM dimension table. This is done by specifying that whenever the Showroom Country object is used in a query, the Showroom table must also be inferred in the FROM clause of the SELECT statement. Providing that the Showroom_Country table is joined to the Showroom table the object is then guaranteed to only return countries in which showrooms exist.


Making this change ensures that we always get the correct set of countries when we query for showroom countries.


While this does not seem that critical it becomes very important especially when we want the user to select the showroom country in a prompt for example. We only want those countries to be in the prompt list where we have showrooms and making this change will ensure that we always get the correct list.

Posted in BusinessObjects, Universe, WEBI | Tagged: , | Leave a Comment »

WEBI – How to display “Others” in country field based on Rank

Posted by Hemanta Banerjee on November 8, 2010

One of the frequent questions I come across is – I want to view the top 5, however I want everything else that is not in the top 5 to be placed in 1 bucket called others. I ran into the same question on the BOB board today and since it was simple enough here comes the solution.

Getting the rank within WEBI is quite simple using the RANK function. The syntax for the function is RANK(measure name; dimension name; top|bottom). For example of I wanted to find out the rank by sales I could define RANK([Sales Revenue], [City], top).

In order to make my life easier I have defined variable called Rank as shown below.


I can use that in a report filter to get only the top 3 regions by Sales Revenue.


Now to display all other regions I will use the WEBI function NOFILTER. This will return the total sales across all the regions irrespective of the filter. Using the formula below I can get the sales for all the regions that are not in top 3.


By placing this in the footer of the table I can get the report to show the sales for others.


Posted in BusinessObjects, WEBI | Tagged: , , | 2 Comments »

How to perform YTD (or any Period to date) design in the Universe

Posted by Hemanta Banerjee on November 5, 2010

Yet another post inspired by the BOBJ board. The idea is how to design a universe such that users could enter any date and get both the measure value for that period as well as YTD. Since we want to make it easy for adhoc users we need to do some design work in the universe to make it easy for the users performing adhoc analysis.

So I figured the easiest approach would be to define a separate set of measures for YTD similar to what I did for the YAGO computation in a previous post. So extending on the same example I followed a very similar approach and it turns out to be quite simple. All we need to do is make sure we are able to run multiple queries, once for getting the sales and another one that sums up the sales from the beginning of year to the selected date. So I know we have to define a separate context for the YTD sales, forcing the BI Server to automatically run 2 queries and join the results. That’s what I want to leverage.

1. To make my life easier in the universe I defined a separate reference table DATES_PERIOD that maps the date to its corresponding YTD start and end dates. This not only makes it simple, it also makes it possible for me use the same design for handling non standard calendars such as Fiscal calendar. Also if I want to do QTD or MTD instead of YTD I can use the same approach by just changing the start and end dates.


In this table I have gone ahead and filled up the start and end dates for YTD for every date in the DATES_TABLE my calendar table.

2.  In my universe I first go ahead and define an alias for the fact table called YTD_SALES. Now instead of joining it to my DATES_PERIOD (date dimension) table I have joined it to my DATES_PERIOD table using a complex join as shown below.


This ensures that I will always select all the sales from the time slice (start and end of YTD) rather than selecting a specific date. My universe is as shown below.


In my universe my time dimension objects such as Date or Qtr are driven by the DATES_TABLE. So in order to tie everything up I have joined the DATES_PERIOD to the DATES_TABLE on the date. This ensures that when the user selects the date, the corresponding period will be selected from the DATES_PERIOD and the BI Server will return the sales that fall in that period. This is the key part of the design.

Now I can setup up the rest of the joins with the rest of the dimension tables.

3. Now I have to define a new context for YTD as shown below. This is needed to make sure that when the user selects from Sales and YTD sales they are sent as separate queries.


After setting up the contexts I can define the YTD Sales revenue by pulling in the appropriate field from the YTD_SALES alias table.


Now checking to make sure that the logic is OK. I define 2 queries, YTD Sales till Dec 31-2004 and the sales for 2004. If the logic is correct both should come out same and it does.


Also I can pull them in the same query if I want. I know that there is a sale on 15-Mar-2004. Filtering on that date gives me both the sales value for that date as well as YTD sales.


The reason I love this design is because very versatile and it can be used for any period to date. The only thing to note is that it will work only if the user selects a date. If the user selects a Qtr or Month then the YTD value will be garbage. If you want to prevent this then you can force the user to select a date using a prompt.

Posted in BusinessObjects, Period to Date Functions, Time Sliced, Universe, WEBI, YTD | Tagged: , , , , , , , | 1 Comment »

How to do Year Ago or Prior period type comparison in WEBI

Posted by Hemanta Banerjee on November 3, 2010

Again a post inspired by the BOBJ board which talks about a generic requirement whereby making it easy for adhoc users to perform relative time period based analysis as easy as possible. The users should be able to select the date using a prompt or otherwise and ask for values for either the selected period or some other reference period such as Current Year Last Week or Prior Year by simply selecting a different measures named as such from the Universe.

This is actually quite simple once you get around to designing it. All we need to do is make sure we run multiple queries once for each time period and then join the queries at runtime in the BI Server. WEBI provides such functionality in the form of contexts. For example if we define the current period and YAGO (year ago) in 2 separate contexts, then when we pull Sales and YAGO sales in the same report BI Server will automatically run 2 queries and join the results. That’s what I want to leverage.

My example shows how to setup 2 measures, Sales and YAGO Sales. However this can be done for any number of measures as well as any number of reference periods as the process is quite simple. So here is what I have done.

1. For each relative time period we need to define an alias of the fact table in the universe. For example I need current period and YAGO therefore I have 2 fact tables – Sales and YAGO Sales.


2. It is also very important to have a proper dates/calendar table that you can use for your time based calculations. In my schema it is the DATES_TABLE with a structure as shown below.


3. Now join the 2 facts to the dates table. The main fact table for current period will be joined normally to the dates table, however the YAGO sales will be joined to the dates table with a lag.

image image


Ofcourse you would also need to define all the other joins between the YAGO_SALES fact and the other dimension tables for completeness. Now that the 2 join conditions have been defined as you can define the measures from each of these tables. When user say runs the query for October 2010 then Sales revenue will be the value for Oct-2010, however YAGO will be Oct-2009 since YAGO sales will join with the date table with a 1 year lag due to our join condition.


Now in order to force the 2 queries we need to define the contexts – 1 for each fact table. This will resolve the loops as well as force the BI Server to issue 2 separate queries to the database.


Now in the query as will see the BI Server will issue 2 queries one for each context and combine them in the result set.


The beauty of this approach is that it is automatically level based. If the user selects a date in the prompt the results will be for same date last year, for month it will be same month LY, for Qtr will be same Qtr last year and for year will be last full year.

You can use the same mechanism to define last week or last month.

Posted in BusinessObjects, Prior Period, WEBI | Tagged: , , , , , , , | 2 Comments »

How to create start and end date prompt in WEBI report

Posted by Hemanta Banerjee on November 1, 2010

Ran into this question on the BOBJ board asking how to create a WEBI report which can prompt the user to enter the start and end dates for running a report. Seems quite simple and useful so here is the solution.

I am using the adventure works universe to illustrate the process. The query I have created is show below.


Created a simple query which prompts the user to enter the year and months for analysis. Note that I will only allow the user to select a single year. If you want to do cross-year analysis then you would need to define a separate object in the time dimension as “Year-Month” which would allow the user to perform cross year analysis. When I run the report I get prompted to enter the year and months for the report.


And the results are as shown.


Posted in BusinessObjects, WEBI | Tagged: , , , , , | Leave a Comment »

%d bloggers like this: