Skip to Content
Author's profile photo Former Member

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
Captura de Tela 2016-03-15 às 19.55.15.png

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

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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...

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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...

      Author's profile photo Adam Wilson
      Adam Wilson

      I realize this is several months later, but check my comment, below, to get the files.

      Author's profile photo Adam Wilson
      Adam Wilson

      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")