access another dataset inside an expression

Question

For each row in my report, I have one field where I need to check another dataset to determine the data that goes in there.

This is the query for that dataset:

        select t1.this, t2.that
        from thisTable t1
        left join thatTable t2 ON t1.id = t2.id
        where t1.someId = @param1
        and t2.someId = @param2

Is there a way to pass in parameters to another dataset via an expression?

Thanks!

score 1 · Answer 1 · answered Apr 18 '16 at 19:29

You can try something like this:

Create a Dataset and make a lookup key:

SELECT t1.this, t2.that, 
    CAST(t1.someId AS VARCHAR(5)) + CAST(t2.someId AS VARCHAR(5)) as LkpKey
FROM thisTable t1
LEFT JOIN thatTable t2 on t1.id = t2.id

Now in the Field Expression you can use a LookUp to get the desired value. Where #YourIDField1 and #YourIDField2 are the Values from the Original Dataset you wish to use as the LookUp, and #FieldYouWant is the value you want returned from the LookUp Dataset.

=LookUp(Fields!#YourIDField1.Value & Fields!#YourIDField2.Value,
    Fields!LkpKey.Value,
    Fields!#FieldYouWant.Value,
    "#YourDataSet")

You can wrap the LookUp in an IIF if a Default Value is desired like this:

=IIF(LookUp(Fields!#YourIDField1.Value & Fields!#YourIDField2.Value,
        Fields!LkpKey.Value,Fields!#FieldYouWant.Value, "#YourDataSet") <> nothing,
    LookUp(Fields!#YourIDField1.Value & Fields!#YourIDField2.Value,
        Fields!LkpKey.Value,Fields!#FieldYouWant.Value, "#YourDataSet"), 
    "Default")

access another dataset inside an expression

1 Answers1