矩阵中的自定义计算 - Reporting Services 2005
我正在编写一份报告来显示每个部门使用的天然气使用量（以加仑为单位）。 请求是查看每个月以及每个部门使用的加仑数。 需要一栏来显示每个目标部门的目标是什么，基于他们在过去的时间范围内使用的汽油的加仑数。 每个部门的目标比上述时间范围内使用的总加仑数少 x%。
我目前在 Reporting Services 中有一个矩阵，其中部门组成行，月份组成列，加仑填充详细信息。 矩阵由 dataset1 填充。 我按照每个部门的要求对每个月的数据进行了分组。 我的问题是计算目标。 我的想法是创建第二个数据集（dataset2），它根据请求的时间范围返回使用的加仑数。 我按部门对这些数据进行了分组。 我希望我可以使用每个数据集中的部门字段来确保使用适当的数字。
我添加了一个新列，该列显示在加仑字段旁边。 当我尝试构建表达式时，我发现如果我对加仑字段求和，我只能获取 dataset2 中使用的加仑。 这给了我每个部门使用的总加仑数。
我试图找到包含我想要完成的任务的类似示例的资源，但我似乎找不到一个。 我试图尽可能详细地说明这一点，但又不会过于冗长。 如果需要的话，我非常乐意澄清或进一步详细解释我上面写的内容。 如果有人有链接、评论或建议，我们将不胜感激。
下面是一个非常简单的视觉效果或我希望完成的任务。 月份和部门将根据返回的数据进行扩展。
months
------------------------------
部门| 加仑/月 | 目标目标
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在“月份”列中添加小计，然后使用一些技巧来获取数据的加仑/月或目标。 您可以使用 InScope() 函数来执行此操作 - 该函数定义特定的“详细信息”单元格是否属于加仑/月列或目标目标（小计列）。 为适当的范围定义字段 (Fields!GallonsPerMonth.value) 或计算 (Fields!GallonsPerMonth.Value * x%)。
有关 InScope() 函数的更多信息，请尝试 此链接
如果您需要有关详细单元格表达式的任何帮助，请告诉我。
PS - 你已经很好地解释了你的需求。
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.