help on calculation including nulls

Hi everybody

I have this sql which calculates the total

MY TABLE IS:

A B C

100 5 3
100 0 5
20 0 0

sql is

A - B + C as total

the result is it gives me only the total of the first row since it has values on the on the B AND C

the result is

A B C total

100 5 3 98
100 0 5
20 0 0

with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...

thanks for any helpRead up on the COALESCE function in the (online) help files.

COALESCE(Field, ValueIfNull)|||Thanks solved my problem and thanks for reminding me on the way of saying thanks in advance..|||you can also use isnull-

select a, b, c, (isnull(A,0) - isnull(B, 0) + isnull(C,0)) as total|||coalesce is generally preferred as it's ANSI. also it can take multiple args.|||Yes thats right, COALESCE have several advantages over ISNULL

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html
http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx (http://http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx)

readability is the only advantage of using ISNULL.|||readability is the only advantage of using ISNULL.

readability? what's more readable:

select ISNULL(a, ISNULL(b, ISNULL(c,d)))

select COALESCE(a,b,c,d)

obviously I prefer coalesce. :)|||IsNull is more readable imo.
Before I learnt of coalesce I had no idea what it meant.|||IsNull is more readable imo.only for two values, and even then it's six of one, six of the other

ISNULL(foo,0)
COALESCE(foo,0)

now try it with multiple expressions, and see if ISNULL is really more readable|||Readability -

Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

this is what databases.aspfaq.com says.

I just wanted to tell alexyeth about the alternative available.
I alway prefer COALESCE.|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

IMO - the fact you don't know what coalesce means is an advantage. Come across it and you have to look it up. Come across ISNULL as a vb programmer and you might think you know what it does.

BOOOOOOOOO to T-SQL ISNULL!|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

I'm pretty sure sql server inherited this function from sybase. doesn't change the fact that ISNULL is a stupid name for the function though. :)|||Goddam Bill lover ;)|||eh, I have a soft spot for sql server, that's all. ;)

Help on Calculated Members

Hi Gurus,

I am new to Microsoft Analysis Services. I wanted to create a new Average Calculation.

The Syntax is

Avg(?Set[, ?Numeric Expression]). I am using the Foodmart cube.

I have tried using many things but have not achieved any success.

These are the following things that I have tried.

1. Avg([Product].[Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk] , 'Avergare of Milk')

In this 'Avergare of Milk' is some sort of Calculation name I suppose.

2. Avg([Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk])

In this ‘6’ I have mentioned assuming there are only 6 members in the hierarchy.

Please tell me What shall I write in place of Set and Numeric Expression.

I also came across an article and found this

“If you want to create an average, what you need to do is create one
measure
called SUM, and another measure based on COUNT and then create a
calculated
member called AVG which would just be SUM/COUNT. I believe there are
examples of this in the Foodmart 2000 cubes.”

But to my Surprise I also found the Syntax of SUM and Count to be similar.

Please help me out in this.

I also have a request Could anyone give me some examples and any link from where I could learn all the available calculation with examples.

Thanks in advance

Here is an example that should help you:

with member [Measures].[Av1] as
'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av2] as 'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, coalesceempty([Measures].[Profit],0))'
member [Measures].[Num] as 'count({descendants([Product].[All Products].[Drink],[Product Name])})'
member [Measures].[Sum] as 'sum({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av3] as '[Measures].[Sum]/[Measures].[Num]'

select

{[Measures].[Av1],[Measures].[Av2],[Measures].[Av3],[Measures].[Num],[Measures].[Sum]} on columns,

{[Store].[Store Name].members} on rows
from sales

As you can see, you Avg1 is different from Av2 because it doesn't consider empty cells, so the average is slightly bigger.

Hope this help,

Santiago

|||

Hi,

Thanks for the help. But I think I was not clear what I wanted. I am using Analysis manager (Microsoft SQL Server -> Analysis Services -> Analysis Manger).

In that I am drilling down the Analysis Servers to list the Available servers and then drilling down the database present to get the list of the available cubes.Now I am right clicking on a cube and choosing edit to open the Cube Editor.In Cube editor I am trying to create a new Calculated Members. (In the Calculated member Builder I am unable to write the query for the Average.) There I am not able to make the calculation. And am unable to sort out the problem.

I hope this information help in giving my problem the perfect picture.

Please let me know if u need anything else

Thanks in advance

|||

In Calculated Member builder, select Measures as the Parent Dimension, write the name of the measure (eg. MyAverage) and type the appropriate mdx expression. For example:

Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])

You don′t need then to reprocess the cube. Just browse it and you should be able to see the new Calculated Member.

|||

HI,

Many many thanks for the help. It worked.

Can I get some link from where I can get some examples of the calculation.

Thanks