SSRS报告中的查找功能

问题描述：

我有一个数据集Dataset1，并且我正在基于分组显示数据.数据是这样的

I have one dataset Dataset1 and in that I am displaying data based on grouping. The data is like this

   CityColumn      CountColumn
   City1              5
   City2              3

以上数据的查询是这样的:

The query of above datase is like this :

select count(*) as "CountColumn" from City group by CityColumn

在上面的数据集中，我已经使用CityColumn上的分组进行计数.

Here in above dataset I have counted using grouping on CityColumn.

现在，我创建了另一个数据集Dataset2，其中的数据是这样的

Now I have created another Dataset Dataset2 and in that The data is like this

  CityColumn
   City1
   City2
   City3

现在在数据集2中，我添加了一个称为TotalCount的计算字段，并使用了Lookup Function函数，就像这样

Now in dataset2 I have add one calculated field called TotalCount and used the Lookup Function the function is like this

=Lookup(CityColumn, CityColumn, CountColumn, "Dataset1")

但是它给我一个类似

查找包括聚合，行号，运行值，上一个或查找函数.聚合，行号，运行值，上一个或查找函数不能在计算字段中使用.

Lookup includes aggregate, rownumber, runningvalue, previous or lookup function. Aggregate, rownumber, runningvalue, previous or lookup function cannot be used in calculated field.

答

查找函数的前两个值必须引用列中的标识值.在您的情况下，城市名称必须同时存在于两个数据集中.认为这是主键.第三个值是要从第二个数据集中显示的值.所以它应该看起来像这样:

The first two values of the lookup function must refer to an identifying value in a column. In your case the City names must be in both datasets. Think of that as a primary key. The third value is the one you want to display from the second dataset. So it should look more like this:

=Lookup(Fields!CityColumn.Value, Fields!CityColumn.Value, Fields!CountColumn.Value, "Dataset1")

确保Dataset1包含您要查找的名为CountColumn的列.请记住，这仅查找单个行，而不是聚合.如果要使用聚合，则可以在查找功能之上执行该操作.

Make sure that Dataset1 has the column named CountColumn that you are trying to lookup. Keep in mind that this only looks up individual rows, not aggregates. If you want to work with aggregates you can do that on top of the lookup function.

由于在计算字段中不允许使用查找功能，因此您需要在饼图中的Value表达式中使用它.它应该看起来像这样:

Since Lookup functions are not allowed in calculated fields, you'll need to use it in the Value expression in your pie chart. It should look like this:

请注意，查找功能必须像求和功能一样进行汇总，才能用作图表值.

Note that the lookup function has to be in an aggregate like a sum function for it to work as a chart value.

相关推荐