January 16, 2016 at 11:19 am
Hello,
I can do a lookup in SSRS 2008R2 for a single field to lookup on, but I have two fields to lookup on and I trying to figure it at. Can anyone assist me. For example
Dataset1 I have two fields: "Company", "Storage Unit" I need to pick up "Amount" from Dataset2 that has "Company", "Storage Unit", "Amount" any tips I will appreciate. Thank you in advance.
January 17, 2016 at 12:26 am
Are both datasets coming from the same data source? If so, is joining them at the source an option?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 17, 2016 at 11:36 am
Hi Orlando,
Yes, the datasource is the same, not sure about join (joining dataset?), can you please tell me what you mean? I have never join a dataset before. Thank you.
January 17, 2016 at 5:14 pm
What I meant was, why not join the two tables in the query you issue from one of your SSRS Datasets? For example, let's say you had two tables, one for people's names:
USE tempdb;
GO
CREATE TABLE dbo.person
(
person_id INT NOT NULL
IDENTITY(1, 1) PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100)
);
and another with people's addresses:
USE tempdb;
GO
CREATE TABLE dbo.mailing_address
(
mailing_address_id INT NOT NULL
IDENTITY(1, 1),
person_id INT NOT NULL
REFERENCES dbo.person (person_id),
address_line_1 VARCHAR(100),
address_line_2 VARCHAR(100),
city VARCHAR(100),
state_code CHAR(2),
zip_code VARCHAR(9),
);
and they were related by the key on the person table, person_id.
Joining them in the report layer using an SSRS Lookup function can be done to get the correct result however it will not be as efficient as joining the two datasets within the database layer before the data even reaches the report layer:
SELECT p.person_id,
p.first_name,
p.last_name,
a.mailing_address_id,
a.address_line_1,
a.address_line_2,
a.city,
a.state_code,
a.zip_code
FROM dbo.person p
JOIN dbo.mailing_address a ON a.person_id = p.person_id;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 17, 2016 at 9:51 pm
Orlando,
I will give that a try. Thanks for your help. I really appreciate the tip.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply