Regarding Prompt

[prkr8]

Hi all,

I have one webI report that have prompts which are created at universe level.

One of the prompt should show the LATEST month when report refresh.

If I refresh the report on Jan 1st the 'LATEST' month will be NOV-06 and from 2nd date to end of month will be DEC06 like so on.

Please advice with your reply.

Happy New Year all.

 

[blom0344]

Definitely a special calendertable that holds this logic for every date.
However, depending on the RDBMS you can write an extensive CASE expression that will net you the result.

I could give an example, but then we would like to know the exact database you are working with

Ties Blom

 

[prkr8]

Thanks for your response.

The Database is Oracle 11i.

Thanks.

 

[prkr8]

Dear Blom,

Can you please provide the example case statement for this.

Thanks

 

[blom0344]

CASE      WHEN (TO_CHAR(SYSDATE,’DD’) = ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 1’ 
	THEN ‘NOV’|| TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,’YY’))-1))
	WHEN (TO_CHAR(SYSDATE,’DD’) <> ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 1’ 
	THEN ‘DEC’|| TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,’YY’))-1))
	WHEN (TO_CHAR(SYSDATE,’DD’) = ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 2’ 
	THEN ‘DEC’|| TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,’YY’))-1))
	WHEN (TO_CHAR(SYSDATE,’DD’) <> ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 2’ 
	THEN ‘JAN’|| TO_CHAR((SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) = ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 3’ 
	THEN ‘JAN’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) <> ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 3’ 
	THEN ‘FEB’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) = ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 4’ 
	THEN ‘FEB’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) <> ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 4’ 
	THEN ‘MAR’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) = ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 5’ 
	THEN ‘MAR’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) <> ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 5’ 
	THEN ‘APR’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) = ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘ 6’ 
	THEN ‘APR’|| TO_CHAR(SYSDATE,’YY’))
	WHEN (TO_CHAR(SYSDATE,’DD’) <> ‘1’ AND TO_CHAR(SYSDATE,’MM’) = ‘6’ 
	THEN ‘MAY’|| TO_CHAR(SYSDATE,’YY’))
	…………………….
	…………………….

	ELSE NULL END

This is the expression for 50% of the year.

I suspect you can see the pattern and exent it for the other 6 months.
If you want the leading zero year 2001 to 2009 then add '0' in the expression like:

‘MAR’||'0'|| TO_CHAR(SYSDATE,’YY’))

If you want this past 2009, then you'll need to extent the expression even further (doable, but exhausting)

Good Luck..


Ties Blom