I have two parameters in SSRS Report. One is Date and the other one is TimeFrame.Date : Jan 1st 2007 Till Dec 31st 2010 and TimeFrame parameter with Literal values MTD, YTDIn the report, If I select a particular date (For e.g.; Aug 23rd 2007) from the date parameter and the value of MTD from the time frame parameter, The measures Renewal Business PIF and Average Policy Limit needs to have the value for the period August 1st 2007 till Aug 23rd 2007. If I use YTD from the timeframe filter, then the measures Renewal Business PIF and Average Policy Limit needs to have the value for the period Jan 1st 2007 till Aug 23rd 2007.Below is the main dataset for the report. The expressions in RED are the parameter expressions that I have given for the MTD and YTD. ( Parameters!TransactionEffectiveDateCalendarYearMonthNameDateName.Value) is the date that I chose for the report from the data parameter. I HAVE TWO QUESTIONS. i) Are the parameter expression syntax appropriate for the MTD and YTD's?ii) How can I change my main dataset to filter by the Timeframe parameter? SELECT NON EMPTY { [Measures].[Renewal Business PIF], [Measures].[Average Policy Limit] } ON COLUMNS, NON EMPTY { ([Transaction Effective Date].[Calendar Year - Month Name - Date Name].[Date Name].ALLMEMBERS * [Product Line].[Product Description].[Product Description].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@TransactionEffectiveDateCalendarYearMonthNameDateName, CONSTRAINED) ) ON COLUMNS FROM [AnalyticsCube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS MTD Parameter expression: ="[Transaction Effective Date].[Calendar Year - Month Name - Date Name].CurrentMember.Parent.FirstChild" + ":" + "[Transaction Effective Date].[Calendar Year - Month Name - Date Name].Parameters!TransactionEffectiveDateCalendarYearMonthNameDateName.Value(0)" YTD Parameter expression: ="[Transaction Effective Date].[Calendar Year - Month Name - Date Name].CurrentMember.Parent.Parent.FirstChild.FirstChild" + ":" + "[Transaction Effective Date].[Calendar Year - Month Name - Date Name].Parameters!TransactionEffectiveDateCalendarYearMonthNameDateName.Value(0)"

Here is the main dataset where I hardcoded the values and tested. How can I pass the date range from the Parameter expression to this main dataset ?SELECT NON EMPTY { [Measures].[Renewal Business PIF], [Measures].[Average Policy Limit] } ON COLUMNS, NON EMPTY { ([Transaction Effective Date].[Calendar Year - Month Name - Date Name].[Date Name].ALLMEMBERS * [Product Line].[Product Description].[Product Description].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET('{[Product Line].[Product Description].&[Geovera Specialty Homeowners]&[Admitted],[Product Line].[Product Description].&[Geovera Earthquake]&[Admitted] }') ) ON COLUMNS FROM ( SELECT ( STRTOSET('{[Transaction Effective Date].[Calendar Year - Month Name - Date Name].[Date Name].&[Feb 21, 2008].Parent.Parent.FirstChild.FirstChild' + ':' + '[Transaction Effective Date].[Calendar Year - Month Name - Date Name].[Date Name].&[Feb 21, 2008]}') ) ON COLUMNS FROM [AnalyticsCube])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

you can use a hidden "from date" parameter that is based on the value of the timeframe parameter. The timeframe parameter should be a simple multivalue string that could be "MTD" or "YTD". parameter from date: (if the timeframe is mtd use the year and month of the to date parameter and make the day of the month be the first, else use the first day of the year in the to date parameter) =iif(Parameters!TimeFrame.Value = "MTD", dateserial(year(Parameters!ToDate.Value), month(Parameters!ToDate.Value), 1), dateserial(year(Parameters!ToDate.Value), 1, 1) then your main query will not use the timeframe parameter, instead it will use the from date parameter

Can you please tell me the steps to setup the hidden parameter ? I m using the above expression in the default values section of the parameter. When I set this FromDate Parameter it is giving the following error - An error occured during local report processing FromDateAlso How should I link the MTD and TODATE parameter ? The TODATE Parameter is populated from the date dataset with all the dates.

You can use the PeriodsToDate function to get you exactly what you are looking for. This is the expression that should hopefully give you what you are looking for with the MTD.PeriodsToDate([Transaction Effective Date].[Calendar Year - Month Name], strToMember("[Transaction Effective Date].[Calendar Year - Month Name - Date Name]." + @TransactionEffectiveDateCalendarYearMonthNameDateName) )What I did here was pass the Month in to the first part of the PeriodsToDate function. This is telling the function that you want to look at the beggining of the month you are passing to the paramter that you are passing in.Here is what your select statement should be: ( SELECT PeriodsToDate([Transaction Effective Date].[Calendar Year - Month Name], strToMember("[Transaction Effective Date].[Calendar Year - Month Name - Date Name]." + @TransactionEffectiveDateCalendarYearMonthNameDateName) ) ON COLUMNS FROM [AnalyticsCube])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSJosh George LiveLogic, LLC

You might have to add the aggregates function around the periodsToDate. It should be something like this:Aggregate(PeriodsToDate([Transaction Effective Date].[Calendar Year - Month Name], strToMember("[Transaction Effective Date].[Calendar Year - Month Name - Date Name]." + @TransactionEffectiveDateCalendarYearMonthNameDateName) ))Josh George LiveLogic, LLC

Hi George, For the above is just like this code which i have here is in SSRS i want to convert this into performancepoint server report i have a problem near periodstodate ,please can u give me some idea and step by tep procedure to get periodstodate in performancepoint server FROM ( SELECT ( STRTOSET(@ProductLineFormDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@RegionStateName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ProductLineProductDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PeriodToDate + '(' + @TransactionEffectiveDateCalendarYearMonthNameDateName + ')') ) ON COLUMNS FROM [AnalyticsCube] i have this code in SSRS which is working fine,But can u help in getting this in PerformancePOint Server ,need to calculate YTD,MTD in performancepoint server,,if i select a date like march 4,2011 then in period filter if i select MTD it should give me march1st to 4th data (aggregation i mean),,and if i select YTD then it should give me jan1st to march4th 2011 data(aggregated) and this is for calculating current year data and even i have another dataset in SSRS which is used to calculate prior year data same as above for MTD and YTD. and the query for that second dataset is FROM ( SELECT ( STRTOSET(@ProductLineFormDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@RegionStateName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ProductLineProductDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PeriodToDate + '(ParallelPeriod([Transaction Effective Date].[Calendar Year - Month Name - Date Name].[Calendar Year], 1,' + @TransactionEffectiveDateCalendarYearMonthNameDateName + '))') ) ON COLUMNS FROM [AnalyticsCube] so could u please tell me how to do it,i have a prob at periodstodate,im able to pass parameters for @productlineproductdesc as <<productlineproductdesc>> and same for others ,so could u please give me the way to do it,thanks goerge.