Post reply

Data driven subsription (User specific)

  • 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

  • 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]

  • 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)

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply