Merging 3 Data Providers compatible 2 by 2

[Former Member]

UPDATED 03/17/2016 - Attached file 3providers.txt. This file is, in fact, a zip file containing both the wid file and the excel. Rename from 3providers.txt to 3providers.zip. This example was done in 4.1 SP5

Hi,

I´ve just find a way to use 3 Data Providers which are "compatible" 2 by 2. The idea came from this post Merging 3 queries using 2 merged dimensions.

In this post, I´ll use three Data providers in an Excel file.

The first  one that I´ll name Invoices has 2 fields an ID of the invoice and the date of the invoice

id	date
1	1/1/16
2	10/2/16

The second, Customer with invoice ID and Customer Name and parts no

customer name	invoice Id	parts no
Maria Cecilia Goulart	3	C
Nadia Stella	2	B
Roger Plank	1	A

And the last one, Items with item , parts no price and qtd (quantity)

item	parts no	price	qtd
111	A	3	4
111	B	3	2
222	A	3	4
333	A	4	5
444	B	7	1
555	C	2	4
666	C	8	4
777	C	1	5

Invoice and Customer has a common dimension Id (id from Invoice and invoice id from Customer)

In Customer and Items the common dimension is parts no.

The challenge is to Show all objects in a single table

Invoice Id	item	prts_det	cust_det	price	date_det
1	111	A	Roger Plank	3	1/1/16
1	222	A	Roger Plank	3	1/1/16
1	333	A	Roger Plank	4	1/1/16
2	111	B	Nadia Stella	3	10/2/16
2	444	B	Nadia Stella	7	10/2/16
3	555	C	Maria Cecilia Goulart	2
3	666	C	Maria Cecilia Goulart	8
3	777	C	Maria Cecilia Goulart	1

The final result is

To achieve this I did the following

1 - Merge Invoice and Customer on [invoice Id merged]

2 - Merge Customer and Items on [parts no merged]

3 - Create a detail variable [invoice id det Customer] with associated dimension =[parts no merged] and the formula =[Customer].[invoice id]

4 - Create a dimension variable [invoice id dim]= [invoice id det Customer]

4 - Create a detail variable [invoice id det Invoice] with associated dimension = [invoice id dim]  and the formula [Invoice].[id]

5 - Create a detail variable [customer name det] with associated dimension [Customer].[invoice id] and formula = [Customer].[customer name]

6 - Create a detail variable [date det] with associated dimension [Customer].[invoice id] and formula = [Invoice].[date]

7 - Create a detail variable [item det] with associated dimension [parts no merged] and formula [item].

Remember to check the table property "Avoid duplicate line agregation"

Now I can use all objects in the same block.

I´m working on a wid file to show the final result,

Regards,

Rogerio