How to filter a cal view with a range value i.e Year 2012-2014 ?

[Former Member]

Hi All,

Below is my Data Model

and below is my Cal View

I have created a cal column in my Analytic (Projection 1) view called "Cal Year" as my base fact table does not have "Cal Year",

Now I have created a input parameter on the semantics of my Cal View and in my Project 2, I have applied a

filter on "Cal Year" (PS: I have created projection 2 so that I could apply filter on my calculated column).

Now, When I execute my query on my Multi Provider I get a input screen and I'm able to filter the data set as per cal year.. and it's working fine.

Now, I want the user to be able to choose a range i.e for example (2012 - 2015)

any suggestions please ?

Thanks,

Ashwin

[justin_molenaur2 · Accepted solution]

Two input parameters, FROM and TO.

Apply the filter BETWEEN or a >= FROM and <= TO.

Evaluate performing a join to a date dimension to derive the YEAR as opposed to a calculated column. Applying a filter to a calculated column can be expensive as you need to generate the YEAR for every row in the table and then apply the filter.

Regards,

Justin

[Former Member · Accepted solution]

Hi Justin,

Thanks for your reply and I appreciate your suggestion.

Now, if I use 2 i/p parameters, then how do I pass them from my BEx Query?

Below is my thinking:

1. Drag 0Calyear into the filters of my query

2. Restrict 0Calyear with a variable (interval variable)

Kind Regards,

Ashwin

[Former Member · Accepted solution]

Hi Justin,

Thanks and I appreciate your suggestion.

I have created 2 i/p parameters on my HANA Cal View and in the Projection of my HANA cal view, I have

set a filter on 0Calyear (i.e btw ip1 and ip2).

In my BEx query I have created 2 manual entry variables with ref char 0calyear and was able pass the user

values smoothly through to my HANA Cal view.

Kind Regards,

Ashwin

[dmitry_kuznetsov1]

Ashwin,

jundging on your screenshots I do not see why use HANA modelling. Enriching the data in BW (time dimension derivation in the transformation to DSO) is a go-for solution in this case.

Dmitry

[Former Member]

Hi Dmitry,

We are trying to achieve data virtualization with this approach and to reduce the data footprint in the system especially for the year specific cubes.

Kind Regards,

Ashwin

[dmitry_kuznetsov1]

By reading one dimension in BW data footprint (is modeled well) will not be measurably impacted. Introduction of additional steps in HANA modelling definitely will, both performance- and support effort-wise.

[former_member200930]

Hi,

Please replace your input parameters with Filters option.  It will work for Range.

- Vikram