Need to pass parameter today to calculate year to date metric
I need a bar chat and a tablix to display year to date culmulative using a parameter Pmonth and Pdate to limit to Jan 2011 to today for everyday. I used period to date function with level month and member date in SSMS. But i need help to edit
this query for SSRS:
WITH
MEMBER [Measures].[year to Date Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Month],
[Date].[Calendar].CurrentMember
),
([Measures].[Sales Amount])
)
SELECT
{
([Measures].[Sales Amount]),
([Measures].[Month to Date Sales])
} ON COLUMNS,
{[Date].[Calendar].[Date].Members} ON ROWS
FROM [Sales Cube]
This query is currently giving me the culmulative for date in all available year but I need parameter that will limit to for example Jan 2002 till 12/31/2002. For example pmonth = jan 2011 and pdate = today e.g 02/27/2011. I need to run this query in SSRS.
Thanks BI Developer
February 27th, 2011 5:29am