BusinessObjects Board

Can't match data in 2 dataproviders

building reports desktopintelligence
#1

Hi Experts,

I am trying to build report in Deski and faced with a some issue.
there are 2 queries in report.
1-st:
Country | Region |Customer | Category | material | qty
Country1| Region1| Customer1| Category1| material1| 1
Country1| Region1| Customer1| Category1| material2| 2
Country1| Region1| Customer1| Category1| material3| 3

2-nd:
Country |Category |materials |coefficient
Country1|Category1| material1| 1.0
Country1|Category1| material2| 1.0
Country1|Category1| material4| 1.0

Need to build table like:
Country , Region, Customer, Category, material, qty, Coefficient
Country 1, Region1, Customer 1, Category 1, material 1, 1, 1.0
Country 1, Region1, Customer 1, Category 1, material 2, 2, 1.0
Country 1, Region1, Customer 1, Category 1, material 3, 3, -
Country 1, Region1, Customer 1, Category 1, material4, - , 1.0

Is it possible to realize this on table level via formulas and datails? or we need to calculate on a dwh side with an intermediate precalc. tables.

Tools: BOXI XI 3.1 sp6, Oracle 11g.

Any suggestions.
Thanks in advance.

highscreen :netherlands: (BOB member since 2018-03-01)

#2

Welcome to B:bob:B!
Look in to merged dimensions.

Nick Daniels :uk: (BOB member since 2002-08-15)

#3

Nick, thank for your fast response.
Unfortunatelly it doesn’t work.
I get result:

Country , Region, Customer, Category, material, qty, Coefficient
Country 1, Region1, Customer 1, Category 1, material 1, 1, 1.0
Country 1, Region1, Customer 1, Category 1, material 2, 2, 1.0
Country 1, Region1, Customer 1, Category 1, material 3, 3, -
Country 1, - , - , Category 1, material4, - , 1.0. - is wrong, because need to have possibility for setting filters on customer and other dimensions

Main issue is appending a set(category,material) from query 2 for each Region and Customer and adding this not empty string into customer if it is not in query 1.

Qty for added string may mark as ‘-’, ’ ’ , 0. It doesn’t matter.

Hope, it is possible to realize via details/formulas/ additional queries with union.

highscreen :netherlands: (BOB member since 2018-03-01)

#4

Suggest you read a bit more about merged dimensions and what you csn do with them.

Nick Daniels :uk: (BOB member since 2002-08-15)

#5

try link country to country, category to category. and make materials DETAIL

bonotatsujin :taiwan: (BOB member since 2017-12-20)