Re: Ancestor question

#1

I don't fully understand what you want finally, but it's because the final
result is from the subcube created by subquery.

How about modifying the MDX query manually as following?

SELECT ( { [Date].[Year - Month].[Month].&[2006]&[7] } ) ON COLUMNS FROM
[Financieel]
==>
SELECT ( { Ancestors([Date].[Year - Month].[Month].&[2006]&[7],
[Date].[Year - Month].[Year]) } ) ON COLUMNS FROM [Financieel]

Ohjoo

"Qbee" <Qbee (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi All,

I want in my report a budget total on year level. I create the following
MDX:

with member [measures].[budget year] as '([Measures].[Budget Amount],
Ancestor([Date].[Year - Month].currentmember, [Date].[Year -
Month].[Year]))'

select { [Measures].[Budget Amount], [measures].[budget year] } on columns
from [Financieel]
where ( [Account].[Account Hierarchy - Name].[Account
Subgroup].&[Personele
Kosten]&[Vaste Personeelskosten].&[Vaste
Personeelskosten]&[Salarissen],[Cost
Centre].[Cost Centre Hierarchy - Name].[Team].&[Algemene
Zaken]&[OPA].&[OPA],
[Date].[Year - Month].[Month].&[2006]&[7] )

I add the [budget year] as a calculation in my cube and use it in a
reporting services report. However, the MDX genereted by the report is as
follows:

SELECT NON EMPTY { [Measures].[Cumulatieve Kosten], [Measures].[Cumulative
Budget], [Measures].[Financial Transaction Amount], [Measures].[Kosten %
Budget], [Measures].[Kosten % Jaarbudget], [Measures].[Cum kosten %
Jaarbudget], [Measures].[Cum Kosten % Cum Budget], [Measures].[budget
year],
[Measures].[Budget Amount] } ON COLUMNS,
NON EMPTY { ( [Cost Centre].[Unit].[Unit].ALLMEMBERS *
[Cost Centre].[Dim Cost Centre Description].[Dim Cost Centre
Description].ALLMEMBERS *
[Cost Centre].[Dim Cost Centre].[Dim Cost Centre].ALLMEMBERS *
[Account].[Account Hierarchy].[Account Number].ALLMEMBERS *
[Account].[Account Description].[Account Description].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Cost Centre].[Dim Cost Centre].&[Algemene Zaken]&[3050]
} ) ON COLUMNS
FROM ( SELECT ( { [Organisation].[Dim Organisation Description].&[Buma],
[Organisation].[Dim Organisation Description].&[Stemra] } ) ON COLUMNS
FROM ( SELECT ( { [Date].[Year - Month].[Month].&[2006]&[7] } ) ON
COLUMNS
FROM [Financieel])))
WHERE ( [Date].[Year - Month].[Month].&[2006]&[7], [Organisation].[Dim
Organisation Description].CurrentMember ) CELL PROPERTIES VALUE,
BACK_COLOR,
FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE,
FONT_FLAGS

The result is that the budget year calculcation only shows the amount on
month level instead of year level.

How can I solve this?

Q

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Ancestor question

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: Ancestor question - 11-30-2006 , 07:28 PM