Please help to create Calculated members

MS SQL SERVER

    Sponsored Links

    Next

  • 1. Microsoft Exam 70-455: what is a "matrix bridge"?
    Hi In the list of topics for Microsoft Exam 70-455 there is a heading "Creating a dimensional model". Three of the entries under this heading are well-known (star schema, snowflake and conformed dimensions). But one of the entries has me stumped: "matrix bridge", a piece of terminology that I have only come across before in the context of electronics. I can just about see that there could be an analogy in dimensional modelling, but I would like some specifics. Needless to say, I googled this term. Suspiciously, the only hits were sites that simply reproduced the content of the exam syllabus. Nobody proferred a definition. Can anyone enlighten me with a definition/reference/explanation of the term "matrix bridge". Regards Peter8888
  • 2. MDX: Display Sub Total & Grand Totals (States by Region)
    Hi, We have a 'Geography' dimension. Region ==> State. I need help in displaying States by Region. Then display Sub Totals by Region & then a Grand Total for all regions. Sample Output: -------------------- NE CT 20 NE NY 30 NE RI 30 ------------------ NE NE 80 ------------------ MA DC 10 MA DE 20 MA PA 30 ------------------- MA MA 60 -------------------- SE AL 20 SE AR 30 SE GA 50 ---------------------- SE SE 100 --------------------- GT GT 240 --------------------- Any help on the sample MDX is much appreciated. Thanks, Prasad NE
  • 3. Header table measure
    I'm trying to build a dimensional model where the fact table will consist of data from a header and detail table. There are measures in both the header and detail tables. How do I create a measure on a header table column without duplicating the value for every detail record?

Please help to create Calculated members

Postby Sam » Fri, 15 Sep 2006 01:55:51 GMT

Year----2005----1stHalf
	     ----2nd Half
                     ----I need to create a calculated member under
2005 which is Avg of 1st & 2nd Half

Year ---2006--- Same thing

I have a Time dimension and we have 2001-2006 years and the way we
calucaluted FY scores is 1st and 2nd Half average

Thanks in advance
Sam


Re: Please help to create Calculated members

Postby ZULFIQAR SYED » Fri, 15 Sep 2006 13:53:52 GMT

Hi Sam,

Try using Avg function on currentmember.parent.children as follows. I
tested this sample code against ss2k5 adventure works olap database.

with member measures.x as
avg(

	[Date].[Calendar].currentmember.parent.children,

	[Measures].[Reseller Sales Amount]
)
select
{
[Measures].[Reseller Sales Amount]
,measures.x
}
on 1,
{
[Date].[Calendar].[Calendar Semester].&[2002]&[1]
,[Date].[Calendar].[Calendar Semester].&[2002]&[2]
,[Date].[Calendar].[Calendar Semester].&[2003]&[1]
,[Date].[Calendar].[Calendar Semester].&[2003]&[2]
} on 0
from
[Adventure Works]

HTH..

Thanks

ZULFIQAR SYED

 http://www.**--****.com/ 





Re: Please help to create Calculated members

Postby Sam » Sat, 16 Sep 2006 00:44:17 GMT

Thanks!!!

But I am using pivot table to access the cube. So I need to implement
the below logic in the calculated members for Time Dimension. So if
they pull TIME dimension they can see 3 children i.e.
2006
-- 1st Half
-- 2nd Half
-- FY (Calculated mebers Avg of 1st and 2nd Half)

so we have around 15 measures so i client pull all the 15 measures then
it should take the avg of all the 15 measures.

Thanks
Sam







Similar Threads:

1.Created Calculated Member from Calculated Member

I have a Measure that gives me a count of terminated emplyees and a 
calculated member that gives me the average number of active employees at 
each level. I would like to create a calculated member that gives me the 
turnover percentage by dividing the termination count with the average number 
of actives. This is what I created, 
"[Measures].[Termcount]/[Measures].[AvgEmpCount]" but it gives me a blank 
value at the bottom level and a value of 1 at each level above that. What am 
I doing wrong?

2.calculated member as parent for another calculated member

Hi eb
M trying to asign a calculated member to be the parent member of another 
calculated member.
In this case AS is saying he can not identify the first cakculated member..
Is this possible at all??

TIA
Rea

3.Using an aggregated calculated member in a new calculated member

Hi all!
I'm a pro SQL Server developer but new to MDX and have problem to use an 
aggregated value in subsequent calculations.

Background: I've got a simple cube with three dimensions; Department, Person 
and Time. Only the time dimension is actually a hieararchy (Year-Month-Date), 
Person and Department only have one level. The fact table have two values, 
Value1 and Value2 (both can be positive an negative). Uses SQL2005 SP2.

Problem: I want to calculate, for each PERSON, Department and Month, the 
maximum sum of Value1 and Value2, i.e. like MAX(SUM(Value1),SUM(Value2)). 
Then, for each Department and Month, I want the sum of the POSITIVE values of 
these sums. 

This measure does the first part of the job(at least I can se the correct 
sum per person, deparment and month in the browser):

CREATE MEMBER CURRENTCUBE.[MEASURES].[MaxValue]
 AS IIF(([Time].[Calender date].CurrentMember.Level IS [Time].[Calender 
date].[Month]
		OR
     	 [Time].[Calender date].CurrentMember.Level IS [Time].[Calender 
date].[Date]
	)
    ,(MAX( {([Measures].[Value2]),([Measures].[Value2])}))
    ,(null)), 
VISIBLE = 1;                

I've tried to figure out how to create a calculated mesaure to sum up the 
positiv sums of MaxValue, but then I always loose the fact that it should 
compare the sums per PERSON also.

Am I on the right track or am I thinking completly wrong?? 

Any suggestions or hints are much appreciated!

Martin 

4.Calculated member with a calculated member parent

I am trying to create a calculated member which has another calculated
member as its parent.  For instance, here's an MDX statement:

WITH
MEMBER [Active Date].Fiscal.[All Active Date].[CurrentYear] AS
'Filter([Active Date].[Fiscal].[Fiscal Year].Members, [Active
Date].[Fiscal].CurrentMember.Properties("Current Fiscal Year") =
"1").Item(0)'
MEMBER [Active Date].Fiscal.[All Active Date].[CurrentYear].[CurrentQuarter]
AS 'Filter([Active Date].[Fiscal].[Fiscal Quarter].Members, [Active
Date].[Fiscal].CurrentMember.Properties("Current Fiscal Quarter") =
"1").Item(0)'
...

So I am trying to make CurrentQuarter the child of CurrentYear.  When I run
this MDX, I get the following error:

"object not found (possibly a dimension member) - cannot find parent
dimension member ("[Active Date].Fiscal.[All Active
Date].[CurrentYear].[CurrentQuarter]" - during the creation of a formula
Formula member name [Active Date].Fiscal.[All Active
Date].[CurrentYear].[CurrentQuarter] is not valid."

I also get the exact same error if I create these members as permanent
calculated members in the cube using Analysis Manager.  What's really
strange is that the Parent member/Choose dialog in Analysis Manager allows
me to choose the CurrentYear member as the parent, yet the CurrentQuarter
member won't validate once I do that.

Is this simply not possible with Analysis Services to have calculated member
children of calculated members, or is there a way to get it to work?

Thanks,
Rick


5.Calculated Member - Current Member - Previous Member Madness

I have a tricky one here.

Basically I have the following, 2 dimensional output

               COL 1    COL 2
ROW 1       4             8
ROW 2       2             4
ROW 3       2             2

I am trying to find the syntax, or some workaround, for calculating COL 2, 
which references COL 1(n) and itself, COL 2(n-1) to calculate:
COL 2(n) = COL 1(n) + COL 2(n-1)

I am trying to reference the PrevMember of COL 2 while generating COL 2.

I've tried generating a calculated member in MDX as such:
member [Measures].[COL2] as '[Measures].[COL1] + 
[Measures].[COL2].CurrentMember.PrevMember', format = '#.##'

It just won't allow me to reference something I'm generating like this 
on-the-fly.  Makes sense.  Is there any way to accomplish this with 
calculated members, cells, anything?

I am using linear interpolation to estimate Percentile Ranks for Student 
scores.  The algorithm 'STARTS' here.  Phew this is fun.

Any help would be greatly appreciated.

tx,
Tim


6. Creating calculated members to correctly total percentage calculations

7. creating calculated members on local cubes

8. Can't Create Calculated member with openquery in T-SQL



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 21 guest