need help Report studio issue

[MFGF]

Suly wrote in a personal message:

I'm sorry for bothering you in Personal message, but in few months ago, I post a them for that but nobody have a answer, or I can't explain very well. But the problem is huge for me, and my boss will kill me if i don't figure it out how to fix it.
So, I have relational data base - DB2, with data warehouse, my model is relational. I suppose to create a detail table for online sales for  products, but for one online reservation(sales-with total amount for different goods), can have different goods(with different price). For online reservation I have a code(for all goods in the reservation), and every goods have number too. The problem is when I sum the total amount for online sale reservation, because for every goods i have row with different amount, but with total amount for reservation, and in RS i cant calculate the hole amount for reservation just once.
Example:
Reservation code: R4434335 total amount 50    goods number 45356 price 15
Reservation code: R4434335 total amount 50    goods number 45357 price 15
Reservation code: R4434335 total amount 50    goods number 45358 price 20
totals:     for reservation RS total (150)                          goods total: 50
the true calculation is: reservation 50                       goods:50
I try group by reservation code, and to group reservation price with group span and I see this:
Reservation code: R4434335 total amount      goods number 45356 price 15
Reservation code: R4434335 total amount  50  goods number 45357 price 15
Reservation code: R4434335 total amount      goods number 45358 price 20
but when i put a total for reservation, again is 150, how can fix this.
Relationships between reservation and goods is 1..n=1..1
Regular aggregation in FM for reservation price is unsupported, in RS i try: Automatic, total, summarize, calculate but still the total is for all rows.
Please help me

This sounds to me like a modelling issue in your FM model. If you want Amount and Price to be aggregated differently, they should be in different "fact" query subjects, each linking to a common dimension containing both Reservation details and Goods details. One "fact" query subject would contain the Reservation code (usage=Identifier) and the Amount measure (usage= Fact) and would join to the dimension based on Reservation Code. The other "fact" query subject would contain Reservation code (usage=Identifier), Goods Number (usage=Identifier) and Price (usage=Fact) and would join to the dimension based on both Reservation Code and Goods Number. Finally, your dimension would have two determinants - the first based on Reservation Code (used as the Key) and "Group by" checked, and the second based on Reservation Code and Goods Number (used as the key) with "Uniquely Identified" checked.

Modelling in this way would aggregate the Amounts for each Reservation and aggregate the Prices for each Goods item (which appears to be what you require) when using them in any reports you author.

Cheers!

MF.

[spmaganti]

This a truly modeling issue as MFGF said. But if it can't be fixed in the model then you might have to generate your own totals for [Reservation code] and [Goods number] by creating data items in the report studio something like this:

total(distinct [Amount] for [Reservation code]) or average([Amount] for [Reservation code])
total([Price] for [Reservation code])

Just some ideas if you haven't already tried

[suly]

Thank you so much.....

First way is more complicate, but most proper, because I really mess out the hole model!
The second way is  easier and works as well!

Thanks again for the help!!!

[suly]

Hi MFGF,

Today I finally have time to tray your method.
Now in the model I have:
1. Sale_fact table - Reservation_code, month_key, price, status, operator_key
2. Goods_fact table - goods number, goods_status, month_key, goods_price, operator_key
The two fact tables are join with common dimension - Month_key, operator_key
I try to create new model query subject:
1.Reservation_code_fact - reservation_code, amount_price
2.Goods_number_fact - goods_number, reservation_code(from sale_fact), price
3.Res.code.goodsnumer_dim - reservation_code, goods_number, month_key, operator_key, status and some other details.
But I can join the two fact query subjects because ''xqe-pln-0203 with respect to query subject , the combination of a) the effective sql generation being "as view" and b) the query subject using multiple facts, is not supported.

So I try to create the same query subjects but from data source(where for goods_fact, and for dimension I have to put a query items from two tables, is this ok, should I type something in join expression?)
I create the three query subjects, but you write that I have two join goods_number_fact with dimension both with Reservation_code and goods_number - so I will have two relationships?
I think that I suppose to create relationship between:
goods_number_fact.reservation_code=Res.code.goodsnumer_dim.reservation_code 1..1=1..n
reservation_code_fact=Res.code.goodsnumer_dim 1..1=1..n
Res.code.goodsnumer_dim.month_key = month_key_dim.month_key  1..1=1..n
and then in Res.code.goodsnumer_dim I have to create determinants?

Is this a right way?

[MFGF]

The two fact tables are join with common dimension - Month_key, operator_key

Can you explain what you mean by this? Are you saying the two fact tables each join to common dimension tables based on these keys (ie to a time dimension table and an operator dimension table) or are you saying the two fact tables join directly to each other using the specified keys?

MF.

[suly]

The two fact tables each one is join to common dimension (time, and operator).

[suly]

Can you explain what you mean by this? Are you saying the two fact tables each join to common dimension tables based on these keys (ie to a time dimension table and an operator dimension table) or are you saying the two fact tables join directly to each other using the specified keys?

MF.

The two fact tables each one is join to common dimension (time, and operator).

MF please can you help me and explain what I'm doing wrong?