Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jojo_jacob
Starting Member
24 Posts |
 Posted - 2004-05-09 : 04:56:33
|
| I am having a dimension with 3 levels. Level-1, Level-2 and Account. The problematic part is that the Account (the lowest) can repeat in multiple places in Level-1 and/or Level-2. An example is as follows:DSF(Level-1)----> RVF(Level-2)---->Account1, Account2, Account3DSF(Level-1)----> EFC(Level-2)---->Account2, Account4Here Account2 is repeated in both the Level-2. Now, if a sum measure is 10 for Account1, 20 for Account2 and 30 for Account3 and 40 for Account4, the RVF(Level-2) should show 60 and EFC(Level-2) should show 60. However DSF(Level-1) should NOT sum Account2 twice and should show the unique sum of Accounts which is 100. Without doing any custom formulae and calculated members, it now shows 120. How can we achieve to get this working?Something like distinct sum ???Many thanks in advance.Jojo |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-05-11 : 08:38:33
|
| DDL and DML with sample data and expected results would help a lot here. It doesn't have to be actual data just data in the tables that simulates your problem. |
 |
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-05-12 : 06:19:54
|
| I have a dimension table with 3 columns: Level1, Level2 and Account. One Account can be repeated in multiple levels (Account is not the primary key). In the Fact table, each record is assigned to an Account. The measure is a SUM. So, even if Account is repeated in two levels, both the levels show the same figure against the same account which is fine. However, the upper level should not SUM twice for the same account. Hope, this is clearer. Thanks. |
|
|
|
akakar
Starting Member
2 Posts |
Posted - 2004-05-28 : 17:27:04
|
| I'm having a similar conundrum. Any help for us? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-28 : 17:30:38
|
| Got DDL and DML? DDL is CREATE TABLE statements for your tables, not just showing us what they look like but the actual code to create them. And DML is INSERT INTO statements for sample data. Then we need expected result set using the sample data. We need all of this information so that we can copy the code into our machines and test it out and come up with a query.Tara |
|
|
|
|
|
|
|
|
Distinct Sum ???