I am trying to show Budget, Cost, Forecast, etc spread across months like this: Figure Jan Feb March Total Budget 12.00 13.00 14.00 39.00 Cost 9.00 10.00 11.00 30.00 To do this, I am unioning 5 selects together, pulling each month's figures into a single field, using the related date in the date field to determine month /year, and then having a Type column that hard codes that select statements figure-type, so it would be "Cost" or "Budget", etc depending on which record type it is. Resulting in something like this: Amount Year Month Type 6.00 2011 January Cost 3.00 2011 January Cost 10.00 2011 February Cost 12.00 2011 January Budget 13.00 2010 February Budget I return the data set I want, no problem, but then in reporting services I use an expression like this to sum only what I want: =Sum(IIF(Fields!Type.Value = "Budget", Fields!Amount.Value,0.0)) and =Sum(IIF(Fields!Type.Value = "Cost", Fields!Amount.Value,0.0)) . The issue here is that it will only sum / spread one figure, and the others result in #ERROR, I can only get either one figure to sum. In my stored procedure that returns this data, I check for nulls using a case statement, and convert each figure to a decimal to make sure they all have the same data type. I don't need to go through all the steps in my query, etc, as I have simplified the example, but I just want to know, based on this approach, should reporting services be able to handle this? Yes right? I think an issue might be that I am reusing the Amount column across multiple rows, and it can only handle one. Are there any limitations in this approach? Is there a better way? Thanks!!

Can you simply return data in the desired format? To me it sounds simpler than applying SSRS expressions.For every expert, there is an equal and opposite expert. - Becker's Law My blog

Hi, If I understood you clearly then below is the steps to achieve it in SSRS. The dataset is in this format Type Year Month Amount Cost 2011 January 6.00 Budget 2011 January 3.00 Below are the steps in the design. 1. Drag and drop a Matrix control on to the body of the report. 2. In the Row groups group on Type column (Fields!Type.Value) 3. In the Column groups group on Year column (Fields!year.Value) 4. In the Column groups again group on Month column (Fields!Month.Value). Need to takecare that this should as the child group of Year group. 5. In the detail cells place Amount column (Sum(Fields!Amount.Value)) 6. If you want subtotal for the column/row groups then add it. Hope its clear & helpful....Pavan Kokkula Tata Consultancy Services.

Thank you, this has helped me figure out my problem. Though if Type has a parent group, and say budget is null for that parent, how would I force the Type group to always show a budget field, but leave its values blank? Must I return a zero / some value in the sql for reporting services to pick this up, or is there another way? Thanks again.

I return the data set I want, no problem, but then in reporting services I use an expression like this to sum only what I want: =Sum(IIF(Fields!Type.Value = "Budget", Fields!Amount.Value,0.0)) and =Sum(IIF(Fields!Type.Value = "Cost", Fields!Amount.Value,0.0)) . How about using this expression Sum(IIF(Fields!Type.Value = "Budget",CDBL( Fields!Amount.Value),0)) and =Sum(IIF(Fields!Type.Value = "Cost",CDBL( Fields!Amount.Value),0)) . HTH, Ram Ram