Hello - I am trying to do something very simply but am having a lot of trouble. I basically have a matrix report summing A/R data with month/year as columns and bucket (1-30 days, over 90 days, etc) as rows. I need to add 3 calculated values to the end of the report (i.e. 3 columns after the last column from the dataset). These calculated values are monthly delta, quarterly delta, and yearly delta. Of course monthly delta compares values between the highest and second highest date values, quarterly between the highest and 4th highest date values etc. The problem I am having is that calculated fields cannot contain aggregates, so I cannot tell SSRS what is the maximum date value (the starting point for determining other date values used in the quarterly and yearly calculations). I am not sure why aggregate functions are available when creating calculated fields if they cannot be used in the first place. In any event, given the limitations of SSRS 2008 how would you go about getting this done? Thank you!!Bonediggler

The reason that you can't use an aggregate function in a calculated field is that these values are derived row-by-row and the rows necessary to calculate an aggregate value are out of scope. You didn't mention what type of data source you're using. This is the type of analytical logic that can be performed very effectively using Analysis Services in a calculated member or MDX query functions. However, if the data isn't currently in an SSAS cube, the effort needed to make that transition could be pretty significant. An example of your query result data would be helpful to come up with the best solution. One possible method would be to write a custom function used to pass the value of each cell in the row neeeded to perform the calculation into code module variables. In the cell for the calculation result, call another custom code function that uses these variables to do the match and return the result. You also may be able to do this by using field or ReportItem expressions. Again, if I could see an example to understand your data, I'm sure we can come up with a good approach.Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

Hi Paul - Thank you for looking at this. I came up with a solution by manipulating the data in the dataset query, but nonetheless am interested as to how this would actually be done IN ssrs. Sample data is as follows: BUCKET DATE AMOUNT A.CURRENT 201006 18,523.36 A.CURRENT 201007 15,423.92 A.CURRENT 201008 14,438.45 A.CURRENT 201009 12,825.69 A.CURRENT 201010 11,986.97 A.CURRENT 201011 18,654.00 A.CURRENT 201012 10,627.55 A.CURRENT 201101 18,872.92Bonediggler

monthly delta compares values between the highest and second highest date values, quarterly between the highest and 4th highest date values Hi Bonediggler, I was confused with the paragraph above, if necessary could you please provide me the expected result based on the sample data in you second reply? From your description, I see you want to calculate data by quarter and year, but you cannot do it in calculated fields. Because aggregate function cannot be used in calculated fields by default. As a workaround, we can use group expression to group data by quarter and year, then we can use aggregate function to calculate data quarterly or yearly. The main steps are: 1. Right-click Details in Row Groups Pane, choose Add Group, and click Parent Group. 2. In the Tablix group pane, structure group expression as below to group data quarterly: =Int(DateDiff(“m”,<StartDate>,Fields!<DateFieldName>.Value)/3). 3. Right-click Group1 created in step 2, choose Add Group, and click Parent Group. 4. In the Tablix group pane, structure group expression as below to group data yearly: =DataPart(“yyyy”, Fields!<DateFieldName>.Value). By now, you can calculate data within each group, using scope argument in aggregate function. For more information about Group Expression, please refer: http://msdn.microsoft.com/en-us/library/bb630426(v=SQL.110).aspx. Thanks, Lola Wang Please remember to mark the replies as answers if they help.