serving the solutions day and night


Monday, March 28, 2016

Dynamics CRM Report - Generate SSRS report using CRM DB and Extension Database

1) Create 2 data sources
a)DNSCRM_EXT (connect ext database, not using filtered table)

b)MSCRM_DataSource (connect CRM database, using filtered table)

2)Create 2 data sets
a)data set DS_LoginUSERID, using MSCRM_DataSource, to login user

b)using DNSCRM_EXT, to get all contacts(it's complex sql query) for login user.
 Pass login user id from 1st data source to 2nd data source using Parameters
 select DISTINCT
  DNS_MSCRM..dns_vrhistory vrh with (nolock)
  inner join c with (nolock) on c.contactid = vrh.dns_ContactID
  inner join DNS_MSCRM.dbo.dns_usersecurity us on us.dns_TeamID = vrh.edm_TeamID
  us.dns_UserId= @LoginUserID
3)Create Parameter, default values->Get Value from a query->DataSet(DS_LoginUSERID), Value(UserID)

4)create DNSCRM_EXT data source on ssrs server (

5)Add report in CRM Report & publish. 

No comments: