SSRS - Setting value of text box from another dataset?

Question

I have a report with a list attached to a dataset with the following columns, and the list is grouped on id:

id
sale_date

I have another dataset2 that contains the following:

id
customer_name
customer_phone

Unfortunately, I cannot join the two tables as they are in different databases, so my question is:

How do I show the customer_name in a textbox within the list so that when the reader forwards through the ids, the correct customer_name displays in the textbox?

I tried putting this expression in the textbox, but it doesn't return anything when I forward through the records: =lookup(fields!id.value,fields!id.value,fields!customer_name.value,"dataset2")

Thank you for your help.

Update: In the interest of moving on, I found an alternative to my problem by using a switch statement in the textbox's expression to populate its value based upon the id. I know it's a hack, but since there are only 10 customer_name's, it's not too bad. I would like to resolve with using the dataset2 data in future.

score 0 · Answer 1 · edited May 23 '17 at 12:08

0

You do not say which database system you are using. I'm assuming SQL Server.

There is nothing to stop you - from a SQL Server point of view - in connecting data from one database to another.

You can even connect to two databases on two different servers.

SSRS will almost always perform better (and be easier to maintain for you) if you sort your datasets out before they get into the report.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 17 '16 at 08:47

BIDeveloper

2,628
4
36
51

I am using sql server, but unfortunately I do not have access to the underlying db to create a view, so I have to bring in the data from the two tables as separate datasets. – MaxAx Jun 17 '16 at 12:59
How are the datasets populated? – BIDeveloper Jun 17 '16 at 13:06
I have two datasources, which I use to create the two datasets directly from the db tables. – MaxAx Jun 17 '16 at 14:52
Yes - but how are the populated? Select * from table? EXEC sp_StoredProc? – BIDeveloper Jun 20 '16 at 07:04

SSRS - Setting value of text box from another dataset?

1 Answers1