BEx Report should populate unavailable Movement types in Report

[sunilb548]

Hi All,

I've 2 DSO's, T1 DSO with only materials loading from flat file and T2 DSO with Material, Movement Type, Posting date, Plant and Quantity, it is loading from Inventory datasource from ECC. Based on these two DSO's I built a Infoset with Left outer join considering all materials from Flat file should be populate in report, for those corresponding materials in Inventory DSO should populate with remaining columns related to that Material.

Here my requirement is, Report has filter with Movement Type. If Material from FF DSO is not available in Inventory DSO with Movement type Filter (Ex:601), report should contain that material as well as remaining columns Movetype, Plant, Posting should populate with # (unassigned) and Qty with 0.

Here I'm attaching image which depicts my requirement clearly.

Please help on this requirement from Report design or Back end design perspectives.

Best Regards

Sunil

[Former Member]

Hi Sunil,

create two formulas for both movetype and qty like below.

if Movetype = '601'

  result = movetype

else

result = '0'.

Bex formula

T2Movetype --> (movetype ==601) * movetype + (movetype <> 601) * 0

if T2Movetype = '0'

result = '0'

else

result = Qty.

Bex formula

T2Qty -->  (T2Movetype ==0) * (qty*0) + (T2Movetype > 0) * qty

[sunilb548]

Hi Jyothi,

Thanks for the response,

Your formula solution would be helpful when we get material into frontend, i.e Ex: Material 1 will be not picked in the SQL query which was fired by report. To be clear on the issue, Movement type is the global filter, when we execute the query,

1. System prompts for MoveType variable,

2. we give '601' as the filter value

3. SQL query will be fired on Info set which was built on top of 2 DSO's (T1 & T2) with Movement type = 601 restriction.

4. Though it is left outer join, query will fetch/select T1 Materials matching with 601 MoveType from T2 table and related columns of that material in T2 (Ex: Material 2).

Here query behavior is, it'll omit Materials which are not satisfying with MoveType restriction (Ex: 201) and give values to other materials of T1 which are not available in T2 as 'UnAssigned (#) '. 

Hope you got the point,

So, How we can bring those skipping materials from backend (Infoset) to front end and populated them in report.

All materials from T1 table should be populated in the report, what ever the restrictions we give on MoveType. Movement types are not static.

Thanks & Regards

- Sunil

[Former Member]

Hi,

so for movement type : '601' is a constant filter in selection screen? I mean users always selects 601 in the selection screen?

[sunilb548]

Hi,

No, user is allowed to give Movement type selection with multiple values.

Ex: 601, 641, 643, 201... etc,.

Regards,

[former_member199945]

Hi ,

Try with multiprovider  it might work as expected .
.

Thanks.