Display of timestamp data as quarter in report

[Former Member]

Hi,

I am using BO XI R3.1 version and webI rich client and trying to display the output of object scheduleenddate (9/1/2009 12:00:00 AM) as quarter in report. I need the output as Q1,Q2,Q3, Q4. The database am using is sqlserver 2005 and this field scheduleenddate is defined as datetime,null. I would like to change the definition of object in universe so that the output should come as Q1,Q2,Q3.

I've tried using the following in the select clause of object.

Case when DATEPART(QQ,Scheduleenddate) =1 Then 'Q1'

when DATEPART(QQ,Scheduleenddate)=2 Then 'Q2'

when DATEPART(QQ,Scheduleenddate)=3 Then 'Q3' else 'Q4' end

have tried in the object definition with the object type as 'date' but it given message as 'The expression type is not compatible with object type' so i changed it to character and then receiving the error message as follows:

Incompatible data

There is a incompatibility between the data that has a 'character' type and the object that has a 'Date' type.

To avoid this incompatibility, your data of the type 'Char' must be presented according to the Date format ''mm/dd/yyyy HH:m:s'', for example, '03/30/2010 10:30:59'.

Any suggestion. Could anyone please advice or suggest any solution.

Thanks in advance,

Eswar

[Former Member]

Hi,

Try to write the expression like this:

'Q' +  CAST (datepart(Q,Scheduleenddate) as VARCHAR)

...And set the object data type to char.

Didier

[Former Member]

Hi,

I've used the following code

Case when DATEPART(QQ,Scheduleenddate) =1 Then 'Q1'

when DATEPART(QQ,Scheduleenddate)=2 Then 'Q2'

when DATEPART(QQ,Scheduleenddate)=3 Then 'Q3' else 'Q4' end

I changed the data type and it is now working.

Thanks for your help.

Eswar

[arijit_das]

Try

IIf(datepart('q',Scheduleenddate)=1,'Q1' ,IIf(datepart('q',Scheduleenddate)=2,'Q2',IIf(datepart('q',Scheduleenddate)=3,'Q3','Q4')))

with object type character.