Hi everybody

Im working with reporting services and have the following problem

I need to create a year to date function that returns a set for this year 2 date, and last year 2 date.

The date dimension is built up as All-Year-Quarter-Month.

The input is a year and a month.

I need to calculate a measure, lets call it amount for the designated period for the chosen year and the year before.

I guess this is a classic example and I have solved it, see the text at the bottom of the posting, but there has to be a more elegant way... please help, since I love the elegant coding..... thx






I have a problem and would like to know the most elegant solution to the problem.

= WITH SET [LY] AS '{[Date].[Total Period].[2001].[Q1].[1]:[Date].[Total Period].[2001].[Q" & FLOOR((Parameters!Month.Value - 1) / 3) + 1 & "].[" & Parameters!Month.Value & "]}'
"SET [YTD] AS '{[Date].[Total Period].[2002].[Q1].[1]:[Date].[Total Period].[" & Parameters!Year.Value & "].[Q" & FLOOR((Parameters!Month.Value - 1) / 3) + 1 & "].[" & Parameters!Month.Value & "]}' " &
"MEMBER Measures.[EUR LY] AS 'SUM([LY], [Measures].[Price EUR])' " &
"MEMBER Measures.[EUR YTD] AS 'SUM([YTD], [Measures].[Price EUR])' " &
"SELECT { [Measures].[EUR LY], [Measures].[EUR YTD] } ON COLUMNS, " &
"{ Descendants( [Products], [Products].[Item No], LEAVES) } ON ROWS " &
"FROM Extranet"