If you are unable to create a new account, please email support@bspsoftware.com

 

Joining queries on fact tables with different data and granularity on report net

Started by flavioctc, 18 Oct 2006 04:21:05 PM

Previous topic - Next topic

flavioctc

Hi everybody,
We need to create a report that joins data which have different granularity on Time dimension and is spread on 2 different fact tables.
LetÃ,´s say that it is like the example below:
query A - table A - sales price - 1 record
Dim 1 - Item - prompted dim value
Dim 2 - Time - max(dim value)
Fact A - price sales

The price fact table have only these 2 dimensions.

query B - table B - sales orders - multiple records
Dim 1 - Item - prompted dim value(same as query A)
Dim 2 - Time - any period that has sales
Fact B - quantity ordered

Sales Orders fact table have other dimensions besides these 2 that are not relevant at the moment.

The Desired result looks like:
ITEM TIME QUANTITY PRICE
A 2006/01 32 6.9
A 2006/02 24 6.9
and so on...

Is there any way to achieve this at report studio without writing SQL code?

Thanks in advance ,
Flavio AmorimÃ, 

uyravikumar

hi,

I did not tried but assuming that

TABLE - AÃ,  Ã, - Having One record per Item
1.ITEM
2.TIME
3. PRICE

TABLE-B - having More than one Reocrd per Item
1.ITEM
2.TIME
3.QUANTITY

TABLE - A.ITEM <--> TABLE-B.ITEMÃ, 
CARDINALITY --> TABLE-A. 1.1Ã,  -- TABLE-B. 1..N

Then Take List

TABLEB.ITEM
TABLEB.TIME
TABLEB.QANTITY
TABLEA.PRICE

I hope this will work...

Regards.