Displaying all components for a product with each occurance of that product

[Former Member]

Hello all,

I'm using BO6.5 SP4 and have the following problem.

I have 2 queries.

The first one retrieves the customer and products sold.

The second one retrieves the components belonging to the products sold.

Suppose the result of the first query is:

Customer.........._Product_

Cust_1..............Prod_1

Cust_2..............Prod_1

Cust_3..............Prod_1

The result of the second query:

Product........._Component_

Prod_1..........Comp_A

Prod_1..........Comp_B

Prod_1..........Comp_C

Prod_1..........Comp_D

What I would like to see in the report is:

Customer........._Product_........._Component_

Cust_1.............Prod_1..........Comp_A

Cust_1.............Prod_1..........Comp_B

Cust_1.............Prod_1..........Comp_C

Cust_1.............Prod_1..........Comp_D

Cust_2.............Prod_1..........Comp_A

Cust_2.............Prod_1..........Comp_B

Cust_2.............Prod_1..........Comp_C

Cust_2.............Prod_1..........Comp_D

Cust_3.............Prod_1..........Comp_A

Cust_3.............Prod_1..........Comp_B

Cust_3.............Prod_1..........Comp_C

Cust_3.............Prod_1..........Comp_D

But what I get is the following:

Customer........._Product_........._Component_

Cust_1.............Prod_1..........Comp_A

Cust_2.............Prod_1..........Comp_B

Cust_3.............Prod_1..........Comp_C

Cust_3.............Prod_1..........Comp_D

Does anyone have a solution to this problem?

Regards,

Harry

[Former Member]

Hi Harry,

I Tried to do the same at my end but it is not allowing me to combine Customer, Product, Component even Product from 2 queries are linked same like yours. I can combine (Customer, Product) and

( Product1 ,Component) and (Product,component) but not All together (Customer, Product,Component) i.e. component canu2019t be added.

Please guide how you do that?

I have Few Queries :

1) Are these columns coming from same table or 2 different tables?

Like Customer, Product from one table and Product and Component from

another table.

2) If data is coming from single table. Please mention how the data is organized in the table

I tried creating the report using all columns from single table. I got the same result as yours

Customer.........Product.........Component

Cust_1.............Prod_1..........Comp_A

Cust_2.............Prod_1..........Comp_B

Cust_3.............Prod_1..........Comp_C

Cust_3.............Prod_1..........Comp_D

*I found one solution which solves your problem :

1) Create 2 separate tables i.e Customer_Product Table ( Customer,Product) and Product_Component Table (Product,Component)

2) Join 2 tables, Define Product Select Statement from any of the 2 tables.

3) Export the universe

4) Create report Combine columns you will get the data you want. (Cartesian Product)

I Hope this Helpsu2026..

Thanksu2026.

Pratik

[Former Member]

Hello Pratik,

Thank you for your response. It looks very promissing and I will try your solution.

In the meantime I already found a workaround by joining the data together outside Business Objects.

Using a specific tool (SyncSort) I was able to create the needed carthesian product and could help the user.

As for your question about the component. You are correct. The component cannot be added directly to the report together with the customer-product from the first query. You need to create a variable as a detail with the product as associated dimension and in the formula you add the component. You can then add that variable to the report.

Regards,

Harry

[Former Member]

Hi

How did you use Syncsort on unix ?

Thanks

Michael

[Former Member]

Micheal,

I didn't. I used Visual Syncsort on Windows.

Cheers,

Harry

[former_member212749]

Hi Harry,

IF you join the Product of Query1 and Product of Query2 hope you would get the Result that you need.

Regards

Prashant

[Former Member]

Hello Prashant,

Thank you for your response, but the dataproviders are already joined together by product. Tried by unlinking them but that gives total unusable results.

The actual queries return more than 1 product for each customer and each product has their set of components.

Unlinking the queries and for instance creating a section for each product will result in showing all components of all products within each section.

Linking the data provider again by product will result in the example shown (even when you have product as a master).

Cheers,

Harry

[Former Member]

Hi Harry,

What I can see from the query described by you is that it is displaying the unique results using the product as the primary key.

Could you please uncheck the option avoid duplicate row aggregation it might help to resolve the issue.

Under query options select the option duplicate rows it will help you to get the complete result set.

Regards,

Sarbhjeet Kaur

[Former Member]

Hello Sarbhjeet,

Any of those solutions described by you do not make any difference.

Remember I've got one query with unique customer and product sold combinations and another query with the products sold with the components.

I just want to show for each unique customer - product sold combination all components belonging to that product sold. But one way or another I don't seem able to make that happen.

Regards,

Harry

[Former Member]

Hi Harry,

Could you please provide me with the SQL you are using so that I can analyze the joins you are using.

Regards,

Sarbhjeet Kaur

[Former Member]

Hello Sarbhjeet,

Below the 2 SQL-statements. The first one retrieving the customer and product sold data, the second one the components of the products sold (I have removed additional conditions because those don't add any additional information for this subject).

SELECT DISTINCT 
  D_CUSTOMER.CUSTOMER_CODE, 
  D_PART.PART_CODE 
FROM 
  D_CUSTOMER, 
  D_PART, 
  D_PERIOD, 
  F_SALES  F_SALES_EUR 
WHERE 
  ( D_CUSTOMER.CUSTOMER_KEY=F_SALES_EUR.CUSTOMER_KEY  ) 
  AND  ( D_PERIOD.PERIOD_KEY=F_SALES_EUR.PERIOD_KEY  ) 
  AND  ( F_SALES_EUR.PART_KEY=D_PART.PART_KEY  )

SELECT DISTINCT 
  D_PART.PART_CODE, 
  F_COMPONENT_CHBT.SUBFAMILY 
FROM 
  D_PART, 
  F_COMPONENT_CHBT 
WHERE 
  ( D_PART.PART_KEY=F_COMPONENT_CHBT.PART_KEY  ) 
  AND  ( 
      @dpvalues('D_PART.PART_CODE',9,2)@dpend 
  )

What I'm actually trying to achieve is to generate some kind of cartesian product. I have tried to achieve that by manually altering the actual SQL-query, but that took hours to run without any result (and in my opinion it shouldn't give any result).

Regards,

Harry