BusinessObjects Board

universe object and predefined condition

building semantic layer
#1

hi,

I have time dim with based on company fiscal calender, Current month object is created in universe based getdate().

Object - current month

select
dbo.timedim.timemonth

Where
convert(varchar(8),timedate,112) =convert(varchar(8),getdate(),112)

when I use ‘current month’ object in predefined condition to return only fiscal month’s less than current month, it is not working.

In query result is shows

dbo.timedim.timemonth< dbo.timedim.timemonth

the where part of current month is not executed.

My understand ‘current month’ will be calculated and then used in the predefined condition. Any suggestion will be very helpful.

thanks in advance.

ThanksForHelp (BOB member since 2006-09-25)

#2

Is your WHERE condition defined at the Object level ? If yes, then the WHERE clause is the problem. As a best practice you should not use WHERE clause in the definition of an Object. You could re-write the same using CASE WHEN THEN ELSE END statement.

BO_Chief :us: (BOB member since 2004-06-06)

#3

hi,

thanks, I changed to CASE WHEN, but it is not giving the requried output

as comparing with getdate() and timedate ( fiscal date calendar), it is returning current month only for the query result with timedate = getdate for all others it blank.

:hb: I am not sure what is missing

Thanks

ThanksForHelp (BOB member since 2006-09-25)

#4

What are the format outputs in the two date columns?
Please give us the syntax in the Case.

Mak 1 :uk: (BOB member since 2005-01-06)

#5

thanks for your time

Timemonth - 2013 03, 2013 04, 2013 05, 2013 06…

timedate - 2013-06-21 00:00:000

since getdate() function is with the time, I am truncating to match with timedate column

CASE when convert(varchar(8,timedate,112) =convert(varchar(8,getdate(),112) Then Timemonth END

query is doing the comparison for each record so when the transaction date is not equal to getdate() it is not returning the timemonth value otherwise it is blank. Case is working correct but I need ‘Current month’ as stand alone value to use in filter.

ThanksForHelp (BOB member since 2006-09-25)

#6

One is Year Month and one is YYYY/MM/DD HH:mm:SS?

Its still unclear what you are trying to compare and you haven’t supplied the Case statement?

Mak 1 :uk: (BOB member since 2005-01-06)

#7

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

#8

Yes Nick, but that was added after :wink: .

Mak 1 :uk: (BOB member since 2005-01-06)

#9

The OP is trying to keep you on your toes.

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

#10

I have to ask Nick, whether I need any of more those :roll_eyes: :mrgreen:

convert(varchar(8,timedate,112) =convert(varchar(8,getdate(),112)

Wouldn’t you just use this in a universe filter then?

Mak 1 :uk: (BOB member since 2005-01-06)

#11

Sorry If I am not clear.

By adding convert(varchar(8,timedate,112) =convert(varchar(8,getdate(),112) in filter will only return records with transaction on getdate(). But I need pending sales less than current month.

example - 2013 06 - current month, I need pending sales from 2013 01 - 2013 05.

since all the transaction need to be grouped monthly I need to identify current month.

thanks for the help

ThanksForHelp (BOB member since 2006-09-25)

#12

Please have a look at this sticky topic.

https://bobj-board.org/t/152613

Mak 1 :uk: (BOB member since 2005-01-06)