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
