Irfan's World

May 14, 2011

How to create a filter in Web Intelligence document that reflects on multiple report tabs..?

In this post, I am going to explain how to create a variable filter in a Web Intelligence document, that when applied on the first tab reflects on all the other tabs in the document,  In other words, the changes are made in the local document variable at one place and the changes are applied to all the tabs where this filter is used in the document.   

Let’s say we have a Web Intelligence document with 4 different tabs and each tab with different grouping on dimensions.  Similar to the following star bullet points:

   Tab 1 – Displays Revenue by Year
  Tab 2 – Displays Revenue by Year & Quarter
  Tab 3 – Displays Revenue by Year, Quarter & State
  Tab 4 – Displays Revenue by Year, Quarter, State & City

I am going to demonstrate it by using the standard eFashion Universe.  After dragging the required objects, My query panel looks like below and note there is no query filter:

image
When I run the above query the following results are displayed from each year 2004, 2005 and 2006.

image 
Create a document variable

Create a variable by name “?_Tab_Filter” and put the following code =If ([Year]="2004";1;0).  Next apply this filter to the first tab and choose to filter on “1” (i.e.  to display only those records where the value is 1).

image 
Now copy this tab 4 times to deliver the four reports above and keep only the required objects and remove objects that are not required in each tab.

Tab 1
image 
Tab 2
image 
Tab 3
image 
Tab 4
image

Remember that when you copy the first tab as different tabs for this kind of requirement, the variable that is added as a filter(?_Tab_Filter) will also get copied along with it to each tab in the document. In my case here, I want to filter on 2004 as my Year, Hence it displays data related to Year=2004 in all the tabs. I repeat all the tabs whether you have 1, 2, 3…. 50 tabs or more.  This works.   Cool

Now if you want to see the data for the Year = 2005 instead of 2004 in all the tabs, then edit the variable ?_Tab_Filter and change the code to =If ([Year]="2005";1;0) and click OK to modify the variable.  This change will reflect automatically on all the tabs where this filter is used and the data is displayed according to the value selected for the dimension Year, here I am changing the variable to 2005 from 2004.  All the tabs now displays data for 2005 including blocks if you have any.  Isn’t this feature is superb ? Yes it is.   Give it a try !!!

Advantages:

    One variable filters all tabs.
    Can be applied to all the blocks (table, crosstab, charts) in a tab.
    Additional dimensions can be added or removed to the filter.    (ex: =If ([Year]="2005" and [State]=”California”;1;0) )
    Assists in bursting the full document  or separate it individually so that all the tabs displays only filtered data.  (ex: a store report for each store manager)
    No manual intervention necessary to go to each tab and change the filter value.

Disadvantages:

  All the required data has to be in the report.
  User needs to change the filter values.   ( no drop down option …Sad, so some knowledge of WebI syntax is necessary)

     Without a doubt in my mind, I say that it is one among the powerful feature’s of SAP Web Intelligence.  That is all for today and thanks for stopping by.

April 26, 2011

Apache Tomcat 5.5 Service installed with BusinessObjects 3.1 does not start on Windows 2003 Server ?

I was trying to install Business Objects 3.1 (32 bit) on Windows 2003 Standard Edition (32 bit).  This system does not have any web servers nor any service packs.   While installing Business Objects 3.1, I selected the option, Install on Apache Tomcat 5.5 and deploy war files.   After the completion of Business Objects installation, I thought everything is running fine but found that the Apache Tomcat 5.5 (Web/app server) is not starting correctly.  Due to this issue, I was unable to login to CMC Launchpad or Infoview.

Symptoms:

     When tried to start Tomcat in CCM,  it starts and stops immediately.

     When tried to start from Control Panel, Services, it started few times by displaying “Starting” and after few seconds service status disappears.

At first, I thought this might be a Windows Service Pack issue.  

1) When checked in Event Viewer it displayed the following error. 

image

2) Downloaded SP2 for Windows 2003 and installed.
    To find out whether your system has already SP2 installed, you can either go to Add Remove Programs or Use the following spCheck Tool from Microsoft.
   
http://support.microsoft.com/kb/279631 

    I tried starting the Apache Tomcat again but still in vain, no success yet. Thinking

3) At this time, I started looking the other way around, that is on the application server Apache Tomcat side.   Looked into Tomcat log files found at the following path on my machine D:\BusinessObjects31\Business Objects\Tomcat55\logs.  (Check the correct location for your machine, default location would be [drive]:\Program Files\Business Objects\Tomcat55\logs),   I found the following error shown below from jakarta_service_date.log. 

image

This gave a little clue that the Apache Tomcat 5.5 service is not able to initialize itself and shuts down immediately,  and that is nothing but it cannot find the JRE on my machine, hence cannot initialize itself.  To resolve the issue, I did the following two things:

a) Setting up the JAVA_HOME
    
       My JAVA_HOME directory location was at D:\BusinessObjects31\Business Objects\javasdk\jre\bin\server.  Following screenshot shows the JAVA_HOME setting.

image

      I also made sure that javaw.exe is also available at the same location “D:\BusinessObjects31\Business Objects\javasdk\jre\bin\server”  when Tomcat 
          
initializes JVM.

SNAGHTML1069b99
b) Setting up Apache Tomcat to look for jvm.dll or javaw.exe at the correct location.

    To do this, I need Apache Tomcat Service name.  I found it from here Start –> Settings –> Control Panel –> Administration Tools –> Services, Clicking on 
    Apache 
Tomcat Service to open its properties window,  On the General Tab, I was able to find the Service Name registered on my server, that is BOE120Tomcat.

image

        Opened a command prompt window and travelled to Tomcat55 bin directory, and then executed the following command tomcat52.exe //ES//BOE120Tomcat.
          Please see the screenshot below.

       After the command execution successful, It displayed a window of Apache Tomcat 5.5.20 Properties shown below, I was able to change the Java Virtual Machine 
          path including the initial and max. memory that  it can use 128MB and 512 MB respectively and then I clicked ‘OK’ to close the window.

 

image

         Rebooted the Server and directly went to Services in Administration Tools to Start the Apache Tomcat 5.5 service.  What I see you can’t imagine… the service starts 
          without any issue. 

         Enabled all the BOE Services, Logged onto CMC Lauchpad, Scheduled few documents in Infoview … everything is working perfectly.Wave

Thanks for stopping by and feel free to discuss any alternative solutions through comments.

April 23, 2011

Crystal Reports 2008: Failed to open the connection. test_oracle Details: [Database Vendor Code: 12154 ] – Resolved

Filed under: SAP Crystal Reports — Irfan @ 12:13 am

After spending lot of time on Google to fix the connectivity issue on Windows 7 64bit between Oracle 10g and Crystal Reports 2008 SP3, I thought I should write this post that may assist some of my readers including me for future reference down the road.  There is not much information available on the web and not any documentation about this issue.  The above error appears sometimes in Business Objects 3.1 SP3 Infoview or when you connect Crystal Reports 2008 Designer Tool to Oracle 10g database.

Remember the following steps:
a) Crystal Reports 2008 SP3 is a 32 bit application.
b) Business Objects 3.1 SP3 is a 32 bit application.
c) So, install  Oracle 10g 32 bit or 64 bit, But install Oracle 10g client connectivity software of 32 bit only to interact with Crystal Reports 2008.
d) If you want to use ODBC, you can connect using Microsoft ODBC drivers that are installed by default or you can also download Data Direct 5.3 drivers. or try this link https://websmp230.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/bobj_download/main.htm

Following is my environment on which connection is successful:
Operating System:   Windows 7 Ultimate (64 bit)
Database:                Oracle 10g 10.2.0.3 (32 bit)
Reporting Tool:       Crystal Reports 2008 SP3 (32 bit)
Enterprise:               Business Objects 3.1 SP3 (32 bit)

Using ODBC
1) create a 32 bit system DSN on the client machine (or the server).
    On a 64bit operating system, you will find 32 bit ODBC setup file in the following path [drive:/]Windows/sysWOW64/odbcad32.exe

2) Open Crystal reports designer tool, and try connecting to the System DSN created in step 1.  Check the following screenshot, Crystal Reports 2008 SP3, Connection tested with ODBC and it is successful on my machine.  Oh what a relief !!

SNAGHTML7fcf80

Using TNSNAMES.ORA
2) Check the following screenshot, Crystal Reports 2008 SP3, Connection tested with TNSNAMES.ORA (i.e. Oracle Server)
 

SNAGHTML772f8a

Make sure that you install all the correct 32 bit versions of Crystal Reports 2008, Business Objects 3.1 and Oracle 10g to be compatible with each other. The report should work fine in Infoview and local PC.

Share your thoughts or any alternative solutions to resolve this error. Have a Good day.!!  Smile

March 5, 2011

Differences between Crystal Reports XI R2, Web Intelligence XI R2 and Web Intelligence XI 3.0

A quick reference sheet that shows the differences between BusinessObjects reporting tools.

image

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.

February 18, 2011

How to display a database column values in a single cell in Web Intelligence Rich Client ?

In this post, I would like to show you how to display an object value in a single cell separated by a delimiter in a WebI report.

I have a table name called Account in Xtreme database and it has the following values,  the requirement is to display all the account names in a single cell in the report.

image

 

This requirement can be fulfilled if we have access to Universe Designer in which three following objects needs to be created with the syntax shown, I am using SQL Server as my database.

    Column Value
    Delimiter
    Actual Object Name – Single Line

1) Create a ColumnValue object as below:

SNAGHTMLae414a1

2) Create a Delimiter object as below:

image

3) Create a Account Name object as below:

This step needs two things, make sure the definition of the object looks like below plus the qualification should be a measure with projection as SUM

SNAGHTMLb151b37

 

Objects qualification and projection

image

All the ground work is done, now I will use the object in a sample report.  Let’s see what it returns ?

image

The report output is shown below…. voila !!!

image

That is all for today.Wave

February 17, 2011

How to get all indexes of table(s) in SQL Server 2008?

Filed under: SQL Scripts — Tags: , , , , — Irfan @ 11:20 pm

1) To get the indexes from a given table and schema.

Use the following script to get all the indexes for a given table and schema name.

DECLARE @TableName SYSNAME,
      
@SchemaName
SYSNAME

SET @TableName = 'Employee'
SET @SchemaName =
'HumanResources'
SELECT
  
i.OBJECT_ID
,
  
i.index_id
,
  
i.name IndexName
,
  
OBJECT_NAME(i.OBJECT_ID) TableName
,
  
c.name ColumnName
,
  
i.index_id
,
  
df.name FileGroupName
,
  
i.fill_factor
,
  
i.is_unique
,
  
i.type_desc
,
  
ic.is_included_column
,
  
schema_name(o.schema_id)
SchemaName
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
  
ON      i.index_id = ic.index_id
AND
          
i.OBJECT_ID = ic.
OBJECT_ID
INNER JOIN
sys.columns c
  
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
INNER JOIN
sys.database_files df
  
ON i.data_space_id =
df.data_space_id
INNER JOIN
sys.objects o
  
ON i.OBJECT_ID = o.
OBJECT_ID
WHERE
  
OBJECT_NAME(i.OBJECT_ID) = @TableName
AND
  
schema_name(o.schema_id) = @SchemaName
AND
  
i.index_id <>
1
ORDER BY
i.index_id

Following is the result when I run the above query for HumanResources Schema.

image

   2) To get all the indexes for a complete schema you can comment the line that checks for the TableName in the WHERE clause.

   DECLARE -- @TableName sysname,
      
@SchemaName
SYSNAME

   -- SET @TableName = 'customer'
   SET @SchemaName =
'sales'
   SELECT
     
i.OBJECT_ID
,
     
i.index_id
,
     
i.name IndexName
,
    
OBJECT_NAME(i.OBJECT_ID) TableName
,
    
c.name ColumnName
,
    
i.index_id
,
    
df.name FileGroupName
,
    
i.fill_factor
,
    
i.is_unique
,
    
i.type_desc
,
    
ic.is_included_column
,
    
schema_name(o.schema_id)
SchemaName
  FROM
sys.indexes i
  INNER JOIN
sys.index_columns ic
     
ON      i.index_id = ic.index_id
AND
             
i.OBJECT_ID = ic.
OBJECT_ID
  INNER JOIN
sys.columns c
    
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
  INNER JOIN
sys.database_files df
    
ON i.data_space_id =
df.data_space_id
  INNER JOIN
sys.objects o
    
ON i.OBJECT_ID = o.
OBJECT_ID
  WHERE
    -- object_name(i.object_id) = @TableName AND
   
schema_name(o.schema_id) = @SchemaName
AND
   
i.index_id <>
1
  ORDER BY
i.index_id

image

The above script is useful to find table column indexes and I use it when I develop filters or objects that can go to WHERE part of the query panel while developing reports using Web Intelligence (Rich Client) or Desktop Intelligence and it boost the performance of the query and that is all what we need at the end of the day.

February 5, 2011

Distribute a System DSN to other machines in your environment

Filed under: Administration — Tags: , , , , , — Irfan @ 6:54 pm

If you are working in network services or doing a kind of administration work using Windows based systems, some times you may come across this kind of work, where it involves setting up new ODBC system DSN’s for a group of 5 or more developer’s to connect to servers, and where the environment is huge with different database servers to connect, develop, test their development.  It really becomes tedious to go to every developer’s machine and do the ODBC setups.    There is a quicker way to move the DSNs by hacking the registry, and transfer all the ODBC’s from machine A to machine B

Note : First take the backup of the registry and save it and keep it in a safe place (on a thumb drive or on network drive).  This is important because even if your system crashes you can restore the ODBC’s from this file.

Following are some of the steps that I have outlined to take the ODBC’s on one machine and transfer to other.

Go to machine A and do this:

     Go to Start menu on machine A.
     Click ‘Run’ or open a command prompt window.
     Enter regedit and then press enter, it will open the registry window
     Go to the following system registry key path and select it.
     HKEY_LOCAL_MACHINE\SOFTWARE\ODBC                                                 (this is where all the ODBC DSN’s are saved)
    
Now, Go to File –> click Export.                                                                     (export Registry File window opens)
     Enter a filename ‘allodbc.reg’ and save the file                                                  (on a Network location, say for instance N:\ODBC )

Go to machine B and do this:

      Go to Start menu on machine B.
     Click ‘Run’ or open a command prompt window.
     Enter regedit and then press enter, it will open the registry window
     Go to the following system registry path and highlight it:
    
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
     Go to File –> click Import.                                                                               (import Registry File window opens)
    
Select the System registry file ‘allodbc.reg’ that you exported to N:\ODBC (or get it from your location) and Click Import.

You will now see all the ODBC DSNs from the machine A when you go to Start –> Settings –> Control Panel  -> Administrative Tools –> Data Sources (ODBC).   Talk to the hand

January 30, 2011

How to Load Unicode Data from Excel to SQL Server that supports non Unicode format ?

Filed under: SSIS — Tags: , , — Irfan @ 9:23 am

Requirement (2): Load the data from an excel file to a non Unicode SQL Server 2008 database table. 

In my earlier post  here , we dealt with Unicode to Unicode format. Today, I will show you how to load Unicode data from a excel file to non Unicode table.

We do need both excel(source) and OLE DB (destination) connection managers. When we load Unicode data to non Unicode format database table, it throws the following error in SSIS Business Intelligence Development Studio (BIDS).

Error Message : Cannot convert between Unicode and non Unicode Character string….



To resolve the above error and move forward, we first need to convert the Unicode characters that are coming from excel source file and send it to the target component.

Moreover, SSIS does not even allow you to run the task without solving the error.  How do we resolve this ..? 

There is a special task called "Data Conversion" available in BIDS in Data Flow transformations. We will use this task to convert the data from Unicode to non Unicode format.

First insert the "Data Conversion" task between the source and target, after the insertion our Data Flow looks like this.




Now double click the Data Conversion task and change the data type as shown below.  The following screen shot shows the data type’s Converted from DT_WSTR(Unicode) to DT_STR(non Unicode string) 🙂



Click OK to close the Data conversion transformation editor. Go back to the Data Flow OLE DB Destination and map the columns correctly as shown below in .

We have to use the new columns that are non Unicode coming out of “Data Conversion” task and map to the Destination columns of the target table.



Now, Close the Destination by Clicking OK and execute the task by right clicking and selecting Execute Task from the Context menu. It will execute successfully and loads all the rows in the destination table.
Following screen shot displays the no. of rows read from the source, converted to string and loaded them successfully in the target table.



You can check the data in the target table either from BIDS or SQL Server Management Studio.  Hope I’ve explained clearly.  Thanks for reading.

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 !!!.

« Newer PostsOlder Posts »

Create a free website or blog at WordPress.com.