reporting-servicesreportbuilder3.0

How to make LookupSet display results in many rows


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...


Solution

  • 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.