I have segmented the relevant parts of the template into Sections A-G - the entire declarative statement which is input as a Data Set into the report appears at the bottom of this article - you should reference that in terms of the rest of this article. The following screenshot shows how this statement ties into the SSRS report.
- Section A - The majority of this report effort is probably going to be in terms of retrieving and formatting the source data that you want to output in the report. So you need to have a way of testing it outside of the report to ensure that it's retrieving the correct data inside SSMS (SQL Server Management Studio). As the parameter that this report relies on is "@CRM_FilteredList" we define this variable here which should pretty much mirror how the variable is defined in SSRS (see below - this format also adheres to the CRM SDK reporting standard). This section is commented out as it is only used for testing in SSMS. It should be uncommented when testing the declarative statement but should be commented out when inputting in SSMS (please do not forget to comment out this section when inputting into the report!)
- Section B - Create a temp table for processing the report data. This is not required in all cases of course but comes in very handy when performing complex processing.
- Section C - Populate the temp table with a query that will be used as the basis for subsequent processing logic.
- Section D - This command executes the query in Section C
- Section E - Run a cursor to process through the records that were populated into the temp table
- Section F - Perform the processing logic specific to your report including lookups, updates, deletes etc. and update your temp table as necessary.
- Section G - Once your temp table has been updated via the above processing logic output the results joining to other CRM views to bring in whatever additional attributes are required. Remember to join using the CRM filtered views.
And that pretty much is that. Once you have this data you can then input as a Data Source into your report and use whatever fancy reporting techniques you want for rendering your report. As long as you adhere to using filtered views and respect the naming conventions of the CRM parameters, you should be able to add this report in a context sensitive ways to forms and views with the standard Advanced Find pre-filtering.
/* SECTION A: THIS IS FOR TROUBLESHOOTING THE REPORT IN SQL: drop table #report declare @CRM_FilteredList nvarchar(100) set @CRM_FilteredList = 'select * from FilteredList where listid = ''92F129FB-4099-E111-9EFF-006073502237''' */ declare @SQL nvarchar(max) BEGIN /* SECTION B: CREATE TEMP TABLE */ create table #report ( contactid nvarchar(36), fullname nvarchar(100), processed bit, ord int, inlist bit ) /* SECTION C: FIRST OBTAIN BASIC REPORT DATA */ set @SQL = 'insert into #report(ord, contactid, fullname, processed, inlist) ' + 'select (ROW_NUMBER() OVER(ORDER BY c.Fullname))*10, c.contactid, c.fullname, 0, 1 ' + 'from (' + @CRM_FilteredList + ') AS list ' + 'inner join FilteredListMember m on m.listid = list.listid and EntityType = 2 ' + 'inner join FilteredContact c on c.contactid = m.EntityId ' /* SECTION D: EXECUTE ABOVE STATEMENT WHICH POPULATES THE #report TEMP TABLE */ exec(@SQL) /* SECTION E: RUN THROUGH REPORT TEMP TABLE RECORDS */ declare @cid nvarchar(36) declare @sid nvarchar(36) declare @fullname nvarchar(100) declare @ord int declare @inlist int declare ProcessTemp cursor for select r.contactid, con.record2id, con.record2idname, r.ord from #report r inner join FilteredConnection con on con.record1id = r.contactid and con.record1objecttypecode = 2 inner join FilteredContact c on c.contactid = con.record1id where con.record2roleid in ('2C110BC6-168D-E111-8DD6-006073502237','EE375944-5415-437D-9336-7698CF665B26') /* Partner/Spouse */ order by r.ord open ProcessTemp fetch ProcessTemp into @cid, @sid, @fullname, @ord while @@fetch_status = 0 begin /* SECTION F: PERFORM WHATEVER PROCESSING LOGIC IS NECESSARY - INSERTS/UPDATES/DELETES. THE PROCESSING BELOW IS JUST SAMPLE PROCESSING - THIS COULD OF COURSE BE ANY LOGIC */ update #report set processed = 1 where contactid = @cid and processed = 0 if (@@ROWCOUNT != 0) -- if update wasn't successful must be because contact was already there begin -- delete if it appears in the marketing list since it will be added above the partner record delete from #report where contactid = @sid and processed = 0 if (@@ROWCOUNT = 0) -- No record deleted ==> was not part of original marketing list set @inlist = 0 else set @inlist = 1 -- insert/reinsert above partner record insert into #report (ord, contactid, fullname, processed, inlist) values (@ord, @sid, @fullname, 1, @inlist) end fetch ProcessTemp into @cid, @sid, @fullname, @ord end --while close ProcessTemp deallocate ProcessTemp /* SECTION G: OUTPUT RESULTS BY JOINING #report TEMP TABLE TO OTHER CRM RECORDS - THIS IS WHAT WILL BE OUTPUT TO SSRS */ select r.inlist ,c.contactid ,c.firstname ,c.lastname ,c.address1_line1 ,c.address1_line2 from #report r inner join filteredcontact as c on c.ContactId = r.ContactId order by r.ord END