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 needed with SQL SERVER 2000 Analysis Manager

I am going through the tutorial and I got to the portion on Set Dimension Custom Rollups, Write Back and more. I selected the Parent-Child dimension, and followed all of the steps as directed until it asked me to Select advanced options. The Write Back option is not listed and it has to be selected in order for me to complete this section. Please help.Same for me, I even try to change connexion without any success
any help ?

Help me please

Hi,
this is my first post..Please help me out!
I am using a dataset from Analysis services and i built a report in tabular
fashion with drilldown ( I used report wizard ). I also created a simple bar
chart...
Now my problem is:
Is it possible to make a drilldown in the graph as well...?
i.e.for example: if the graph loads initally with country (x-axis) and
sales(y-axis)
i need the graph to give me the sales data with individual states in the
country and split sales data..am i making sense?..
Thanks for your valuable time.
P.S: I am using Sql server 2005 Beta 2.
Sincerely,
SankarI answered your question on the MSDN Forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=22371
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sankar" <sankar_s_07@.hotmail.com> wrote in message
news:%23m8gjEDeFHA.3376@.TK2MSFTNGP10.phx.gbl...
> Hi,
> this is my first post..Please help me out!
> I am using a dataset from Analysis services and i built a report in
> tabular
> fashion with drilldown ( I used report wizard ). I also created a simple
> bar
> chart...
> Now my problem is:
> Is it possible to make a drilldown in the graph as well...?
> i.e.for example: if the graph loads initally with country (x-axis) and
> sales(y-axis)
> i need the graph to give me the sales data with individual states in the
> country and split sales data..am i making sense?..
> Thanks for your valuable time.
> P.S: I am using Sql server 2005 Beta 2.
> Sincerely,
> Sankar
>|||Thank you Robert..
I shall download the latest version.
Sincerely,
Sankar
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e$PYQPHeFHA.544@.TK2MSFTNGP12.phx.gbl...
> I answered your question on the MSDN Forum:
> http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=22371
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "sankar" <sankar_s_07@.hotmail.com> wrote in message
> news:%23m8gjEDeFHA.3376@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > this is my first post..Please help me out!
> >
> > I am using a dataset from Analysis services and i built a report in
> > tabular
> > fashion with drilldown ( I used report wizard ). I also created a simple
> > bar
> > chart...
> > Now my problem is:
> >
> > Is it possible to make a drilldown in the graph as well...?
> >
> > i.e.for example: if the graph loads initally with country (x-axis) and
> > sales(y-axis)
> > i need the graph to give me the sales data with individual states in the
> > country and split sales data..am i making sense?..
> >
> > Thanks for your valuable time.
> > P.S: I am using Sql server 2005 Beta 2.
> >
> > Sincerely,
> > Sankar
> >
> >
>