How to filter data in a Cal View Dynamically? (without using i/p parameters or variables)

[Former Member]

Hi All,

I have a requirement wherein I have to slice the data set into 3 separate data sets i.e Current Year Data, Current Year -1 Data and Current Year -2 Data based on Current Year but then the user will not be providing any i/p parameter or variable from the query and there is no current year in the table on which I have created this cal view.

I have only a fiscal year.

Below is what I have tried and think:

1. In normal BW system you can always get your current year/date from sys-datum using an ABAP routine or code so is there any such field in HANA?

2. I have used a filter expression in my cal view on the fiscyear column of my cal view i.e addyears(FISCYEAR,-1) and addyears(FISCYEAR,-2) but this is not working

3. will it help if I join a time dimension table to my table in the cal view to get the current year? if yes, how will I filter this year dynamically.

appreciate if you can provider any thoughts..

PS : My source is BW

Kind Regards,

Ashwin.

[anindya_bose · Accepted solution]

Hi Ashwin

You can try this in calculated attribute

CURRENT YEAR - NVARCHAR(4)

component(now(),1)

PREV_YEAR NVARCHAR(4)

string ( int ( component(now(),1)) - 1 )

You would need two more calculated Column : 3)  FISCYEAR - CURRENT YEAR

and                                                                    4)  FISCYEAR - PREV_YEAR

When the difference is Zero, that is your relevant data .

However, I would prefer Input Parameter type "Derived From Procedure" or "Column" type  . It can filter directly from the table and would give much better performance.

Regards

Anindya

[Former Member · Accepted solution]

Thanks for your suggestion, Anindya.

will try and let you know if it works.

Kind Regards,

Ashwin

[Former Member · Accepted solution]

Hi Anindya,

yes, I could calculate the current year and previous year with your inputs,

so now how can I filter my FISCYEAR using this calculated column?

Below is what I have tried but no luck

and this is not filtering the data, it brings back everything!

any thoughts?

Kind Regards,

Ashwin

[anindya_bose · Accepted solution]

Ok..  Now you know how to get current year in model .

In your first projection node, you can calculate  two columns with "Component" function.

Add another projection and filter on DIFF.  You only need data where DIFF is 0 , 1, and 2.

You can later use this DIFF field for "restricted column".

Overall model would be like this.. Apply filter at projection 2.

Projection 1 ------> Projection 2 ------->Aggregation ----->Semantics

However, this would bring all the data to Projection 1 and then filter at Projection 2.  You can do filter at projection 1 itself, using Input Parameter which dynamically gets the data.

Note: you can cast DIFF field as NVARCHAR( attribute), so that you can use it in Restriction .

FISCYEAR(A)	CURRENT_YEAR(B)	DIFF(B-A)
2016	2016	0
2015	2016	1
2014	2016	2
2013	2016	3
2014	2016	2
2015	2016	1
2013	2016	3
2016	2016	0
2011	2016	5
2009	2016	7

[Former Member · Accepted solution]

Hi Anindya,

Thanks for your suggestion and it worked for me as below.

Here is what I have done:

1. Created 3 separate cal views i.e Cal_View_CY; Cal_View_CY-1 and Cal_View_CY-2

2. Created 2 Cal Columns i.e "Current_Year" and "Flag" in each of the Cal_View

   I have calculated "Current_Year" as component(now(),1) in Cal_View_CY

                            "Current_Year" as string ( int ( component(now(),1)) - 1 ) in Cal_View_CY-1

                            "Current_Year" as string ( int ( component(now(),1)) - 2 ) in Cal_View_CY-2

3. in Flag I have a calculation (Current Year - Fiscal Year)

     Steps 1,2 and 3 were created in Projection 1

4. In Projection 2, I have put a filter on "Flag" as "Flag = 0"

Thanks a lot for your suggestion and I completely agree that i/p parameter is the best solution for this

but not able to help as this is how my requirement is

Kind Regards,

Ashwin

[anindya_bose · Accepted solution]

Hi Ashwin

Did you join 3 Calc View created in step 1 ?  If yes, consider Union with constant value there.  Much better for performance.

Your step one can also be handled with a Input Parameter.   Let's say your fiscal years is something constant, like 2016, 2015 and 2014.   In that case you would apply constant filter for 3 calc views in step 1. Right?  These three value can be made dynamic with input parameter with procedure type .

Regards

Anindya

[Former Member · Accepted solution]

Hi Anindya,

Yes, the idea is to create 3 virtual providers on top of these Cal views in BW and then use a CP to union.

I Completely agree with you on the I/p parameter concept as I have tried it myself in my other models and it works like magic!

I Can't use the same logic here as the query which hits the model doesn't have a mandatory variable on the year and I can't change the client's request or existing query

Thanks for all your help n suggestions, appreciate it very much!

Kind Regards,

Ashwin.

[anindya_bose · Accepted solution]

Input Parameter type "Derived From Procedure" is not a prompt . Reporting user would not get a prompt.  Input Parameter would get value from Procedure, and the procedure would dynamically calculate the year based on current date.   Below an example I am using in my model

Regards

Anindya

[Former Member · Accepted solution]

Oh!! was not aware of this approach! Could you please elaborate the steps?

I'll definitly try this..

Thanks,

Ashwin

[anindya_bose · Accepted solution]

Writing a small blog post with all the detail steps, would provide you link .

You might have a look at this .

Regards

Anindya

[Former Member · Accepted solution]

Sure Anindya, would appreciate it very very much!

Kind Regards,

Ashwin