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.

Duduce difference

Hi all,
I have a query that adds up two tables as below:
SELECT SUM(RPAAP / 100) AS [Sales Ledger]
FROM F03B11
SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)
I would like to take this a step further and deduce the difference
between the two. However I?m not sure how this should be done.
Any help would be most welcome.
Sam
?new person on windows 2000?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Use sub queries thus:
select [Sales Ledger] - gl as difference
from
(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1 join
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2
Assuming Q2 returns just the one row
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||SELECT SUM(RPAAP / 100) AS [Sales Ledger] -
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3))
FROM F03B11
Hope this helps
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!