CODEWITHSUNDEEP
reporting-services
James Youkhanis
James Youkhanis

Reputation: 45

SSRS email to only user with browser rights

Is there a way for to trigger sending the emails on demand by a user who only has Browser rights? Is there something within SSRS to control that feature.

Upvotes: 0

Views: 58

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

This answer is a lot of guessing as you don't provide much info. If this answer is not helpful, please edit your question and describe your current workflow and where SSRS currently sits in that workflow.

If you are using a report as part of your workflow, in this hypothetical situation:

  1. User submits and invoice somehow
  2. Invoice gets printed via SSRS report and at this point is deemed 'processed'
  3. Invoice "submitted by" user gets an email says their invoice has been processed

Now lets a assume you have a simple Invoice header table something like

InvoiceID   Amount    SubmittedBy       Processed    ProcessedBy
12345       456.78    [email protected]  NULL          NULL

Assuming the Report to generate in the invoice has a dataset something like

SELECT * FROM InvoiceHeader h
    JOIN InvoiceDetail d on h.InvoiceID = d.InvoiceID
WHERE h.InvoiceID = @InvoiceID

At the end of the dataset query we can simply add some extra code to update the header table and email the recipient (assumes dbmail is configured on the SQL Server)

So we will end up with a final dataset query that looks like this

SELECT * FROM InvoiceHeader h
    JOIN InvoiceDetail d on h.InvoiceID = d.InvoiceID
WHERE h.InvoiceID = @InvoiceID

IF EXISTS(SELECT * FROM InvoiceHeader WHERE InvoiceID = @InvoiceID and Processed IS NULL) -- checks in case the Invoice is being printed for a 2nd time
    BEGIN
         -- update the header table
          UPDATE InvoiceHeader SET Processed=1, ProcessedBy = CURRENT_USER WHERE InvoiceID = @InvoiceID
         -- send en email
         DECLARE @to varchar(100)
         DECLARE @body varchar(1000)
         SELECT 
               @to = SubmittedBy, 
               @body = 'Your invoice ' + @InvoiceID + ' for the amount of ' + CAST(Amount as varchar(30)) + ' has been processed by ' + @ProcessedBy
              FROM InvoiceHeader WHERE InvoiceID = @InvoiceID

         EXEC msdb.dbo.sp_send_dbmail 
                                     @recipients = @to,
                                     @subject = 'Invoice processed',
                                     @body = @body
         
    END

NOTE: This is not tested as I've done this off the top of my head so there may be some silly errors but you should get the idea.

Upvotes: 1

Related Questions