Create Year Offset

[Former Member]

Dear experts,

I have a client requirement for a webi report where user must enter a year (e.g. 2010) and the report must return that year and previous (e.g. 2010 and 2009).

How do I create that offset? Must it be defined directly in the universe or can I create it only in the webi report.

Thanks for your help.

Inê

[Former Member · Accepted solution]

HI,

You could do it easily in the universe.

Create the objetc with the below condition in the where clause,

Where clause:

Year in ( @prompt(user in put year),@prompt(user in put year-1))

This condition will give you data for two years for ex: user input is 2010 and the values passed in the prompt will be 2010 and 2009,hence you will get the data for two years.

I hope this will help you.

Cheers,

Ravichandra K

[Former Member · Accepted solution]

Hi Ravichandra,

Thank you for your reply.

Could you give me further detail regarding that where clause... I'm new at BO

Cheers!

Inê

[Former Member · Accepted solution]

Hi,

The condition which i gave above need to be in the where clause of any one object which is being used in the report(preferablly have it in the year object).

Please check for the exact synatax according to your DB.

Cheers,

Ravichandra K

[Former Member · Accepted solution]

Ok. It's done.

the sintax is:

"Table"."year" = @Prompt('year','N',,,) - 1 or @Prompt('year','N',,,)

Thanks for your help!

Inê

[Former Member · Accepted solution]

You may find a between to be more efficient than IN or the OR clause that you have. Talk to your DBA and ask them for help to understand which of the following performs the best:

table.year IN (@Prompt('Year')-1, @Prompt('Year'))

or

table.year = @Prompt('Year')-1 or table.year = @Prompt('Year')

or

table.year between @Prompt('Year')-1 and @Prompt('Year')

I would be curious to know what their answer is.