Question Re: Data selection from "Valid from" & "Valid To" date in Technology Q&A

Data selection from "Valid from" & "Valid To" date

former_member231968 — Mon, 14 Sep 2015 13:56:38 GMT

Dear BI Experts

I have a requirement in SAP BI.

I have data in BI Cube as show in attachment. Which is having Material, Valid from , Valid date and the Amount(Unit Price)

In BI Report user will enter one single date (i.e 27.05.2012) and the output should show amount 5.25 As seen in the attachment.

I mean to say that. If user execute the report by giving 27.05.2012. query should search in between 0VALIDFROM and 0VALIDTO for validity and display the result.

Note : user cant user.>= or <= conditions in scenario. If we use >=27.05.2012 then the result will show all the values which is greater than input date.

Your valuable input highly appreciated.

Re: Data selection from "Valid from" & "Valid To" date

cornelia_lezoch — Mon, 14 Sep 2015 14:34:13 GMT

Hi Sachin,

as far as I know, this is only possible using a stock cube.

What is the meaning of your 0AMOUNT? Is it a stock value?

Doe you report only one material? Then you could create a user exit variable, which reads the user entry and finds the correct value.

regards

Cornelia

Re: Data selection from "Valid from" & "Valid To" date

cornelia_lezoch — Mon, 14 Sep 2015 14:35:31 GMT

or can´t you create 2 variable - VAR1 which is entry enabled and VAR2 which is replacement path from VAR1?

Then define in your query

valid from <= var1

valid to >= var2

Re: Data selection from "Valid from" & "Valid To" date

former_member231968 — Tue, 15 Sep 2015 05:17:29 GMT

Hello Lezoch

"0Amount" is the Unit Price ( sales price) of material.. this price keep changes provided if any promotion or offers granted to the customers.. so accordingly there is validity period fixed for every price against the material.

the concern is.. when we have a particular Material price valid from (i.e 01.09.2015 till 30.09.2015 at Unit Price is 5.00 USD) and if the user execute the report giving date 15.09.2015 in selection parameters.. the output should show USD 5.00 is active on date 15.09.2015.

Regards

Sachin

Re: Data selection from "Valid from" & "Valid To" date

Former Member — Tue, 15 Sep 2015 05:55:40 GMT

Hi Sachin,

As your requirement is that you should get the unit value which falls with a particular range even on selecting any date in that range.

For this you will have to write exit code (exit variable) then only it is possible to achieve your requirement.

Regards,

Amit

Re: Data selection from "Valid from" & "Valid To" date

former_member231968 — Tue, 15 Sep 2015 06:03:10 GMT

Hello Amit

Good to hear that.. but it will be more clear if you can let me know, what kind of Exit variable should be written.

Sachin

Re: Data selection from "Valid from" & "Valid To" date

cornelia_lezoch — Tue, 15 Sep 2015 07:27:30 GMT

it´s not polite to answer calling me only by my last name

Re: Data selection from "Valid from" & "Valid To" date

former_member231968 — Tue, 15 Sep 2015 09:46:43 GMT

Hello Guys any suggestions !!!!

Re: Data selection from "Valid from" & "Valid To" date

Former Member — Tue, 15 Sep 2015 15:31:50 GMT

Hi Sachin,

You need to create two variables one on Valid From and other on Valid To

Valid From Variable – Its Manual input variable with single value entry.

Valid To Variable – Its replacement path variable, get value from Valid from variable

Then Create a new selection and use the Amount Key Figure along with these two variables such as,

Valid To IO value >= Valid to variable value and Valid from IO value <= valid from variable value.

Kindly find below screenshot for your reference,

It should solve your problem

Thanks,

Nilesh

Re: Data selection from "Valid from" & "Valid To" date

former_member231968 — Wed, 16 Sep 2015 11:09:29 GMT

Can you please share little more information on your suggestions "Valid To Variable – Its replacement path variable, get value from Valid from variable"

i am little difficult in understanding this line..

Sachin Kumar

Re: Data selection from "Valid from" & "Valid To" date

Former Member — Thu, 17 Sep 2015 04:31:18 GMT

Hi Sachin,

Create a new variable on Valid TO infoobject and select processing by - Replacement Path.

Have a look at below snap for more details,

Thanks,

Nilesh

Re: Data selection from "Valid from" & "Valid To" date

former_member231968 — Thu, 17 Sep 2015 08:13:56 GMT

Hello Nilesh..

that's perfect.. solved my issue,