locked
SSRS Data-driven subscription sends 1 email for each object in search. RRS feed

  • Question

  • Environment:  SQL Server 2014 Db, SSRS in SharePoint mode.

    Objective: Weekday subscription, looks for values in table.  If found, send one email with results of .rdl (report).

    Query:

    SELECT 1
    FROM Dabatabse1.Schema1.Table1
    WHERE (Column1 = 'x' or Column2 = 'y')
    	AND Column3 NOT IN ( 'A','B','C')
    	AND last_modified >= DATEADD(m, -2, GETDATE());

     Issue: If the query table has 3 rows, it sends 3 copies of email, 4 rows, 4 copies of email....etc.  It even shows sending 4 emails on the status:
    


    -Al H


    • Edited by AlHowarthWF Tuesday, April 21, 2020 2:51 PM
    Tuesday, April 21, 2020 2:50 PM

Answers

  • Hi AlHowarthWF,

    According to your description, I think IF EXISTS can meet your demands. You can try below query:

    IF EXISTS (
    SELECT *
    FROM Dabatabse1.Schema1.Table1
    WHERE (Column1 = 'x' or Column2 = 'y')
        AND Column3 NOT IN ( 'A','B','C')
        AND last_modified >= DATEADD(m, -2, GETDATE())
    ) SELECT 1 

    Hope it will help

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 22, 2020 6:00 AM

All replies

  • Hi AlHowarthWF,

    According to your description, I think IF EXISTS can meet your demands. You can try below query:

    IF EXISTS (
    SELECT *
    FROM Dabatabse1.Schema1.Table1
    WHERE (Column1 = 'x' or Column2 = 'y')
        AND Column3 NOT IN ( 'A','B','C')
        AND last_modified >= DATEADD(m, -2, GETDATE())
    ) SELECT 1 

    Hope it will help

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 22, 2020 6:00 AM
  • Perfect, thanks.

    -Al H

    Wednesday, April 22, 2020 1:20 PM