cancel
Showing results for 
Search instead for 
Did you mean: 

Filter on Current and Previous Year in IDT

jyothirmayee_s
Active Contributor
0 Kudos

Guys,

I might have messed up something and couldn't able to get the right way of creating filters in IDT.

Need to restrict 2 Year data (Current and Previous Year) in WebI and wanted to create a run time parameter  (No prompts)


i have created dimension in BL for Current Year and Previous year but any ideas on the approach is appreciated.

Max(extract(year from "_SYS_BIC"."view"."CREATED_DATE"))

Data source is HANA view and BO 4.1.

Thanks,

Jothi

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

Hello Jyothi,

What is the Source of your HANA (e.g. ECC etc)?

I would recommend to create a filter object on that date object that you can drag in the query to limit your data for just 2 years.

Something like below based on the

Year(Table.DateObject) =Year(CURRENT_DATE)

OR

Year(Table.DateObject) =Year(CURRENT_DATE)-1

Also I would recommend to create a Calculated Column on HANA itself that you can make use of while creating filter.

Here is the code to get that

component(date, int)


Where Date is the date field and you need to give 1 for second parameter to extract year

Regards

Niraj

Former Member
0 Kudos

Hello Jyothi,

I was able to get the correct syntax to create filter object on IDT from HANA.

Please refer to the screen shot below and create a filter object and use that in your Webi Report so that you will get the data for current and Previous year only also with this you will not restrict the Universe to always bring in last two years data which increases the scale-ability of the Universe and also will not prompt the end user.

Regards

Niraj

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Jyothi,

You can use functions like currentdate=now() to get current date

and component function to get year out of the current date currentyear=component(currentdate,1)

previousyear=currentyear-1


Instead of current date you can use any date from which you want to extract year

refer below links

Date Functions - SAP HANA Modeling Guide for SAP HANA Web Workbench - SAP Library

jyothirmayee_s
Active Contributor
0 Kudos

Hi Divya,

Are you suggesting to use the Component() in HANA view to create variable.

I don't want to restrict data at HANA view level, I wanted the restriction at Universe level.

Can you share your experience on it please?.

Thanks,

Jothi

Former Member
0 Kudos

Hi Jyothi,

While creating the object --> SQL Assistant --> Functions --> Database Functions --> All

look for the functions which were mentioned earlier. Generally IDT gives the option of using functions based on database on which the universe is built.

As you are using Hana as the source if should support the respective functions in the universe as well.

Right now i don't have Hana environment. So check the list and let me know if you could not find them.

Thanks, Divya