Matrix中的自定义计算 - Reporting Services 2005

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.

我正在编写一份报告,显示每个部门使用的燃气使用量(加仑)。请求是查看每个月和每个部门使用的加仑。根据他们在过去时间框架中使用的加仑气体,需要一列来显示每个部门的目标目标。每个部门的目标目标是比所述时间范围内使用的总加仑数少x%。

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.

我目前在Reporting Services中有一个矩阵,其中部门组成行,数月组成列,加仑填充详细信息。矩阵由dataset1填充。我按照每个部门的要求为每个月提供数据分组。我的问题是计算目标。我的想法是创建第二个数据集(dataset2),它根据请求的时间范围返回使用的加仑数。我按部门对这些数据进行了分组我希望我可以在每个数据集中使用department字段来确保使用适当的数字。

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.

我添加了一个新列,显示在加仑字段旁边。当我试图构建表达式时,我发现如果我对加仑字段求和,我只能抓住数据集2中使用的加仑数。这给了我每个部门使用的总加仑数。

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

部门|加仑/月|目标目标

1 个解决方案

#1

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.

在“月份”列中添加小计,然后使用一些技巧来获取数据的加仑/月或目标目标。您可以使用InScope()函数执行此操作 - 这可以定义特定的“详细信息”单元格是否属于加仑/月份列或目标目标(小计列)。为相应的范围定义字段(Fields!GallonsPerMonth.value)或计算(Fields!GallonsPerMonth.Value * x%)。

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

有关InScope()函数的更多信息,请尝试此链接如果您需要有关详细信息单元格表达式的任何帮助,请与我们联系。

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

PS - 你在解释你需要的东西方面做得很好。

#1