Skip to Content
0

Date filter in Universe Design Tool

May 04, 2017 at 01:03 AM

129

avatar image
Former Member

Dear Experts.

I need to enhance a Universe using UDT (BO 4.1.5) to handle dates with the purpose of filtering data for the current year up to the last period (month).

I am not too familiar with UDT, so I have copied a functioning filter from another similar Universe and edited it to fit mine, including creating some necessary objects (Advanced Hidden objects\Fiscal Year_wCY).

The code and syntax below, parses correctly in the Universe but when exported and tried in the Webi report, I get an “Invalid Prompt Definition (Error: INF)”.

1) Is there anything wrong with the code below?

2) Do I need to create separately any of the @Functions below, such as @Prompt and @Variable? How?

3) Any idea why the filter won't work?

4) More importantly, is there a way to troubleshoot this error? Thru Webi? Rich Client?

Any help would be appreciated.

Thank you.

YTD Date Filter

CONVERT(DATE, RIGHT('0' + MV_X_METRICS.PERIOD,2) + '/1/' + CAST(MV_X_METRICS.FISCAL_YEAR AS VARCHAR)) BETWEEN

(

CASE

WHEN 'Prior Period' <> @Prompt('Period Equal to:','A',{'01','02','03','04','05','06','07','08','09','10','11','12','Prior Period'},Mono,Constrained,,{'Prior Period'},USER:0) AND 'Current Year' <> @Prompt('Year Equal to:','A','@Select(Advanced Hidden objects\Fiscal Year_wCY)',Mono,Constrained,,{'Current Year'},USER:1)

THEN

CONVERT(DATE, ('01' + '/1/' + @Variable('Year Equal to:')) )

WHEN 'Prior Period' <> @Variable('Period Equal to:') AND 'Current Year' = @Variable('Year Equal to:')

THEN

CONVERT(DATE, '01' + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' <> @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) <> '01'

THEN

CONVERT(DATE, '01' + '/1/' + @Variable('Year Equal to:') )

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' <> @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) = '01'

THEN

CONVERT(DATE, '01' + '/1/' + CAST(CAST(@Prompt('Year Equal to:','A','Advanced Hidden objects\Fiscal Year_wCY',Mono,Constrained,,{'CURRENT YEAR'},User:1) AS INT)-1 AS VARCHAR))

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' = @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) <> '01'

THEN

CONVERT(DATE, '01' + '/1/' + CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR))

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' = @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) = '01'

THEN

CONVERT(DATE, '01' + '/1/' + CAST(CAST(@Variable('Year Equal to:') AS INT)-1 AS VARCHAR))

END)

AND

(

CASE

WHEN 'Prior Period' <> @Variable('Period Equal to:') AND 'Current Year' <> @Variable('Year Equal to:')

THEN

CONVERT(DATE, @Variable('Period Equal to:') + '/1/' + @Variable('Year Equal to:'))

WHEN 'Prior Period' <> @Variable('Period Equal to:') AND 'Current Year' = @Variable('Year Equal to:')

THEN

CONVERT(DATE,@Variable('Period Equal to:') + '/1/' + CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR))

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' <> @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) <> '01'

THEN

CONVERT(DATE, CAST( MONTH( DATEADD( MONTH ,- 1 ,GETDATE ( ) ) ) AS VARCHAR)

--(to_char(add_months(sysdate,-1),'mm') + '/1/' +@Variable('Year Equal to:'))

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' <> @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) = '01'

THEN

CONVERT(DATE, CAST( MONTH( DATEADD( MONTH ,- 1 ,GETDATE ( ) ) ) AS VARCHAR) + '/1/' + CAST(CAST(@Variable('Year Equal to:') AS INT)-1 AS VARCHAR))

``WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' = @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) <> '01'

THEN

CONVERT(DATE, CAST( MONTH( DATEADD( MONTH ,- 1 ,GETDATE ( ) ) ) AS VARCHAR) + '/1/' + CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR))

WHEN 'Prior Period' = @Variable('Period Equal to:') AND 'Current Year' = @Variable('Year Equal to:') AND DATEPART(MM, GETDATE()) = '01'

THEN

CONVERT(DATE, CAST( MONTH( DATEADD( MONTH ,- 1 ,GETDATE ( ) ) ) AS VARCHAR) + '/1/' + CAST(CAST(@Variable('Year Equal to:') AS INT) -1 AS VARCHAR))

END)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers