BO XI R3 Date format

[Former Member]

A column in a calls table in the database has the date stored as dd/mm/yyyy. We need to be able to report the number of calls by month and year, so want to create an object that will display 'Month Year', e.g. January 2011.

However, I don't seem to be able to do this no matter what I try. If I create the object as a date and add the formatting as Mmmm yyyy in the universe (the example text shows correctly), when I run a report using that date, it won't count the calls against the month. It displays the date in the correct format but counts the calls against each inidividual day of the month, so for January 2011 there are 31 rows in the table with 'January 2011 nnn'.

I've tried using various combinations of trunc, to_date and to_char and although some parse OK, when I run the report I get error messages about the date format being applied twice, etc.

I must be missing something obvious here, but have wasted a lot of time trying to figure it out. Can anyone give me a simple solutiion?

Anne.

[Former Member]

Hi ,

i also faced same problem ,where we have PC REGISTER DATE field is there from that i extracted Year , Month Objects at universe level.. Here are the formulas which i applied am gettign correct results

CONVERT(INT,CONVERT(VARCHAR,YEAR(PCREGISTERDATE)) + RIGHT('0'+ CONVERT(VARCHAR,DATEPART(MM,PCREGISTERDATE)),2))<= @Prompt('Members Upto Year:','A','Customer\PCRegister_Year',mono,constrained) + case @Prompt('Members Upto Month:','a',{'January','Febuary','March','April' ,'May','June','July','August','September','October','November','December'},mono,constrained) when 'January' then '01' when 'Febuary' then '02 ' when 'March' then '03' when 'April' then '04' when 'May' then '05' when 'June' then '06' when 'July' then '07' when 'August' then '08' when 'September' then '09' when 'October' then '10' when 'November' then '11' when 'December' then '12' END

[Former Member]

Hi Vasantbh,

Thanks for your suggestion but unfortunately that won't work for us because we are running BOXI against an Oracle database. I have tried using similar Oracle commands but get error messages for everything I try.

Regards,

Anne.