Hi I want to do something as simple as Adding the previous 12 months sales to a row. My tablix report is suppose to look something like this: Year Filter: 2010 Months: Months Sales Sales Past 12 months Jan 12345 67890 Feb 23456 78901 Mar 34567 89012 And so on So ... in the third column, Sales Past 12 months, say for Mar 2010, the Sum should be something like: Sales Mar 2010 + Sales Feb 2010 + Sales Jan 2010 + ... + Sales Apr 2009 = 89012 My dataset is taken from a Fact table and every row is a month. It's so simple but I'm stuck. Please help? jea

You could do the calculation using functions in SSRS. The better option would be to rewrite your SQL query so that it returns the values (you'll need a subquery and a join to your date dimension using datediff or dateadd functions). This may not perform very well. The best option is to source this from an SSAS cube and use the LastPeriods() funciton.my blog http://karlberan.wordpress.com/

I'm not that good with SSAS .. yet ... jsut getting started. What funcitons exactly in SSRS could I use? Is there any site where I can find a whole bunch of examples for them? What we did was to expand our statistictable in our DW ... simple as taht, some calculations per row, last year results, sums YTD ... last years YTD this month ... and so on ... Thanksjea

Hi, As Karl posted, the better approach is to calculate the sum in SQL query or use Analysis Services. However, calculating the sum of last 12 months for each row is not directly in Reporting Services. As a workaround, we can use Custom Code to achieve this. For example, please first click the Report in the main menu and select Report Properties… Then, in the Report Properties dialog, select Code in the left box and input the code below public dim list As System.Collections.Generic.List(Of Double) = New System.Collections.Generic.List(Of Double) public function Add(value as double) as double If list.Count = 12 Then list.RemoveAt(0) End If list.Add(value) return value end function public function Get() as double Dim sum As Double For Each i In list sum = sum + i Next ruturn sum end function In the code, we define a global List collection to store last 12 values. The Add function to add a value to the list and keep only last 12 values in it. The Get function to calculate and return the sum of the 12 values in the list. After that, we need to call these functions in the custom code. In the detail cell of Sales Amount Column, we can input the expression like =Code.Add(Fields!SalesAmount) to pass the Sales Amount of the current month to the custom code. And in the cell of Sales Past 12 months we can input the expression =Code.Get() to get the sum of last 12 months. For more information about Custom Code in Reporting Services, please refer to the link below: http://msdn.microsoft.com/en-us/library/ms159238.aspx If anything is unclear, please let me know. Thanks, Tony ChainPlease remember to mark the replies as answers if they help and unmark them if they provide no help

Very nice ... thanks a lot. But I did an Aggregate table instead. What I didn't mentioned .. .sorry didn't think it was needded, my bad ... was that we usea Raw table and export necessary data to a star schema and and aggregate table. I select everything that I need from this star schema and I get everything fast and simple ... simpler maybe. Thanks Tony for your suggestion ... will have it mind for comming reports!!jea

Very nice ... thanks a lot. But I did an Aggregate table instead. What I didn't mentioned .. .sorry didn't think it was needded, my bad ... was that we usea Raw table and export necessary data to a star schema and and aggregate table. I select everything that I need from this star schema and I get everything fast and simple ... simpler maybe. Thanks Tony for your suggestion ... will have it mind for comming reports!!jea