Search code examples
reporting-servicescrmssrs-2012dynamics-crm-2016

SSRS : How to get multi values from two tables in same column

I got 3 datasets in my SSRS project they're :-

  1. Visit
  2. ExtraDeductionDS
  3. CheckListDS

I have table in DB (Database) called "Visit" it's 1:many relation with "ExtraDeduction" & "CheckLists" tables.

First dataset bring the "VisitID" and "Name" with all related record in Extradeduction table.

enter image description here

And the second dataset bring the "VisitID" and "Name" with all related record in Checklist table.

enter image description here

Note : each of these two tables "Extradeduction, Checklist" has a column called "Name".

My question is : how to retrieve "Name" column in "Extradeduction & Checklist" tables that related in one visit and put the value in one column in my report, that's mean in the final result I need it to show like that :-

enter image description here

(102, 107) are visit ID column (int data type), and "Extra{Num} & Checklist{num}" they are the "Name" column (nvarchar Data type) .

Be informed that i'm using "Microsoft Dynamics CRM Fetch" connection type in my datasource, because my MS CRM is online so I don't have direct access to the database.

Any help in this regard will be highly appreciated.

Thanks ..

Solution

  • You have the lookupset function too ( Description on MSDN)

    And the multi lookup ( Description on MSDN)

    The lookupset function seems to be the one you need.