Combining multiple data provider

[Former Member]

Dear All,

I am working on a webi report which consumes three data providers based on BEx. My data provider provides following field

1. Stock related information

     This data provider gives following columns date and quantity.

2. Purchase

     This data provider gives following columns : date of GR Entry, document number and amount

3. Payment

     This data provider gives following columns: date of payment, invoice number (purchase number) and amount ( There is no material in this data provider).

Intention of creating this report is to show a table that will be give stock position on daily basis and an amount paid against an invoice. Amount paid should be reflected in the table only when payment is done.

Providing  sample data as below

Stock Info

Date               Qty

01-Apr-16        100

02-Apr-16          90

03-Apr-16          80

04-Apr-16          70

05-Apr-16          60

06-Apr-16        100

07-Apr-16          90

08-Apr-16          80

09-Apr-16          70

10-Apr-16          60

Purchase

GRE Date       Document Number     Qty

06-Apr-16        1111                         40

Payment

GRE Date       Document Number     Amount

07-Apr-16        1110                         6000

09-Apr-16        1111                         4000

Expected Result

Date               Qty          Amount

01-Apr-16        100    

02-Apr-16          90

03-Apr-16          80

04-Apr-16          70

05-Apr-16          60

06-Apr-16        100

07-Apr-16          90

08-Apr-16          80

09-Apr-16          70          4000

10-Apr-16          60

Is their any we can achieve this? I think will have to use outer join and inner join combination. Since we are using BEx as source, I can not combine two queries.

Any help to resolve this issue is highly appreciated.

Regards,

Girish Oak

[Former Member]

Try and make use of TimeDim() function to tweak the logic.

Regards

Niraj

[Former Member]

Hi Niraj,

Sorry but I am unable to figure out how this can be done. Attaching an excel sheet that can be used as a sample data set. I have added a sheet labeled as Expected Output, that's the output I am looking for.

Date	Qty	Payment
4/1/2016	100
4/2/2016	90
4/3/2016	80
4/4/2016	70
4/5/2016	60
4/6/2016	100
4/7/2016	90
4/8/2016	80	2000
4/9/2016	70
4/10/2016	60

[Former Member]

Merge the GRE Date from Purchase and Payment and Date from Stock Infor. SO with that Merge Dimension you will have all possible dates in your merged dimension.

Then just use Quantity and Amount from other two queries.

Let me know if this helps.

Regards

Niraj

[Former Member]

The problem is if I merge these two data providers I get two payment rows where I want only one payment to appear as 1111 is the only common document. Dont want to display for 1110 document. Also, I will be getting stock for all days hence need to have full outer join on this resultant data set.

Regards,

Girish Oak