I have 2 datasets
First one looks up for contactID, ContactMethod and Name (dataset1)
ContactId | ContactMethod | Name
1048 | email@email.ca | John Doe
Second looks up for contactID, deliveries, and deliveryStatus
ContactID | deliveries | deliveryStatus
1048 | FailedAttempt | Undeliverable
1048 | FailedAttempt | Undeliverable
1048 | FailedAttempt | Undeliverable
and repeat that 6 more times...
I know I could do one single query but those two datasets reside in two different database so I can't make one query with 2 datasource ( as far as I know. I could be totally wrong)
So here's my function
=Join(LookUpSet(Fields!ContactMethod.Value,
Fields!ContactMethod.Value,
Fields!CoarseReportingBucketName.Value,
"EX_JobDeliveries"),
",")
This will return me, in one cell:( if you see the picture in the link, undeliverable is in one cell 9 times)
How would I go about in displaying all these results in different cells, unless there's another way. Using Lookup only returns row, I was hoping it would automatically fill itself, but it doesn't.
So the result would look something like this ( so a stepped report)
ContactMethod | Name | deliveries | deliveryStatus
email@email.ca|
John Doe |
FailedAttempt | Undeliverable
FailedAttempt | Undeliverable
FailedAttempt | Undeliverable
and 6 more times of that...
You can use Environment.NewLine
in the join function:
=Join(LookUpSet(Fields!ContactMethod.Value,
Fields!ContactMethod.Value,
Fields!CoarseReportingBucketName.Value,
"EX_JobDeliveries"),
Environment.NewLine)
If you need to show each value in a row of your tablix/matrix you will have to modify your dataset query.
Also note you can perform a query across different databases (even in different servers).
SELECT firstdb.*, seconddb.*
FROM Server1.Database1.dbo.myTable AS firstdb
INNER JOIN Server2.Database2.dbo.myTable AS seconddb
ON firstdb.id = seconddb.id
Taken from here.
Despite it is possible you face issues with the collation between both servers it is the only way to get your expected result. There is no way to generate rows from SSRS expressions.