Database Functions In IDT

[former_member183330]

Hi Experts,

I have created one universe on SAP BI(BW), in this universe I wanted to create one dynamic filter based on date for last 12 months (Current month not included) I tried some database function like add_months and so, but these are not working for this.

Could any one tell that what type of database functions I Can use in IDTfor this.

Thanks,

G Sampath Kumar

[former_member4998 · Accepted solution]

Hi

You can create all the time periods(Last 12 Months, YTD, YTM) in the BW (Bex Q’s) and use directly in the Webi. So it will improve the performance also.

[former_member183330 · Accepted solution]

Hi Sreenivasulu,

Thanks for your response, but I wanted to create the universe on IDT only. So in IDT how to write the logic for this REQ.

Thanks,

G Sampath Kumar

[former_member4998 · Accepted solution]

Hi

Create two filters @IDT:

Current date = curDate() / sysDate()

Last 12 Months = decrementDays(curDate(), 365)

Find the below screenshot for more info.

Then use these filetes in the webi report

Note: PLEASE mark blog as helpful & answered

[former_member183330 · Accepted solution]

Hi Sreenivas,

As you suggested I can use this as filter, if we have 366 days in year then it wont satisfy and if I run the report any day in current month it should give the previous 12 months data.

Is there any work around for this.

Thanks for your help.

[former_member4998 · Accepted solution]

Hi

but you said Current month characteristic not included, and want to create object in the universe not in the bw.

If there is no date object, then as per the best practice you can create at Bex level using offset values.

[former_member183330 · Accepted solution]

Hi Sreenivas,

Thanks for the help.

I got the logic to achieve above requirement, I used below Logic in filter and it is working fine.

(Month(@Select(Izdso Orr\0calday))>=Month(CurDate())

And

Year(@Select(Izdso Orr\0calday))=Year(CurDate())-1)

Or

(Month(@Select(Izdso Orr\0calday))<Month(CurDate())

And

Year(@Select(Izdso Orr\0calday))=Year(CurDate()))

Thanks,

G Sampath Kumar