Prompt with Filter

building semantic layer
1

Hi All

I have to create a report with 2 Objects Record Date, Record Name. Below are the list of values.
Record Date Record Name
01-02-2009 Chandra
05-03-2009 Siva
06-04-2009 Ramesh
05-05-2009 Sridhar
6-06-2009 Subbu
15-07-2009 Chaitu

User wants a prompt to be created for No. of Months. If user enters 2 in the Prompt then report should filter date for the last 2 months data based on current date.
With the above example, current date: 11-08-2009 minus 2 months is 11-06-2006. Hence report should show record dates setup during this period.

Record Date Record Name
15-07-2009 Chaitu

User can change No. of Months value dinamically each time the report is run and based on that the report should show the records.

Thanks to help us.

Regards
Chandra

vcoreddy11 (BOB member since 2006-09-02)

2

Chandra,

Try this -

Record Date between add_months(sysdate,to_number(@prompt()*-1) and sysdate

Mind you, this works for an Oracle database.

vinod_menon :india: (BOB member since 2007-04-09)

3

[Moderator Note: Moving to Universe Designer - Jansi]

Jansi :india: (BOB member since 2008-05-12)

4

Many Thanks Vinod.

When i modify the sql with the below query
BETWEEN add_months(sysdate,to_number(@Prompt(No of Months)*-1) AND sysdate

It is throughing me a Syntax error: Syntax error in Variable (QP0018).

However when i used the @variable instead i am able run the report.

BETWEEN add_months(sysdate, (@variable(‘No of Months’))*-1) AND sysdate

Please check both the above conditions and suggest what is that syntax error.

Thanks to help me.

Regards
Chandra

vcoreddy11 (BOB member since 2006-09-02)