duplicate values

hey guys,

ive run into an issue and im not quite sure how to fix it.. let me explain

i have a cube, by market and i wish to show sales and freight costs.

The freight Fact table has no relationship to the market dimension as freigth is charged on customer order level, not item level.

I have data showing like the following:

Year

2006

Market

Sales

Freight Amount

Auto OEM

$7,115,315.22

$56,620.73

Auto Repl

$18,517,004.07

$56,620.73

Intercompany

$4,772,910.37

$56,620.73

Ind OEM

$12,039.74

$56,620.73

Ind Repl

$18,679,313.71

$56,620.73

Other

$84,263.19

$56,620.73

Grand Total

$49,180,846.30

$56,620.73

I would only like the freigth amount to show at the Grand Total Level. Is this possible? or, if not, is there a way you can combine two dimensions into 1? ie, combine a Freight Account dimemsion and the Market Dimension so that i can see Markets, then the different Freight Accounts by rows?

Thanks in advance,

Scotty

If you look in the cube structure tab in BIDS, you will see that each measure group has a property called IgnoreUnrelatedDimensions, this defaults to true which means that when you slice a measure by an unrelated measure you see the total amount (which is the behaviour you are seeing). Changing this property to false will mean that the measures will only display when they are either sliced by a related dimension or at the highest level.

It's hard to be sure, but from what you have said I don't think combining the two dimension would work.