Formulae variable confusion/ abap routine

[Former Member]

in ods i have data like

Postingday................value1........value2

1/26/10............70.................50

1/27/10............60.................48

............

12/30/10............120.................55

12/31/10............10.................52

now in the report for a cube i have to show data for 3 keyfigures

1 is amount and 2nd is (Amount x Value1) 3rd is (Amount/Value2)

now the value1 and value2 changes everyday depending on what the Date is...

so today on 1/26/11 the value1 is 70 and value2 is 50

so i have to write formulae variable which fetches the value1 and 2 for Todays date...

what should be the code for customer exit for formulae variable..

something like pseudocode below

when "zvar1"

FOR 0calday = today = PostingDay in ods table /bic/azpostingdte

Seleect Value1 ..

endfunction

[Former Member · Accepted solution]

why don't you populate valu1 and value2 in the cube itself as kf by performing a lookup on DSO.

it would be a one time load. you will directly have value1 and value 2 in cube itself.

if you do at front end then every time you execute the query the lookup would be performed.

[Former Member · Accepted solution]

Hi Om

thanks

but in cube i have 5 lines for 1 financial document #

if i add it as keyfigure

it will be in 5 lines

and that value will add up

so if it is 70 forr 5 lines its gonna show up as 350 for day 1/26

even if i do keyfigure restricition/ property trick something like aggregation etc.

there can be multiple orders as in day for 1/26

so when i run report for whole week or whole month or whole year it gets very tricky to keep value1 as 70

[Former Member · Accepted solution]

I know you have 5 lines for 1 financial document, and you can add it as KF. But the value1 and value2 you can hide. You will 2 formals to caculate. One is amount*value1, the the other is amount/value2.

hope it will help you.

Edited by: gang qin on Jan 27, 2011 4:40 PM

[Former Member · Accepted solution]

Om and Gang

first of all i cant look up ods and load values as keyfigure

when you look up usually in routine you have to identify the Dataelement of a characteristic or a DBTable for it...

so i cant really lookup keyfigure for a matching calendar day

and this table and values are maintained seperately in an ods as it is changing everytime...

[Former Member · Accepted solution]

ok, you would like to do sth in the customer exits with formular variable.

formular variable : zvar. (with customer exits) for value1.

case i_vnam.

WHEN 'zvar'.

data: l_s_table type your tablename /bic/......

IF i_step EQ '1'.

CLEAR: l_s_range,

select single * from DB table into l_s_table

with date = sy-datum.

CLEAR l_s_range.

l_s_range-low = l_s_table-value1.

l_s_range-sign = 'I'.

l_s_range-opt = 'EQ'.

APPEND l_s_range TO e_t_range.

endif.

endcase.

for value2 you do same with value1.

PS: for amount you must treat it as a restricted kf (with a variable of customer exits, which is gefiiled with sy-datum.)

hope it helps you.

Edited by: gang qin on Jan 27, 2011 9:43 PM

[Former Member · Accepted solution]

i wrote below but it gives error message

"unable to interpret WITH . possible causes comma error or incorrect spelling"

CASE i_vnam.

WHEN 'Zvar_value1'.

DATA: l_s_table TYPE /BIC/AZVALODS00. (ODS TABLE)

data: l_s_range_low.

IF i_step = 1.

CLEAR L_S_RANGE.

SELECT SINGLE * FROM /BIC/AZVALODS00 INTO l_s_table

WITH V_TODAY = sy-datum.

l_s_range_low = l_s_table-/BIC/ZVAL1.

l_s_range_sign = 'I'.

l_s_range_opt = 'EQ'.

APPEND l_s_range TO e_t_range.

ENDIF.

ENDCASE.

if i put . after l_s_table it gives error

WITH is not expected WAIT is expected.

[Former Member · Accepted solution]

sorry.

not WITH, but Where

[Former Member · Accepted solution]

thank you very much for your patience

it gives me error

Field "v_today" is unknown

i have defined v_today above as -

---

DATA: V_TODAY type sy-datum.

i have used

-

---

where v_today eq sy-datum.

if i use -

where /bi0/ozdate1 eq v_today

it gives me error --/bi0/ozdate1 is unknown...

i thought /bi0/ozdate1 shoud be compared for todays date in ods table

[Former Member · Accepted solution]

V_today must be a field in your table e.g ODS table, which has date value for today.

select single * from your ODS table into l_s_table

where fieldname eq sy-datum.

[Former Member · Accepted solution]

hi Gang

the date field in my ods is ZDATE123

the field is date123 and data-element is /bi0/oizdate123

so i wrote the code as --

SELECT SINGLE * FROM /BIC/AZVALODS00 INTO l_s_table

where ZDATE123 eq sy-datum.

it gives error "Field "zdate123" is unknown"

so i used the code as

SELECT SINGLE * FROM /BIC/AZVALODS00 INTO l_s_table

where /bi0/oizdate123 eq sy-datum.

that also gives the error "Field "/bi0/oizdate123" is unknown"

that is why i am getting confused how to declare

should i need to use any Data statement to use this ??

[Former Member · Accepted solution]

Hi,

Are you sure that ZDATE123 is right written in /BIC/AZVALODS00?

ZDATE123 or / bic/ZDATE123?

IF right, I think that it should not give error. PLS in the above all the codes define:

TABLES: /BIC/AZVALODS00.

[Former Member]

Hi,

Restrict the amount with the variable 0DATE which is the SAP exit variable for current day.