RE: PeriodsToDate and (All) member on Time dimension

Reed Jacobson was so kind to email me a solution to this issue:

By using Exists I could filter out unwanted members.

member [Measures].[AccQg] as
sum
(
exists
(
[DimDate].[Year - Quarter - Month Of Year - Date
Key].[date].members.item(0):
[DimDate].[Year - Quarter - Month Of Year - Date Key].currentmember
,
DESCENDANTS( [DimDate].[Year - Quarter - Month Of Year - Date
Key].[Year].[2003], [DimDate].[Year - Quarter - Month Of Year - Date
Key].[date], SELF )
)
,measures.Qg
)
select {measures.Qg, [Measures].[AccQg]} on columns,
([DimDate].[Date Key].children) on rows
from [cms db]
where [DimDate].[Year].[2003]


"Kjell Arne" wrote:

Hi

I'm trying to accumulate a calculated measure from beginning of time scope
to CurrentMember.

This is my calculated measure, Qg:

([Measures].[DynamicPropertyValue],
[Event].[Event].CurrentMember,
[Dynamic Property].[Dynamic Property].[Qg])

I have to do this because the relational db i based on a "open schema" model
and Qg is otherwise just a dimension member.
For "open schema" see
http://blogs.msdn.com/eugenea/archive/2006/05/29/610232.aspx

Using PeriodsToDate to accumulated Qg I use this calculated measure:

sum
(
periodstodate
(
[DimDate].[Year - Quarter - Month Of Year - Date Key].[(All)],
[DimDate].[Year - Quarter - Month Of Year - Date
Key].currentmember
)
,measures.Qg
)

It's working somewhat, but I can't seem to figure out how to specify the
timerange for this last calc. measure. It takes all time into account when
accumulating. I want it to limit calculation to i.e.
[DimDate].[Year - Quarter - Month Of Year - Date Key].[Year].&[2003] :
[DimDate].[Year - Quarter - Month Of Year - Date Key].[Year].&[2004]
somehow.

This is my select as it is now:

select {[Measures].[Qg], [Measures].[AccQg]} on columns,
([DimDate].[Date Key].members) on rows
from (select [DimDate].[Year - Quarter - Month Of Year - Date
Key].[Year].&[2003] on 0 from [cms db])

I've tried to remove the subcube in the from clause and replaced it with
where statements, but I think this is the way it should be. I read this is
supposed to be the way after SQL 2005 SP2.

What am I missing?

Thanks.

regards
Kjell Arne
.

Relevant Pages

  • Re: Total from a subform
    ... Thank you that was the problem once I added the sum function to the formula ... Never base a calculation on a calculation, ... Create the calculated field in the form footer of the subform like ... Ensure to name the control a name you will remember such as ...
    (microsoft.public.access.forms)
  • Re: percentage calculation...trying again
    ... NEXT, SUM, you get 0.00, so two pennies of difference. ... To show that Excel does the same, copy the four number above, FORMAT the ... shows that what appears as 0 did sum as 2 pennies. ... with the calculation involving freight and sales tax. ...
    (microsoft.public.access.queries)
  • Re: Newbie vectorization dramas
    ... that the sum could be calculated as twice the overlapping area plus ... the smallest X because the smallest X is negative and rand() is ... calculation that intuitively should not be there. ...
    (comp.soft-sys.matlab)
  • Re: Incorrect SUM values in section footer
    ... you cannot use the builtin functions such as SUM on ... calculated Controls, that is, where the calculation is done in ... again, or use a domain aggregate function, such as DSUM in the Control ...
    (microsoft.public.access.reports)
  • Re: Cognos over AS cubes - does not display calculated members correctly
    ... not sum, as appropriate here) or you can create a calculation that PP ... The cubes include calculated measures such as ... > However Cognos SUMS the margin% measure from the lowest level, ...
    (microsoft.public.sqlserver.olap)
Loading