Conditional Calculated Column - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Conditional Calculated Column

Author

Topic

Tomco21
Starting Member

6 Posts

Posted - 2011-01-05 : 08:46:24

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...

Child__| Group__ | Class__ | Amount
-------------------------------------
Steven | Group A | Class 1 | 3000
Dan___ | Group B | Class 1 | 4000
Grace_ | Group A | Class 2 | 2000
Jane__ | Group B | Class 2 | -3000

I want to create a conditional calculation on the Amount column based on the sum of the lowest level, so if the sum => 0 then output Amount otherwise output 0.

So when i browse on Group -> Child the output will be like...
Group A 5000
- Steven 3000
- Grace 2000
Group B 1000
- Dan 4000
- Jane -3000

But when i browse on Class -> Child the output would be like...

Class 1 7000
- Steven 3000
- Dan 4000
Class 2 -1000
- Grace 0
- Jane 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-16 : 17:49:08

You can use a recurce Common Table Expression for this.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources