Irfan's World

September 15, 2011

How to display column values in a single cell in Web Intelligence using Oracle database ?

A similar post has been posted earlier using SQL Server here.  The steps for creating such a report are same for Oracle database.

I am using Oracle 11.2.0.1.0 with Business Objects XI 3.1 – Version 12.3.0.601 on Windows 2003 O/S.

Requirement is to the DeptNo and all its employee names in a single line by each department number.

Create a dimension object in the Universe and name ColumnValue as below:

image 

Create a dimension object as a Delimiter for separating each value as below:

image

Next, Create the actual dimension object EmpName – SingleLine that you want to display on a single line in Oracle as below, In this example I am using the ename column from emp table in scott schema:

image Set the properties of EmpName – SingleLine Object as shown below:

image

The WebI report generates the following SQL

image

When refreshed the report displays the following results.

image


To remove the last comma use the LEFT function from Web Intelligence tool and extract only LENGTH – 1 characters.  That is all for today.

February 27, 2011

What’s new in Business Objects 3.1 SP2

In Business Objects 3.1 SP2, you will see some enhancements in the following products:

        1. Translation Manager
        2. Live Office
        3. Web Intelligence
        4. Query as a Web Service
        5. Universe Designer
        6. Report Conversion Tool

1) Translation Manager:

      In universes:  You can now translate custom hierarchy metadata.
     In Web Intelligence, you can translate:
                Prompts
                Report names
                Query or data provider names
                Local variable names
                Alerter names and descriptions
                Content in a Web Intelligence formula
                     Formats. (Ex : You can now create and save multiple custom formats in a document.)

2) Live Office

In this release, Live Office provides new functionality as detailed below:

     Modify Object Refresh Order
                  (Ex : Live Office objects can now be refreshed in the user specified order. When there are multiple objects in a document, the user can modify the refresh order of the objects. This allows the data returned by an object to act as the prompt or parameter 
                  for subsequent object.)

     Queries: Using an object as a filter.
                              (Ex: When inserting a query into your Live Office enabled document, you can now filter the query results with an object.)

3) Web Intelligence

       Forward compatibility
               
Web Intelligence documents created using SAP BusinessObjects XI 3.1 SP2 cannot be opened in SAP BusinessObjects XI 3.1.

       Backward compatibility
               
Documents created using SAP BusinessObjects XI 3.1 can be opened in SAP BusinessObjects XI 3.1 SP2.

       Input controls
               Input controls provide a convenient way of filtering and analyzing report data. Some of them are list boxes, radio buttons and sliders. These can be assigned to report elements(blocks, section headers) Selecting values in an input control filters the values in the
              
associated report element. Also Tables and charts can themselves be defined as input controls. Selecting values in the table or chart filters the values in the associated report element.  For example, a variable in a formula can be assigned to a slider control.
               Changing the variable with the control allows a user to examine the effects of different variable values on the formula result.

image

 

     BI services

          BI services are web services that publish Web Intelligence content over the web. They are defined and accessed using the standard WSDL and SOAP protocols.

     More translatable content in multilingual reports in WebI

               The following report elements can now appear translated in multilingual Web Intelligence reports:

                    Query name
                    Report name
                    Free cell content
                    Local variable name
                    Alert name, description and alert message
                    In report’s structure, object name
                    Text in the new GetLocalized() function

    New locale- and localization-related functions

          The Web Intelligence formula language contains three new functions and they are: 

          GetPreferredViewingLocale returns the user’s Preferred Viewing Locale.
          GetDominantPreferredViewingLocale returns the dominant locale.
          GetLocalized returns the translation of report text in the user’s
Preferred Viewing Locale. It also allows report creators to provide notes for translators to aid them in translating text in Web Intelligence reports.

image

    Query on Query 

          Query on query allows users to define a query condition based on an object resulting from another query.

   PDP-API Exposition

         
Personal data provider(PDP) is a feature that enables Web Intelligence users to build documents on another data source as the Universe. The following are the PDP’s which are supported:
                        Excel
                        Text files – *.txt (usually Tab delimiter)
                        Formatted text files – *.prn (Space delimiter)
                        ASCII files – *.asc (Tab delimiter)

   Fold and Unfold

This feature will allow Web Intelligence Reports Viewers/Editors to be able to Fold/Collapse Information in sections.

image

    Query Filter on QaaWS and LiveOffice

         This feature will allow users to design query prompts, using universe objects as comparison terms. The aim is to extend the prompt feature scope, to the level of Web Intelligence query panel, so QaaWS users are able to easily design and consume efficient queries, as  
         they are available with WebIntelligence.

   Rich Client PDP – Web Intelligence Web Services
             Generic Web Services Plug-in
             Business Intelligence Services Plug-in

   Customize Web Intelligence functions with Extension Points
             You can now use calculation extensions to customize the Web Intelligence reporting functions so that they follow any logic you require.

4) Query as Web Service

      Business Intelligence (BI) content is usually bound to a specific user interface of BI tools. Query as a Web Service changes lets BI content be delivered as a query to any user interface that can process Web Services.

5) Designer

       New SQL parameter SMART_AGGREGATE allows use of most detailed aggregate tables
       New @Variable settings for retrieving reports and displaying information in different locales. The @Variable function can now be used to define Language (Locale) settings using the locales variables like GetPreferredViewingLocale, GetDominantPreferredViewingLocale
           and GetLocalized

        @Prompt editor
                    This feature applies to both relational and OLAP universes. It aims at providing users with a User Interface guided workflow to build the prompt definition and correctly enter List Of Values (LOV) and default values in that definition.

       MDX dictionary for OLAP universes
                  
The object definition editor includes a dictionary of MDX elements that can be inserted in the object. The MDX elements are dependent on the data source. The available parameters are stored in the relevant parameters (.PRM) file.

       New OLAP parameters and controls at universe generation time
                 
This feature offers users some options to control and configure the universe generation to obtain the expected universe and relieve users from having to remove unwanted objects in the universe after the generation. Those options apply also at update (Refresh 
                 
Structure):

                  Replace prefix.
                  Generate Technical Name (Tech_Name) as detail.
                  Rename Level00 to All
                  Generate level 00
                  Set measures aggregation to delegated

6) Report Conversion Tool

      Query on Query filter is fully converted
                
Reports that contain the Query on Query feature will be fully converted.

      Fold/Unfold feature is partially converted
                
Reports with sections that contain the Fold/Unfold feature are fully converted.
                      Reports with tables, crosstabs, and breaks that contain the Fold/Unfold feature are partially converted.

      Converting the data in reports to text format
               
Report Conversion Tool provides an option with which a user can convert Desktop Intelligence document data format into text format.

Most of the information in this post is gathered from Business Objects User Conference that I attended.  There are many other products from Business Objects XI 3.1 suite that has been enhanced but not listed in this post.

January 27, 2011

Custom Date Format in Universe Designer for WebI reports

Date is an important attribute in doing business, capturing events etc.   As you know there are lot of different date formats used in different countries.  For example, In USA we use MM/DD/YYYY format, in UK they use DD/MM/YYYY etc.  A request came to me from one of my client to format a date that displays month name, something  similar  as "7th of February 2005"  or "23rd of February 2004" format, as they need to convey some information through a business letter to their customers living around the world.  The client’s reporting system mostly uses Web Intelligence reports.   My first idea was to train the users on how to create variable in the report and use it, Later I have decided that I should create an object in the Universe and came up with the following solution.  It is not that difficult once you understand what the code does to create a Universe object to support this requirement.

We have the following data in the SQL Server 2008 database, which shows the date format from the database.

SQL Code for the Universe Object (SQL Server) :

CASE
  
WHEN DATENAME(DAY, [Date]) IN (1,21,31) THEN
       
DATENAME(DAY, [Date]) + 'st ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
  
WHEN DATENAME(DAY, [Date]) IN (2,22) THEN
       
DATENAME(DAY, [Date]) + 'nd ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
  
WHEN DATENAME(DAY, [Date]) IN (3,23) THEN
       
DATENAME(DAY, [Date]) + 'rd ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
  
ELSE
       
DATENAME(DAY, [Date]) + 'th ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
END

Parse the Universe Object to make sure that the syntax and the code is correct, test the object on few reports before it goes to production.

Let’s test what data is returned by this object.

Hope this tip helps. See you later !!!.

January 24, 2011

SQL Server Dates – Part 2 (Quarter)

In this post, I will be showing you how to generate Quarterly dates using getdate() database function.

Quarter Dates

The following code will generate First Day of the Previous Quarter, Last Day of the Previous Quarter, First Day of the Current Quarter, Last Day of the Current Quarter.

select ‘First Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())-1,0)
select ‘Last Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)-1
select ‘First Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)
select ‘Last Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)-1
select ‘First Day of the Next Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)

Wait for my next post for more Quarterly dates. Click for Date Part 3

SQL Server Dates – Part 1 (Year)

To display today’s date in SQL Server 2008 select GETDATE().

Following are some of the dates that can be used in SQL Queries to fulfill the end user needs while retrieving data from the database or creating custom objects for delivering reports in BI tools.

select ‘First Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0)
select ‘Last Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)-1
select ‘First Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)
select ‘Last Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1
select ‘First Day of the Next Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)

I will be continuing on how to generate Quarterly dates in my next post Quarter Dates

January 23, 2011

SQL Server Dates – Part 3 (Quarter ..)

Quarterly Dates continued … Following are mostly used Quarterly Dates in Data Warehousing and Business Intelligence reports.

Previous Year Quarters:

select ‘First Day of the Previous Year / First Quarter :’ , DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))
select ‘First Day of the Previous Year / Second Quarter :’ , DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))
select ‘First Day of the Previous Year / Third Quarter :’ , DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))
select ‘First Day of the Previous Year / Fourth Quarter :’ , DATEADD(Q,3,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))

Current Year Quarters:

select ‘First Day of the Current Year / First Quarter :’ , DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Second Quarter :’ , DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Third Quarter :’ , DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Fourth Quarter :’ , DATEADD(Q,3,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Third Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,3,getdate())-1,0)
select ‘Last Day of the Current Year / Second Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,3,getdate())-1,0)-1

Blog at WordPress.com.