Irfan's World

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

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 25, 2011

How to load data from an excel file using SSIS 2008 when target table supports Unicode ?

Filed under: SSIS — Tags: , , , , — Irfan @ 12:47 am

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

 
    Let’s first see what we have. We have an excel file in Office 2007 format that has 57 rows to load into a destination table in the Target database using SQL Server 2008 Integration Services.

Source : Excel file in MS Office 2007

No. of rows : 57
Destination : SQL Server 2008 Database

No. of rows after the load : 57

 

This would be an easy task, we can load the data without any issues. The source data file looks like below which has about 7 columns:

Now to load the data using SSIS, you need to install SSIS (SQL Server Integration Services) on you machine. 

    Open BIDS
    Create a Package and give it name and save it
     Create one excel Connection Manager and one OLE DB Connection Manager
    Create a Control Flow in which you will create a Data Flow. (This data flow is shown below)
  

And, the SSIS package developed in BIDS (Business Intelligence Development Studio) to load the data from excel file looks like below, It has a data flow that contains a connection to an excel data source file plus it has an OLE DB connection that connects to our Target table in the Destination database.

The destination table has the following data type’s that stores Unicode characters without any issues. Note to the readers, SSIS 2008 when reads first 8 records from the excel file it decides the data type of that column, Here the excel file string columns are read by SSIS as Unicode strings and our Target table is having Unicode data type’s which supports Unicode characters that can be stored easily. So the data type’s in source and target matches.

When the job is executed in BIDS (Business Intelligence Development Studio) it loads all the data to the destination table successfully.

Unicode ——-> to ——> Unicode (57 rows are loaded and the Job works perfectly, No worries) 🙂

Wait, what happens when we try to load the same excel file data to a non Unicode target table ?

<!–[if get mso 9]> Normal 0 false false false EN-US X-NONE AR-SA <![endif]–><!–[if gte mso 9]> <![endif]–> <!–[endif]–>

Source : Excel file in MS Office 2007 format

 

No. of rows : 57

Destination : SQL Server 2008

 

No. of rows after the load : 57

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 6 (Year, MI, SS)

Filed under: SQL Server 2000/2005/2008 — Tags: , , , , — Irfan @ 10:34 pm

Today, I am going to show you how to add hours, minutes or seconds to a datetime field GETDATE() function from SQL Server 2008 always returns Current Date and Time.  I am referencing Current Date and Time as "now" in this article.

To display the current date and time, type the following query in SQL Server editor:

SELECT GETDATE() as ‘Current Date and Time’

Hours

Add 4 hours from now.
SELECT DATEADD(HOUR,4, GETDATE()) as ‘Add Four hours from now’

Subtract 6 hours from now.
SELECT DATEADD(HOUR,-6, GETDATE()) as ‘Subtract 6 hours from now’

Minutes

Add 10 minutes to the current date and time
SELECT DATEADD(MI,10, GETDATE()) as ‘Add 10 min to now’

Subtract 60 minutes from the current date and time
SELECT DATEADD(MI,-60, GETDATE()) as ‘Subtract 60 min from now’

Seconds

Add 50 sec to the current date and time
SELECT DATEADD(SS,50, GETDATE()) as ‘Add 50 sec to now’

Subtract 60 minutes from the current date and time
SELECT DATEADD(SS,-40, GETDATE()) as ‘Subtract 40 sec to now’

Following are the results when we run the above queries in SQL Server Management Studio.

SQL Server Dates – Part 5 (Week)

Filed under: SQL Server 2000/2005/2008 — Tags: , , , — Irfan @ 10:28 pm

Here are some of the examples for generating weekly dates that are helpful to create weekly reports.

Week Dates

select ‘First Day of the Previous Week :’ , DATEADD(WW,DATEDIFF(WW,0,getdate())-1,0)

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

SQL Server Dates – Part 4 (Month)

Most of the times when we develop Business Intelligence reports for trending, like trend reports, cross tabs or charts, compare analysis ( current month vs. previous month, current rolling 6 months vs. previous rolling 6 months, current rolling 12 months vs. previous rolling 12 months) which are widely used in BI industry.

Following are some date examples of how to generate those dates using GETDATE() function from SQL Server 2008 database that can be beneficial for your report development. These date examples are also very useful for scheduling reports. (i.e. something we call dynamic dates as parameters for scheduling the reports)

Month Dates

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

Rolling 12 Months

select ‘Current Rolling 12 Months / Begin Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-11,0)
select ‘Current Rolling 12 Months / End Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1
select ‘Previous Rolling 12 Months / Begin Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-23,0)
select ‘Previous Rolling 12 Months / End Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-11,0)-1

Wait for my next post on Weekly dates.

Older Posts »

Blog at WordPress.com.