Advanced SSRS Reporting Part 2

The following post walks through what can be viewed as a kind of "template" for advanced reporting in MSCRM as introduced in my previous post. This example leverages the reporting scenario that I described there by way of example. However, the point of this post is to describe the relevant parts of the template rather than describe the processing logic relevant to that specific example - the processing logic will of course vary greatly from report to report so I do not want to focus on this at all as that is besides the point.


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