MDX: Rolling YTD

Hi,
I have been searching for a solution in MDX to create rolling
Year-to-Date calculated members.
For example:
When the current member time is June 2006, I want the total sales YTD
of last year, that
means from January 2005 till June 2005.
Thanks for any hints for a solution
RS.
ps.
My starting point was:
sum({ytd()},[Measures].[Sales])
But this formula is the sum YTD for this year. I'm searching for a
formula for the sum YTD of last year.
I've tried every combination with sum, OpeningPeriod(), lag,
ParallelPeriod().RS wrote:
> Hi,
> I have been searching for a solution in MDX to create rolling
> Year-to-Date calculated members.
>
> For example:
> When the current member time is June 2006, I want the total sales YTD
> of last year, that
> means from January 2005 till June 2005.
>
> Thanks for any hints for a solution
>
> RS.
>
> ps.
> My starting point was:
>
> sum({ytd()},[Measures].[Sales])
>
> But this formula is the sum YTD for this year. I'm searching for a
> formula for the sum YTD of last year.
>
> I've tried every combination with sum, OpeningPeriod(), lag,
> ParallelPeriod().

> I datagrep is offer the solution for most of the problems
> so visit this for ur solutions
>http://www.datagrep.com

MDX Query Tool

I have been searching and looking for days for a tool that will allow you to build mdx queries in a GUID (Alot like the browser) and then view the actualy mdx query. I tried to profile the AS 2005 database while using the browser but it profiles some strange stuff..

SELECT
NON EMPTY [{51E097E0-68D7-48E7-9847-B7D931EA6E0B}Pivot70Axis0Set1]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY [{51E097E0-68D7-48E7-9847-B7D931EA6E0B}Pivot70Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,
{
[Measures].[Account Count],
[Measures].[Estimated Average]
}
ON PAGES
FROM [Mimi]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

What tools are you guys using? I really dont feel like learning MDX if a good tool is out there.

Thanks in advance,

MardoYou might want to create a "Report Server Project" in Visual Studio (from Business Intelligence Project group). There in Data page you can build the query for the report by means of the user interface. You can view the created MDX if you press some button on the toolbar (something like MDX view). But the query built there follows certain pattern only.
|||Andrew,

I tried that. The big problem with the MDX query builder in Microsoft Sql Server Report services is its entire goal is to flatten the mdx queries, no cross joining, etc. This is a big problem not only for report services, but also the query builder itself. I wish the report services would handle true olap data, for more reasons then just this.

Marty|||I'd give the excel add in for analysis services a go. It allows you to view the pivot table query MDX. The other alternative is to just learn it! (It isn't so bad).|||

Phil,

This is what I have done. It actually is pretty easy once you get into the syntax.

Thanks for your help.

Mardo