Hi,

I need help to build a statement in MDX for Analysis Services.

I'd like to calculate the average grade for a certain exam.

I've got 2 measures: the number of people who made the exam and the sum
of all grades.

now the average would be: [Measures].[Sum Grades] / [Measures].[No of
attendends]

Every attendend who refuses to make the exam after seeing the questions
gets grade:0!

I want to exclude those people in my average calculation!!!!

So I need an MDX statement which does something like this:

[Measures].[Sum Grades] / ([Measures].[No of attendends] without those
who have grade 0)


Can anybody please help me with my statement!

Thanks

if you have a dimension called "Grades"
try something like:
aggregate(
exclude([Grades].[grade level].members, [Grades].[grade level].[Grade
0])
,[Measures].[No of attendends])

this will return all attendends except those in grade 0


"florian" <florian.stammer (AT) gmail (DOT) com> wrote

Hi florian,

You could use avg function which does exclude null when calculating
average as opposed to 0. To get around this problem, you could convert
0 into null and then use avg function. Here is an example for just one
of the ways of doing it. For the sake of clarification, the query
(based on Adventure Works sample) has some extra columns/members.


with
member measures.w as

iif(
[Measures].[Customer Count]=294
,
0
,
[Measures].[Customer Count]
)
member measures.x as

iif(
[Measures].w = 0
,
null
,
[Measures].w
)


member measures.y as
avg(
[Date].[Calendar]
.currentmember
.parent
.children
,
measures.x


)

select
{
[Measures].[Customer Count]
,measures.w
,measures.x
,measures.y
}
on 0 ,
[Date].[Calendar].[Calendar Quarter].&[2003]&[2]
..children
on 1
from
[Adventure Works]

HTH..

ZULFIQAR SYED
HTTP://ZULFIQAR.TYPEPAD.COM

florian wrote:

If you're using AS 2005, you could create a new named query on the fact
table, with where clause: Grade = 0. If a measure group is added with
this named query as its fact table, records with Grade = 0 will now be
excluded.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

where exactly can I create a new named query?

Thanks!

Deepak Puri schrieb:

http://msdn2.microsoft.com/en-us/library/ms175643.aspx

How to: Add, View, Modify, or Delete a Named Query Using Data Source
View Designer

Updated: 14 April 2006

In Microsoft SQL Server 2005 Analysis Services (SSAS), you use Business
Intelligence Development Studio to add, view, modify, or delete a named
query in an Analysis Services project or database.

To add a named query

In Business Intelligence Development Studio, open the project or connect
to the database that contains the data source view in which you want to
add a named query.

In Solution Explorer, expand the Data Source Views folder, then open the
data source view in Data Source View Designer by doing one of the
following:

Double-click the data source view.

Right-click the data source view and click Open.

Click the data source view and then on the View menu, click Open.

Open the Create Named Query dialog box by doing one of the following:

In the Tables or Diagram pane, right-click an open area and then click
New Named Query.

On the Data Source View Designer toolbar or on the Data Source View
menu, click New Named Query.

In the Create Named Query dialog box, do the following:

In the Name text box, type a query name.

Optionally, in the Description text box, type a description for the
query.

In the Data Source list box, select the data source against which the
named query will execute.

Type the query in the bottom pane, or use the graphical query building
tools to create a query.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***