Past 12months Data in SSRS
Hi, I have to get data for the last 12 months based on the selected month and year by the user in my SSRS 2008R2 Report. e.g. User selet Jan 2010 I need to display data for Feb 2009 to Jan 2010. I have written a function in the Report code which works for it. But whn i bind that code in my dataset query it throws error. Can anyone help me on this Please.Thanks & Regards, Chaitali Thaker
May 17th, 2011 1:35am

I guess you have used something like; - DateAdd with a negative interval; - http://msdn.microsoft.com/en-us/library/ms186819(v=SQL.100).aspx Please post details of the error.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 2:59am

Hi Kieran , I have created month parameter which consists of Month name from Jan to Dec. I have Year parameter which gets its value from the cube. I need to take this two values (Jan 2010) and return data for past 12 months including selected month year. Below is the code in report. Public Function GetMonth(ByVal param As String,ByVal param1 As String) As String '="[Date].[Month].&[2011]&[3]" Dim year As Integer = param.Substring(param1.IndexOf("&") + 2, 4) Dim month As Integer = param.Substring(param.LastIndexOf("[") + 1, 2).Replace("]", "") Dim offSet As Integer = year - 1990 Dim offsetRet As Integer = 12 * offSet Dim OffsetreFinal As Integer = month + offsetRet - 12 Return Convert.ToString(OffsetreFinal) End Function in the dataset query i made it an expression with the below changes:- ="select [Measures].[Budgeted Revenue] on columns , ( Subset ([Date].[Month].members ,"+Code.GetMonth(Parameters!Month.Value)+",12) ,[OperationType].[Operation Type Desc] ON rows FROM [Cube]" On doing the above changes in dataset it says :- "Cannot set the command text for dataset." I am very much confused in how to combine Month and Year and get this 12 months output.Thanks & Regards, Chaitali Thaker
May 17th, 2011 3:20am

Any Idea?? I need to concatenate 2 params and return output for last 12 months based on the params selected.Thanks & Regards, Chaitali Thaker
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 4:54am

Hi, I didn't realise that you are using OLAP / MDX as your data source / expression language. I am also learning MDX like you. I will try and chip away at this. If anyone else can chip in helping MSDN_new please do. You may also try and ask the MDX part of your question separately in the forum; - http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads There are excellent MVP experts in MDX who regularly answer questions in this forum. Kind Regards, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood
May 17th, 2011 5:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics