BusinessObjects Board

BO Date Functions

building semantic layer
#1

I want to create two condition objects in a Universe that will represent “FIRST_DAY_OF_MONTH” and “LAST_DAY_OF_MONTH” in order to use them as prompts in a monthly scheduled report but the problem is that i don’t know how to use the BO functions in the editor…

Any help will be appreciated…

dapostolopoylos :greece: (BOB member since 2005-06-02)

#2

Hi,

You don’t use BO functions in a universe when you define a universe object. You need to use functions specific to your database.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Have a look at https://bobj-board.org/t/152613 and https://bobj-board.org/t/162166

anorak :uk: (BOB member since 2002-09-13)

#4

I want to schedule a report to run once a month with prompts the first and the last day of the previous month…

Somebody told me that i have to create two condition objects on the Universe level in order to get those dates automatically…

dapostolopoylos :greece: (BOB member since 2005-06-02)

#5

Yes thats one way of doing it, the links provided by anorak pretty much tell you how to do that.

Nick Daniels :uk: (BOB member since 2002-08-15)

#6

You need to create objects that are
“First Day of the Previous Month” and
“Last Day of the Previous Month”

Have a look at the Relative Dates sticky at the top of the forum topic list.

The objects won’t parse when you build them but they do work. :+1:

#7

I am using IBM DB2 and i managed to create the query that creates the first and last day of previous month but the conditions don’ t parse and in the integrity check i get an error…

dapostolopoylos :greece: (BOB member since 2005-06-02)

#8

They won’t parse because they aren’t associated with a table.

In SQL Server, for example, current date is getdate()
If you tried to parse that in Designer, it would generate “SELECT getdate() FROM” as SQL code, which would not parse.

As soon as you use objects from tables (or views, derived tables, etc.) within your query then it will work fine.

Try it and report back. :yesnod:

#9

I used these statements and it worked fine (i am using db2):



--First Day of the Last Month:

CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 1 MONTH




--Last Day of the Last Month:

CURRENT_DATE - DAY(CURRENT_DATE) DAYS

:slight_smile:

dapostolopoylos :greece: (BOB member since 2005-06-02)

#10

In Db2 if we implement t below formula & try to check the values(display) then we get err during SQL generation The query does not references a table (QP0007) …IS this t normal behavior?

Msb :india: (BOB member since 2008-02-15)

#11

Yes, this is normal behaviour. Please, try a search on B :mrgreen: B, and you will find threads explaining why that is.

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

#12

If we reference a single table which has dates in select table option then the formula would parse… If we have multiple tables with multiple dates then it can’t be parsed… But it would we work in the report with other objects :slight_smile:

Msb :india: (BOB member since 2008-02-15)