BusinessObjects Board

To Develop objects for Previous Months

building semantic layer
#1

Hi All,
I need some one help on how to implement the below requirement in the universe,
Currently we are achieving this in the report level which I think it is not good in regards to performance.

The requirement that we are achieving is as below,

When the users selects a date from a prompt( For eg., 2009-09) then I display the data for that month,previousmonth,2 month previous and 3 month previous. For this I am writing complex formulas in report using relative function etc.,

How can I achieve the same in the universe. Can some one please help me. I know I ave to create pre-defined conditions but I am not sure how to create those.

The universe information is as below,
Class: Date/Time
Object : Year_month (This object is pulled to the prompt in the report for the user to etner select the Year and Month).

The select statement for this object in the universe is as below,

substring (dbo.rms_dim_date.cal_month_year_display_2,1,5)||substring(dbo.rms_dim_date.cal_month_year_display_2,7,2)

Column Calendar_date is also used - > dbo.rms_dim_date.calender_date
Database : Sybase
Designer - BO XIR2

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

#2

Have you serached on BOB for smart calendar tables and date functions? Mark P posted this for Oracle, you will have to adjust SQL the syntax for your DBMS/sybase. Again, put some elbow grease to it :wink:

Andreas :de: (BOB member since 2002-06-20)

#3

hey guys…i need help on the requirement below…

I have an object Activity_date and the requirement is that I need to make a filter that will take only activity dates or current and previous month.

My database is netezza and no formula i create seems to work.

i created this object -
trunc(trunc(sysdate,‘MM’)-1,‘MM’) for previous month’s 1st day but i guess this is for Oracle database. :frowning:

I need something similar for netezza as this is throwing a database error.

further i need to make a condition object in the universe where i need to put a filter as @select(activity_date) between AND <today’s date(I don’t know how to take this either in netezza) :(>

nishagupta :india: (BOB member since 2010-03-03)

#4

Sybase follows a lot of the syntax of SQL Server, using functions such as dateadd.

This page should give you sufficient syntax to use a bit of the old brain power on to work out what you want.