Data driven subsription (User specific)

• PSB

  SSChampion

  Points: 13195

  More actions

  July 15, 2016 at 7:47 am

  #311826

  Hi,

  I need to create a SSRS report that will be only used for subscription . It will email list of users (QualityMgr) whose status is 'Validation REquired' and TaskCompletion is 100% . The data emailed should be user specific .

  Another email list for Reviewing Managers whose status is 'Approval Needed' and TaskCompletion is 100% . The data emailed should be user specific .

  CREATE TABLE #SendToUsers

  (

  WorkFlow VARCHAR(5),

  Plot VARCHAR(5),

  GM VARCHAR(10),

  PDate DATETIME,

  TaskCompletion VARCHAR(10),

  ReviewMGr VARCHAR(100),

  RvwMgrCheck VARCHAR(100),

  QualityMgr VARCHAR(100),

  QualityMgrCheck VARCHAR(100)

  )

  INSERT INTO #SendToUsers ( WorkFlow,Plot,GM,PDate,TaskCompletion,ReviewMGr,RvwMgrCheck,QualityMgr,QualityMgrCheck)

  SELECT 'SP','ADT','AZE','2016-04-29 17:00:00.000','100%','Anthony P.' ,'Pending Quality Mgr Validation', 'Tony Jordan', 'Validation Needed'

  UNION

  SELECT 'SP','ADT','KZK','2016-04-29 17:00:00.000','100%','Joseph F. ', 'Pending Quality Mgr Validation', 'Tony Jordan', 'Validation Needed'

  UNION

  SELECT 'ZP','GS','INM','2016-04-29 17:00:00.000','100%','Naser M..' ,'Pending Quality Mgr Validation', 'Danilo Rodrig', 'Validation Needed'

  UNION

  SELECT 'ZP','GS','TKG','2016-04-29 17:00:00.000','100%','Peter M.' ,'Pending Quality Mgr Validation', 'Danilo Rodrig', 'Validation Needed'

  UNION

  SELECT 'SP','DM','TZK','2016-04-29 17:00:00.000','90%','John L.' ,'Pending Task', 'Sergio Valentino', 'Pending Task'

  UNION

  SELECT 'SP','DM','SKG','2016-04-29 17:00:00.000','100%','Anthony P.', 'Pending Quality Mgr Validation', 'Sergio Valentino', 'Validation Needed'

  UNION

  SELECT 'TLM','BD','ING','2016-04-29 17:00:00.000','100%','Susy M.', 'Pending Quality Mgr Validation', 'Adam Hubber', 'Approved'

  UNION

  SELECT 'SUP','DM','SKG','2016-04-29 17:00:00.000','100%','Dorothy P.', 'Approval needed', 'Sergio Valentino', 'Validated'

  UNION

  SELECT 'SUP','BD','ING','2016-04-29 17:00:00.000','100%','Lucy M.', 'Approval needed', 'Adam Hubber', 'Validated'

  SELECT * FROM #SendToUsers

  -- When QualityMgrCheck ='Validation Needed' and TaskCompletion = 100% then user should receive email with data specific to them,

  -- example Quality Mgr 'Tony Jordan' should receive

  SELECT * FROM #SendToUsers WHERE QualityMgr = 'Tony Jordan' AND QualityMgrCheck ='Validation Needed' AND TaskCompletion = '100%'

  --Sergio Valentino should see only one record where Task Completion= 100%

  SELECT * FROM #SendToUsers WHERE QualityMgr = 'Sergio Valentino' AND QualityMgrCheck ='Validation Needed' AND TaskCompletion = '100%'

  --Similarly for ReviewMgr when RvwMgrCheck = 'Approval needed' and TaskCompletion = 100%

  SELECT * FROM #SendToUsers WHERE ReviewMGr = 'Lucy M.' AND RvwMgrCheck ='Approval needed' AND TaskCompletion = '100%'

  DROP TABLE #SendToUsers

  -- There will be a table or a query with email Address for QualityMgr where TaskCompletion is 100%

  CREATE TABLE #QualityMgr

  (

  Email VARCHAR(100),

  QualityMgr VARCHAR(100),

  QualityMgrCheck VARCHAR(50)

  )

  INSERT INTO #QualityMgr ( Email,QualityMgr,QualityMgrCheck )

  SELECT 'TJordan@xyz.com','Tony Jordan','Validation Needed'

  UNION

  SELECT 'SValentino@xyz.com','Sergio Valentino','Validation Needed'

  UNION

  SELECT 'DRodrig@xyz.com','Danilo Rodrig','Validation Needed'

  SELECT * FROM #QualityMgr

  DROP TABLE #QualityMgr

  -- There will be a table or a query with email Address for REviewMgr where TaskCompletion is 100%

  CREATE TABLE #ReviewMgr

  (

  Email VARCHAR(100),

  ReviewMgr VARCHAR(100),

  RvwMgrCheck VARCHAR(50)

  )

  INSERT INTO #ReviewMgr ( Email,ReviewMgr,RvwMgrCheck )

  SELECT 'LMaster@xyz.com','Lucy M.','Approval needed'

  UNION

  SELECT 'DTipps@xyz.com','Dorothy P.','Approval needed'

  SELECT * FROM #ReviewMgr

  DROP TABLE #ReviewMgr

  Thanks,

  PSB

• FridayNightGiant

  SSCertifiable

  Points: 7151

  More actions

  July 15, 2016 at 7:53 am

  #1890318

  I'm assuming you aren't running Enterprise Edition?

  Check out this link which is a technique of performing data driven subscriptions without enterprise edition

  http://www.sqlservercentral.com/articles/Development/datadrivensubscriptions/2432/[/url]

• PSB

  SSChampion

  Points: 13195

  More actions

  July 15, 2016 at 8:14 am

  #1890326

  I am using Enterprise edition and going to run subscription from SharePoint.

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

  Oct 19 2012 13:38:57

  Copyright (c) Microsoft Corporation

  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

• FridayNightGiant

  SSCertifiable

  Points: 7151

  More actions

  July 15, 2016 at 8:17 am

  #1890329

  OK check out these links then.

  https://blogs.msdn.microsoft.com/jtarquino/2011/06/14/reporting-services-data-driven-subscriptions-on-sharepoint-integrated-mode/[/url]

  https://msdn.microsoft.com/en-us/library/ms169673.aspx