CODEWITHSUNDEEP
reporting-servicesreportbuilderreportbuilder3.0
user3219693
user3219693

Reputation: 201

LookUpSet - Group By

I have 2 databases:

Database1 on Server1 Database2 on Server2

I create a table that gives me a small example result below:

Name       |  Date        | Total
Name1         10/04/2014    125,101
Name1         11/04/2014    125,101
Name2         10/04/2014    2,5
Name2         11/04/2014    2,5

So from Database1 on Server1 I get the Date and Name values.

I get the total column by using the following expression on the 'Total' textbox field that looksup the total from Database2 on Server2:

=Join(LookupSet(Fields!Col1.Value, Fields!Col1a.Value, Fields!TotalCol.Value, "DataSetServer2"),",")

Obviously what I want to do is get the following results:

Name       |  Date        | Total
Name1         10/04/2014    125
Name1         11/04/2014    101
Name2         10/04/2014    2
Name2         11/04/2014    5

Is this possible? If any further information is needed then let me know, apologies I have never really used LookUpSet.

Additional Info:

DataSet2 is used to query Database1 on Server1 to create the table and example data is:

Date        | Col1    | Name
10/04/2014    UID01     Name1
11/04/2014    UID01     Name1
10/04/2014    UID02     Name2
11/04/2014    UID02     Name2

The following dataset is then used to query Database2 on Server2

Date        | TotalCol   | Col1a
10/04/2014    125          UID01
11/04/2014    101          UID01
10/04/2014    2            UID02
11/04/2014    5            UID02

Upvotes: 2

Views: 2577

Answers (1)

Ian Preston
Ian Preston

Reputation: 39596

With your sample datasets:

enter image description here

enter image description here

I have created a simple table based on the first dataset:

enter image description here

The expression is:

=Lookup(Fields!Col1.Value & Fields!Date.Value
    , Fields!Col1a.Value & Fields!Date.Value
    , Fields!TotalCol.Value
    , "DataSetServer2")

You'll note that I'm comparing Fields!Col1.Value & Fields!Date.Value to Fields!Col1a.Value & Fields!Date.Value in the first two parameters here. You can pass any expression, it doesn't have to be one column. The expression I've used give the unique lookup comparisons you require.

Since we're getting the unique rows, LookupSet can be replaced with Lookup.

This gives the required results:

enter image description here

You just need to find a way to make the lookup unique, i.e. some combination of Col and Date. You could even use a calculated field in the dataset.

Upvotes: 2

Related Questions