Hi Ravi, and how will i get all the possible childs into a calculated field of the dataset1? I think i need to do this in the query of dataset1, but how can i do this? can i use a "subquery" in the select? ... and .... what if the result of the subquery are more than one row? How will this work? ... Makes it sense, to combine in 1 query, where normally 2 queries are used? f.e. in query one i get 1 row with my customer in query two i get all childs of this customer (via a second sql-table (connection)) where i look for all rows with uid of the customer und if these rows have the "Child" .. i return this row. but in query one i only get 1 row back in query two i get maybe 3 rows?! Thx, Greets Pit

maybe its better to show the quers for the datasets dataset1: SELECT CRMAF_FilteredContact.salutation, CRMAF_FilteredContact.contactid, CRMAF_FilteredContact.fullname, CRMAF_FilteredContact.firstname, CRMAF_FilteredOpportunity.contactid, CRMAF_FilteredOpportunity.contactidname, CRMAF_FilteredOpportunity.customerid, CRMAF_FilteredOpportunity.customeridname, CRMAF_FilteredOpportunity.opportunityid, CRMAF_Filterednew_zr.new_zr_art, CRMAF_Filterednew_zr.new_zr_artname, CRMAF_Filterednew_zr.new_dokuid AS zr_dokuid, CRMAF_Filterednew_zr.new_dokuidname FROM Filterednew_zr AS CRMAF_Filterednew_zr INNER JOIN FilteredOpportunity AS CRMAF_FilteredOpportunity ON CRMAF_FilteredOpportunity.opportunityid = CRMAF_Filterednew_zr.new_dokuid INNER JOIN FilteredContact AS CRMAF_FilteredContact ON CRMAF_FilteredContact.contactid = CRMAF_FilteredOpportunity.customerid dataset2: SELECT FilteredConnection.record1id, FilteredConnection.record1idname, FilteredConnection.record2id, FilteredConnection.record2idname, FilteredConnection.record1roleid, FilteredConnection.record1roleidname, FilteredConnection.record2roleid, FilteredConnection.record2roleidname, FilteredContact.new_age AS age, FilteredContact.birthdate, FilteredContact.firstname, FilteredContact.lastname, FilteredContact.fullname, FilteredContact.address1_line1 AS street FROM FilteredConnection INNER JOIN FilteredContact ON FilteredContact.contactid = FilteredConnection.record2id WHERE (FilteredConnection.statuscode = 1) AND (FilteredConnection.record2roleid = 'B629CEDD-8957-E011-A37B-00155D01991E') OR (FilteredConnection.statuscode = 1) AND (FilteredConnection.record2roleid = 'C31891C2-8D57-E011-A37B-00155D01991E') ORDER BY age

Hi, i hope i am right in this forum. I create custom reports in vs2008 for ms dyn.crm2011 (BIDS?). I have 2 datasets. First is with fields from 3 db-tables (contact, opportunity, risks) Now i need to look for in the connection db, if the contact (customer) has children. I have a unique ID for child. Now i have to query with the id of the customer for the rows in db connection which has the uid child ... and from this rows i need the uid of these contacts (children). This is the way i use, to get the data from dataset2 to dataset1: iif(Not isnothing(Join(LookupSet(Fields!zzz.Value, Fields!yyy.Value, Fields!xxx.Value, "dataset2"))), Join(LookupSet(Fields!zzz.Value, Fields!yyy.Value, "- " & Fields!fullname.Value & ", " & Fields!street.Value, "dataset2"), chr(10)), "No result") I made this with a second dataset. But now i have this problem which i had very often. I need to make a lookupset from the table or matrix in reportbody (which is only dataset 1) to dataset 2. From there i need the names birthdays & adresses. And if that was not enough i nee to compare the adresses of the children with the adress of the customer (if they live in the same home). OK sorry for the long prehistory, but now my 100000 questions. 1) I cant use lookups in datasets to get the child-uids from ds2 to ds1. Why not & how can i archive, that i get all uids (n:n connection) in ds1. I want to have the uids in new calculated fields (via lookup to ds2) ... why is this not possible? 2) Then i thought i can use variables to firstly get all needed data and store them in variables. But this is not possible too. i think in bids-reports i can use variables only in group-variables or report-variables .. and it seems that this variables can only store light things, but how can i use this variables in my report body while execution. i cant load f.e. the uids of the children, because can use the lookups only in report body (while execution). That broken my next idea. This was: make a function (in custom code area) and call the function while execution via Code.getUID(UIDsource, UIDdestination, fieldtoget, dataset). 3) Is it an option to get the complete fields in one query / one dataset? I think this isnt possible, or how can i have 1 row with div. data (opportunity-data, customer-data) .. and then i have one field within a few uids of children. normally i think i should have 3 rows for 3 children, or not? are you shocked now, or is there someone who can help me? thx, greets pit

Hi Ravi, and how will i get all the possible childs into a calculated field of the dataset1? I think i need to do this in the query of dataset1, but how can i do this? can i use a "subquery" in the select? Thx, Greets Pit