LookupSet 基于两个字段的组合而不是一个 [英] LookupSet based on two fields combined instead of one

问题描述

我有两个数据集:Dataset1(主要)和 Dataset2.我的报表数据集是 Dataset1.

我想从 Dataset2 访问总保费金额并将其放入报告中.如果市场名称"的参数是单值参数，则报告工作正常.

当我将参数属性设置为允许多个值"时，我的报告显示生产"字段的总计错误.问题是因为lookupset 函数是基于Currency_Type 的.

我应该做的是搜索每个市场名称和每种货币类型，然后在第二个数据集中搜索适当的总溢价并返回值.

数据集 1:

市场名称货币类型应收账款

数据集 2:

公司名称2 货币类型1 总保费金额

结果应该是这样的:

市场名称 货币类型 应收总保费金额

如果要搜索一个值，我使用此代码:

=code.SumLookup(LookupSet(Fields!Currency_Type.Value, Fields!Currency_Type1.Value,Fields!Gross_Premium_Amount.Value, "DataSet2"))

如果我想要组合基于市场名称和货币类型的查找集，我该怎么做?

解决方案

你能在你的查询中加入数据集吗?如果可能的话，那会容易得多.否则，您需要有某种方法来分解您的多值参数，例如将其作为元素包含在您的第一个数据集中.(如果合适，甚至可能是交叉连接.)

但是直接回答你的问题

<块引用>

如果我想要组合基于市场名称和货币类型的查找集，我该怎么做?

[请注意，这与如何查找多值参数没有太大关系.]

我已经解决了这个问题，方法是从查询本身的两条信息中创建一个键，例如字符串连接，但您也可以在 LookupSet 函数中执行相同操作:

=LookupSet(Fields!Currency_Type.Value +"-" + Fields!MarketName.Value, Fields!Currency_Type1.Value + "-" + Fields!Market_Name.Value, Fields!Gross_Premium_Amount.Value, "DataSet2"))

这仅在 MarketName 是您的第一个数据集中的值时才有效.

I have two datasets: Dataset1 (primary) and Dataset2. My report dataset is Dataset1.

I want to access Gross premium Amount from Dataset2 and put it in the report. The report is working just fine if the parameter for "market name" is a single value parameter.

When I set the parameter properties to "Allow Multiple Values", my report is displaying wrong Totals for "Production" Field. The problem is because the lookupset function is based on Currency_Type.

What I should do is search for each Market Name and for each currency type, then search for the appropriate Gross Premium in second dataset and return the value.

Dataset1:

Market Name      Currency Type     Receivable

Dataset2:

Company Name2    Currency Type1    Gross Premium Amount

The results should be like this:

Market Name      Currency Type     Receivable             Gross Premium Amount

I used this code if to search for one value:

=code.SumLookup(LookupSet(Fields!Currency_Type.Value, Fields!Currency_Type1.Value,Fields!Gross_Premium_Amount.Value, "DataSet2"))

What should I do if I want the lookupset based on Market Name and Currency Type combined?

解决方案

Can you join the datasets in your query? That would be a lot easier, if it's possible. Otherwise you need to have some way to break apart your multi-value parameter, such as a including it as an element in your first dataset. (Maybe even a cross join if appropriate.)

But to directly answer your question

What should I do if I want the lookupset based on Market Name and Currency Type combined?

[Note that this doesn't have much to do with how to lookup a multi-value parameter.]

I have solved this problem by creating a key from the two pieces of information in my queries themselves, such as with string concatenation, but you can also do the same in your LookupSet function:

=LookupSet(Fields!Currency_Type.Value +"-" + Fields!MarketName.Value, Fields!Currency_Type1.Value + "-" + Fields!Market_Name.Value, Fields!Gross_Premium_Amount.Value, "DataSet2"))

This will only work if MarketName is a value in your first dataset.

这篇关于LookupSet 基于两个字段的组合而不是一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！