Custom Calculations in a Matrix - Reporting Services 2005

Question

I am writing a report to show gas usage (in gallons) used by each department. The request is to view each month and the gallons used by each department. A column is required to display what each departments target goal is, based on the gallons of gas they have used in a past time frame. Each departments target goal is x percent less than the total gallons used for said time frame.

I currently have a matrix in Reporting Services with departments making up rows, months making up columns, and gallons filling the details. The matrix is being filled by dataset1. I have the data grouping as is requested for each month by each department. My problem is calculating the target goal. My thought was to create a second dataset (dataset2) that returns the gallons used based on the time frame requested. I grouped this data by department. I was hoping I could use the department field in each dataset to make sure the appropriate numbers were used.

I added a new column which shows up next to the gallons field. As I attempted to build the Expression I found out that I could only grab the gallons used from dataset2 if I was summing the gallons field. This gives me the total gallons used by every department combined.

I have tried to find resources with similar examples of what I am trying to accomplish but I cannot seem to come across one. I am trying to keep this as detailed as possible without making it too wordy. I would be more than happy to clarify or explain into further detail what I have written above if it is needed. If anyone has links, comments, or suggestions they would be greatly appreciated.

A very simple visual or what I am hoping to accomplish is below. The months and departments would expand based on the data returned.

                  months
       ------------------------------

departments| gallons/month | target goal

Answer 1

Hi there,

Add a subtotal to you 'Months' Column, and then use some trickery to get either the gallons/month or target goal for the data. You can do this by using the InScope() function - this defines whether or not a particular 'Detail' cell falls in the gallons/month column or the target goal (subtotal column). Define either the field (Fields!GallonsPerMonth.value) or the calculation (Fields!GallonsPerMonth.Value * x%) for the appropriate scope.

For more on the InScope() function try this link Let me know if you need any help with the expression for the Detail Cell.

PS - You've done a great job of explaining what you need.