BusinessObjects Board

X Number of Days from Current Date

building reports webintelligence
#1

Hi everybody,

New BO user having some difficulties and have heard great things about BOB!

I’m writing a report that has to show records where the date completed field is x number of days before todays date. I’ve been scouring around and found out about the RelativeDate function but I just cannot get it to play.

So for example:

Name | Task | Date Complete

Bob | Proceed with Assessment | 01/11/11 --------would show
Sam | Proceed with Assessment | 25/12/10 --------wouldn’t show

etc…

Here is what I have:

[DateCompleted] >= RelativeDate(CurrentDate();-182)

Can you anyone give me any pointers as what is the best way to go about what I am trying to acheive?

Many Thanks

Matt

matt_lincoln (BOB member since 2011-12-06)

#2

Welcome to the forums Matt.

What are you doing with that formula?

One option is to make it part of a boolean variable:

=If([DateCompleted] >= RelativeDate(CurrentDate();-182);1;0)

Then filter on the 1s.

#3

Thanks Mark P after looking at this further (with some lunch in me!) maybe I should of put this in the Universe Forum.

I think I want this to be a dimension that I could filter on when building the query for the report?

I can confirm that the above formula does work and does show the correct boolean values. But to filter on them from there…?

It’s amazing how quickly one thing can make you feel like the class idiot!

matt_lincoln (BOB member since 2011-12-06)

#4

Matt,

What database are you using?

You could have a look here:-

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

Cheers,

Mark.

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

#5

Hi Mark,

It is a SQL database on MS SQL Server 2005

matt_lincoln (BOB member since 2011-12-06)

#6 
cast(convert(char(10),dateadd(d,-182,getdate()),23) as datetime)

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

#7

Click on the Show Filters button (on the left of the bar, next to show Formula Bar)
Then drag the variable into there and set the condition equial to 1.

As for building in the query, should it be always 182 or does that vary?

#8

Sorry I have been away so not had chance to reply before now!

It is always 182 days. I will try the suggestions and will get back to everybody.

Cheers

Matt

matt_lincoln (BOB member since 2011-12-06)

#9

Excellent, the SQL works flawlessly. There were some minor issues with locale settings in webi that hadn’t been set properly in the installation. But a few trial an errors lateer all is good.

Thank you so much everybody who read / replied.

Matt

matt_lincoln (BOB member since 2011-12-06)