0

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

Majed Al-Omair
  • 61
  • 1
  • 12
  • http://stackoverflow.com/questions/19793738/joining-two-datasets-to-create-a-single-tablix-in-report-builder-3 – BIDeveloper Aug 03 '16 at 08:57
  • Mr. BIDeveloper, that's not what i'm looking for, my case is : I have Visit table related 1:many with Extradeduction & Checklist tables, I need view all data in Extradeduction & Checklist tables that related with the visit, like what I explained in the post. The URL talk about LOOKUP function it's 1:1 relationship with two datasets, but I have 2 datasets 1:many relationship . Thanks ... – Majed Al-Omair Aug 03 '16 at 10:25
  • The link shows you how to join two datasets, that is all you need to do. – BIDeveloper Aug 03 '16 at 10:27
  • Mr. BIDeveloper, I have column called "Name" in these two tables "Extradeduction & Checklist". I need the result shows in one column, how to do that with the LOOKUP function.? I mean all the "Name" data in Extradeduction & Checklist tables shows under one column in the report . – Majed Al-Omair Aug 03 '16 at 10:29
  • How are you populating your datasets? Are you using SQL queries? – iamdave Aug 03 '16 at 11:39
  • Mr. iamdave, No i'm using FetchXML, it's little bit harder than SQL and got some limitation, For example I can't use (union). https://s31.postimg.org/bgfdssw9n/XML.jpg (Image) . – Majed Al-Omair Aug 03 '16 at 11:47
  • Any help please .? – Majed Al-Omair Aug 03 '16 at 19:20
  • I did it by adding new row and use LOOKUP function to make the data related with other dataset, but it just retrieves one record (First Record) in the second dataset.!! I think the problem in the grouping, how can I do it.? , any idea/help.? – Majed Al-Omair Aug 04 '16 at 12:46

2 Answers2

1

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.

NdsAerith
  • 86
  • 3
  • Mr. Anne BRUNAS CASSININ, thanks for your answer, I tried to use Lookupset inside join function and it worked, but all related data became in one row, but what I need is put each related data in different rows. How to do that.? – Majed Al-Omair Aug 06 '16 at 12:15
  • I found this solution =join(lookupset(Fields!VisitID.Value, Fields!ID.Value, Fields!ReferenceNo.Value, "VisitExtraDeduction"), VbCrlf). "VbCrlf" function to organize the data as multi rows, but actually it's still putting the related data in one row. Any other idea.? – Majed Al-Omair Aug 06 '16 at 13:17
  • Any one can help.? – Majed Al-Omair Aug 08 '16 at 04:44
  • I still can't retrieve the record in multi rows. Any help please.? – Majed Al-Omair Aug 10 '16 at 05:35
  • Sorry, I don't know how todo it in an other way than yours. In other cases when I need lines in a cell i take a tablix in the cell, but I don't think it will work with a lookup. – NdsAerith Aug 10 '16 at 08:22
  • Mr. Anne BRUNAS CASSININ, thanks a lot for your answer. I will try to find another way to do it. Appreciate it. – Majed Al-Omair Aug 10 '16 at 09:15
0

try using Lookup instead of Lookupset without using join, like this:

=lookup(Fields!VisitID.Value, Fields!ID.Value, Fields!ReferenceNo.Value, "VisitExtraDeduction")

If that not work, you can create a subreport and insert it on the column of main report where you want to display data. You have to pass respective ID from main report to subreport to fetch the desired data.

Dev
  • 78
  • 1
  • 13