I can't get the PeriodstoDate Range Syntax to work through Project Server OLAP when creating Views.

I should not have to be a MDX expert to get a simple report !

This is the command I am trying.

Round(Sum(PeriodsToDate(Year, [Time].[2006].[Q2].[May].[17]:[Time].[2006].[Q3].[July].[25]),[Measures].[Actual Work]),0)

Of course, I have tried every combination of brackets and parenthesis with and without the "Year", etc....except of course the correct one.

Any help would be greatly appreciated.

Well, it's not clear from the query why PeriodsToDate() is needed (if,
indeed, you're simply trying to sum the measure over the specified date
range). The optional 2nd argument for PeriodsToDate() should be a
member, not a range:

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

PeriodsToDate (MDX)

Returns a set of sibling members from the same level as a given member,
starting with the first sibling and ending with the given member, as
constrained by a specified level in the Time dimension.

Syntax

PeriodsToDate( [ Level_Expression [ ,Member_Expression ] ] )
...
So, something like this might suffice:

Round(Sum(
{[Time].[2006].[Q2].[May].[17]
:[Time].[2006].[Q3].[July].[25]},
[Measures].[Actual Work]), 0)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

It appears that when creating a view in Project Server ,that uses OLAP, that the charting engine always charts/graphs the curve out to the end of the Project no matter what. Although the suggestion you had does sum correctly the work in the date range the chart still charts out that total between the ranges on out to the end of the project.

This appears to be a Project Server View issue rather than a MDX issue.

Thanks,

Jason