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 |
|
Tomco21
Starting Member
6 Posts |
 Posted - 2011-01-04 : 18:51:40
|
| Hi, thanks to everyone who looks at this, this has puzzled me for a week now and i haven't been able to find a solution. I'm fairly new to cube design so please bear with me.I have the data...Group | Class | Amount---------------------------Group A | Class 1 | 3000Group A | Class 1 | 4000Group A | Class 2 | 2000Group A | Class 2 | -3000I want to create a conditional calculation on the Amount column based on the sum of the previous level, so if the sum => 0 then output Amount otherwise output 0. For example if i drop Group in as the dimension and CalculatedAmount as the measure, the sum of Amount = 6000 so CalculatedAmount will be left as 6000. If however i drop a hierarchy of Group -> Class as the dimension and CalculatedAmount as the measure Class 1 will be given the CalculatedAmount of 7000, Class 2 will be given the CalculatedAmount of 0.Is this possible? Does this make sense? As i say i am fairly new to this so i may be going about it completely the wrong way, any hints or advice would be greatly welcomed.Thanks,Tomco |
|
|
Tomco21
Starting Member
6 Posts |
Posted - 2011-01-04 : 19:29:11
|
| I've realised the data set i've provided doesnt really explain what i am trying to do, using the example above i understand i could just write a simple IF on Amount and return the sum or return 0. So please imagine if you will the following dataset...Child | Group | Class | Amount-------------------------------------Steven | Group A | Class 1 | 3000Dan | Group B | Class 1 | 4000Grace | Group A | Class 2 | 2000Jane | Group B | Class 2 | -3000When i browse on Group -> Child the output will be like...Group A 5000- Steven 3000- Grace 2000Group B 1000- Dan 4000- Jane -3000But when i browse on Class -> Child the output would be like...Class 1 7000- Steven 3000- Dan 4000Class 2 -1000- Grace 0- Jane 0 |
 |
|
|
|
|
|
Conditional Calculated Column for my Cube