Merging 3 Data Providers compatible 2 by 2
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
Wow thank you for the post. It is clear.
1. Why do we have to create Dimension Variable from Detail Variable?
2. Is there anyway to open Wid file on Webi? I do not have desktop rich client.
Hi H.T
1 - The reason for that is I need a dimension to create a detail variable, as I cannot use [invoice id det Customer] because it´s a detail, I create the dim var;
2 - Unfortunately you don´t have another way of opening it.
Regards,
Rogerio
Hi Rogerio,
I have been banging my head against my wall in the 3 days 🙁
If you do not mind, could you please give me 3 quick tips?
1. Does order of columns matter in the table...?
2. Regarding Parts No. column in your table----is it Merged Dimension or do you insert Parts No Dimension from one of your queries?
3. Do you need to insert two newly-created Details (Invoice Ids from Invoice & Customer) in the table for it to work...?
Sorry for disturbing you but I would be forever grateful if you could help me.
I will keep researching what I am doing that is not making it work...
Thank you...
Hi H.T,
never mind, we´re here to help.
1 - No, the order of the columns don´t matter;
2 - The variable is defined over the merged dim
3 - It´s a good idea to keep the details in the table.
I intend to post the wid correspondent to this sample later today.
Regards,
Rogerio
Thank you so much for your reply.
I could not open it as Excel but thank you anyway.
I hope this excellent post of yours benefits many BO users as a whole...
I realize this is several months later, but check my comment, below, to get the files.
Thank you so much for this post! I will be analyzing it a lot and learning its ways, as many of our reports have similar needs.
A side note about the attached file: when I downloaded it the filename was "3providers.txt.zip". I unzipped it and it created a file called "3providers.txt", but when I opened this file it was binary data. When I renamed it to "3providers.zip" none of my unzip utilities recognized it. I opened it again in a text editor and noticed that it began with the letters "Rar", so I renamed it to "3providers.rar" and then my decompression utilities recognized it and I was able to get the files.
So for others who want to get to the attached file:
1. download it (the file is named "3providers.txt.zip")
2. decompress/unzip the file (it will create a file called "3providers.txt")
3. rename the file from "3providers.txt" to "3providers.rar"
4. decompress/unrar the file (it will create the files "3dateproviders.wid" and "3prov.xls")