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

Help on calculated member

I don't know how to do this.

I have one table "services" with a field "clasification" and a another table "serviceschange" with a field "clasificationchange". The two tables are related with foreign key. The serviceschange table has a field idservices (a fk to the services table).

I want to show in the fact table the name and the clasification of a service (the name is in the services table) but for the clasification i have to do this:

If the id of the service is in the serviceschange table i have to show the clasifiactionchange of this table, but if the serviceschange doesn't have the id i want to show the field clasification of the services table. How i can do this? is it possibly?

Assuming AS 2005, and that services is a dimension, classification can be defined as an attribute of the services dimension. A named query like the following could be created in the DSV, to be used as the dimension table for services (if there is at most 1 entry for a service in serviceschange - otherwise grouping may be necessary):

>>

select sv.idservices, sv.servicename, coalesce(sc.classificationchange, sv.classification) as classification

from services sv

left outer join serviceschange sc

on sv.idservices = sc.idservices

>>

sql

Help needed in the calculated column on the bases of other column

select myname,mydate,myduedate,mymonthCal,mydaysleftCal,mycategoryCal,mydateyear_monthCal from tbltest

myname and mydate are in the database wh ich are value who are not allowed null.

mymonthCal,mydaysleftCal,mycategoryCal and mydateyear_monthCal are calculated column on the basis of myname and mydate

myname mydate myduedate
aa 3/31/2005 12:00:00 am 5/31/2005 12:00:00 am
12/1/2005 12:00:00 am 12/30/2006 12:00:00 am
cc 12/12/2006 12:00:00 am

expected resultset
myname mydate mymonthCal, mydaysleftCal, mycategoryCal, mydateyear_monthCal
aa 3/31/2005
bb 12/1/2005
cc

1,mymonthCal

if mydate is 12-31-2005 12:00:00 am THEN mymonth should be Dec-05

2,mydaysleftCal

if myduedate is empty it will return 39,126
other wise it will first check if myname is empty [remeber not null just ''] it will return empty string else
return the difference of current date with mydate

3,mycategoryCal

it is calculated by the excel formula on the bases mydaysleft

=IF(mydaysleftCal="","",IF(mydaysleftCal<=0,0,IF(mydaysleftCal<=10,"0 - 10",IF(mydaysleftCal<=30,"11 - 30",IF(mydaysleftCal<=60,"31 - 60",IF(mydaysleftCal<=90,"61 - 90",IF(mydaysleftCal>90,"90+","")))))))

4,mydateyear_monthCal

if mydate is 3/31/2005 12:00:00 am and month is less then 10 then mydateyear_monthCal should be like 2005_3 where 2005 is the year and 3 is the month
if month is greater then or equal to 10 then mydateyear_monthCal should be like 2005_x3

I didn't exactly understand what you were attempting to accomplish in step 4 (MyDateYear_MonthCal) -so I left it up for you to verify. However, I think that I have illustrated several concepts that 'should' help you move forward with this query. (A couple of datetime functions, a couple of string functions, CASE structures for switching, and the use of derived tables in place of an actual table.)

In the calculation of MyDaysLeftCal, since a number is being calculated, zero will display instead of NULL or empty string ''. Also, note that MyDate is NULL or empty string '', the date will appear as '01/01/1900'.

Code Snippet

DECLARE @.MyTable table
( MyName varchar(10),
MyDate datetime,
MyDueDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'aa', '3/31/2005', '5/31/2005' )
INSERT INTO @.MyTable VALUES ( 'bb', '', '' )
INSERT INTO @.MyTable VALUES ( 'dd', '04/12/2007', '04/30/2007' )
INSERT INTO @.MyTable VALUES ( 'ee', '1/31/2007', '3/31/2007' )
INSERT INTO @.MyTable VALUES ( '', '12/1/2005', '12/30/2006' )
INSERT INTO @.MyTable VALUES ( 'cc', '12/12/2006', '' )

SELECT
MyName,
MyDate,
MyMonthCal,
MyDaysLeftCal,
MyCategoryCal = CASE
WHEN MyDaysLeftCal = '' THEN ''
WHEN MyDaysLeftCal <= 10 THEN '0-10'
WHEN MyDaysLeftCal <= 30 THEN '11-30'
WHEN MyDaysLeftCal <= 60 THEN '31-60'
WHEN MyDaysLeftCal <= 90 THEN '61-90'
ELSE '90+'
END,
MyDateYear_MonthCal
FROM ( SELECT
MyName,
MyDate = convert( varchar(10), MyDate, 101 ),
MyMonthCal = ( left( datename( month, MyDate ), 3 ) + '-' +
( right( cast( year( MyDate ) as char(4) ), 2 ))),
MyDaysLeftCal = CASE
WHEN MyDueDate = '' THEN 39126
WHEN MyName = '' THEN ''
ELSE datediff( day, MyDate, getdate() )
END,
MyDateYear_MonthCal = CASE
WHEN month( MyDate ) > 10 THEN
( cast( year( MyDate ) as varchar(4) )) +
'_x' +
cast( month( MyDate ) as varchar(2) )
ELSE ( cast( year( MyDate ) as varchar(4) )) +
'_' + cast( month( MyDate ) as varchar(2) )
END
FROM @.MyTable
) dt

MyName MyDate MyMonthCal MyDaysLeftCal MyCategoryCal MyDateYear_MonthCal
- - - - - -
aa 03/31/2005 Mar-05 737 90+ 2005_3
bb 01/01/1900 Jan-00 39126 90+ 1900_1
dd 04/12/2007 Apr-07 -5 0-10 2007_4
ee 01/31/2007 Jan-07 66 61-90 2007_1
12/01/2005 Dec-05 0 2005_x12
cc 12/12/2006 Dec-06 39126 90+ 2006_x12