Exclude Report in Email - SSRS Breaking & Routing

Is there a way to exclude the report attachment when sending an email via SSRS Breaking & Routing?

For some context, I am trying to automate customer invoice past due emails using a BAQ report on a schedule. It is working, but I need the report excluded from the email.

I guess it’s not as bad as I thought originally. In the email, the attached report is only showing the results for that customer, which is fine, but it would be cool if it’s possible to exclude!

1 Like

If you don’t want to include a report in Email then develop a simple BPM for email, Report Routing not needed.

3 Likes

I have a BPM set up in a schedule that sends the following email
If they have Invoices due this week it includes them
and if they have overdue invoices it includes them

if they are all up to date they don’t get an email

1 Like

This is beyond my knowledge gap at this point. I’m intrigued, I’m searching on my own, but if you have a moment a little hand up would be much appreciated!

Ok, it’s just after 10pm here, I’ll send something in the morning

2 Likes

Sounds great, thank you so much. I’ve come across the Updateble BAQ “GetList” approach. I did come across this one too, using SysAgentSched update as the “trigger”, wondering if this was your approach or not:

Ditto :slight_smile:

Looks like a very nice solution, @LBARKER! Thanks so much for the feedback!

Don’t forget the important things you lose when going away from Break/Routing.

  • B/R will generate multiple emails (or print jobs). That’s what the “Break” part is for. A BPM with a Email widget will generate exactly 1 email when the BPM fires.

  • B/R can set fields (To:, CC:, Subject:, message body, etc…) dynamically (based on report data)

  • B/R attaches the report to it, and the name of the attachment can be dynamic.

  • An Email widget is very limited in what it can do.

  • Emails (single or multiple) can be generated in an Exec Custom Code widget, but requires building the email message by hand.

3 Likes

Hello,

A couple of things to note about the code below

We have this run every Monday morning at 6am, it loops through all open invoices and checks the due date, if they are due this coming week it groups them into a table, if they are overdue it groups them into another table

If the balance value of the Invoice is below a certain value it skips it, if the invoice is a CM it skips it too

If you only want the code to work for overdue invoices, I’m sure you know how to comment out code

We found that a vast majority of our customers had dedicated email addresses for their accounts department, some had automatic invoice handlers, so we added some UD fields to our Customers record.

Most have moved away from using personal email addresses for their accounts dept. due to the fact that people move on from jobs

  • BTAccntEmail_c string x(200) you can fit more than one email address in it
  • BTStmntEmail_c
  • BTInvEmail_c

We also added a checkbox to determine whether to send these invoice reminders or not

  • SendReminders_c (true/false)

To get around using the email UD fields you could add a foreach on the customers contacts to find the contact that is set as a billing contact, and to get around using the added checkbox you could set the condition off another checkbox

First up I set up a System Agent Schedule that runs at 6am every Monday (best to run this type of code at an off peak time)

Then I set up a Standard Data Directive on SysAgentSched

Then I added an Execute Custom Code widget with the following code in it

Note: if you end using this code you will need to update the number of the AgentSchedule in the code…. And a few other bits specific to your company

And make sure you test it in a test environment with your email address hardcoded into the To: field


bool tvSYS = ttSysAgentSched.Any(s => s.Updated() && ttSysAgentSched.Any(s1 => s1.Unchanged() && s1.SysRowID == s.SysRowID && s1.LastRunOn != s.LastRunOn && s.AgentSchedNum == 209860));
if (tvSYS == true) {

  var sunday = DateTime.Today.AddDays(-(int) DateTime.Today.DayOfWeek);
  var nextsunday = DateTime.Today.AddDays(-(int) DateTime.Today.DayOfWeek).AddDays(7);
  string emailSubject = "";
  string emailBody = "";
  bool noContact = false;

  var InvCusts = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.InvoiceBal > 1 && i.DueDate < nextsunday && Db.Customer.Any(c => c.CustNum == i.CustNum && c.NoContact == false && c.SendReminders_c == true)).Select(i => i.CustNum).Distinct().ToList();

  if (InvCusts != null) {
    foreach(var InvCust in InvCusts) {
      ////////Cust details////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
      var style = "<style> table, th, td {border: 1px solid  #000; border-collapse: collapse; padding-left: 5px; padding-right: 5px;} th {background-color: #c0c0c0;} th {width:110px; text-align:centre;}</style>";
      string emailBodyDueThisWeek = "";
      string emailBodyOverDue = "";
      var emailSub = "";
      var emailTo = "";
      var emailFooter = "<a href=\"https://www.yourcompany.com.au\"><img src=\"https://yourcompany.com.au/es19/banner.png\" alt=\"Treadwell Logo\"></a>";

      var oBillTo = Db.Customer.Where(c => c.Company == Session.CompanyID && c.CustNum == InvCust).Select(x => new {
        x.BTAccntEmail_c, x.BTStmntEmail_c, x.EMailAddress, x.BTInvEmail_c, x.Name
      }).FirstOrDefault();

      var custName = oBillTo.Name;

      var accntEmail = oBillTo.BTAccntEmail_c;
      if (!accntEmail.Contains("@")) {
        accntEmail = oBillTo.BTStmntEmail_c;
      }
      if (!accntEmail.Contains("@")) {
        accntEmail = oBillTo.EMailAddress;
      }
      if (!accntEmail.Contains("@")) {
        accntEmail = oBillTo.BTInvEmail_c;
      }
      if (!accntEmail.Contains("@")) {
        accntEmail = "ar@yourcompany.com.au";
      }

      ////////End Cust details////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

      System.Text.StringBuilder messageLine = new System.Text.StringBuilder();
      System.Text.StringBuilder messageLineOD = new System.Text.StringBuilder();
      decimal balInv = 0;
      decimal totalInv = 0;
      decimal balInvOD = 0;
      decimal totalInvOD = 0;
      int DTW = 0;
      int ODNum = 0;

      string DTWText = "invoice is";
      string ODText = "invoice is";

      ///////////////////Due this week////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
      var Invs = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.InvoiceBal > 1 && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.CustNum == InvCust && i.DueDate < nextsunday).ToList();
      if (Invs != null) {
        foreach(var Inv in Invs) {
          if (Inv.DueDate > sunday) {

            DTW = DTW + 1;
            balInv = balInv + Inv.InvoiceBal;
            totalInv = totalInv + Inv.InvoiceAmt;

            var invnUMBER = Inv.InvoiceNum;

            var InvoiceAmt = string.Format("{0:C2}", Inv.InvoiceAmt);
            var InvoiceBal = string.Format("{0:C2}", Inv.InvoiceBal);
            var InvoiceDue = string.Format("{0:dd/MM/yyyy}", Inv.DueDate);
            var InvoiceTot = string.Format("{0:C2}", totalInv);
            var InvoiceBalT = string.Format("{0:C2}", balInv);

            messageLine.AppendLine($"<tr><td  style=\"text-align:right;\">{invnUMBER}</td><td style=\"text-align:right;\">{InvoiceAmt}</td><td style=\"text-align:right;\">{InvoiceBal}</td><td style=\"text-align:right;\">{InvoiceDue}</td></tr>");
            var totalLine = $"<tr><td  style=\"text-align:right;\">Totals:</td><td style=\"text-align:right;\">{InvoiceTot}</td><td style=\"text-align:right;\">{InvoiceBalT}</td><td style=\"text-align:right;\"></td></tr>";

            if (DTW > 1) {
              DTWText = "invoices are";
            }

            emailBodyDueThisWeek = "<br><br>The following " + DTWText + " due to be paid this week:<br><br><table> <tr><th>Invoice</th> <th>Invoice Amount</th> <th>Invoice Balance</th> <th>Due Date</th></tr>" + messageLine + totalLine + "</table><br><br>";

            ///////////////////END Due this week////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
          }

          ///////////////////OVERDUE////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

          if (Inv.DueDate <= sunday) {

            balInvOD = balInvOD + Inv.InvoiceBal;
            totalInvOD = totalInvOD + Inv.InvoiceAmt;
            ODNum = ODNum + 1;

            var invnUMBEROD = Inv.InvoiceNum;

            var InvoiceAmtOD = string.Format("{0:C2}", Inv.InvoiceAmt);
            var InvoiceBalOD = string.Format("{0:C2}", Inv.InvoiceBal);
            var InvoiceDueOD = string.Format("{0:dd/MM/yyyy}", Inv.DueDate);
            var InvoiceTotOD = string.Format("{0:C2}", totalInvOD);
            var InvoiceBalTOD = string.Format("{0:C2}", balInvOD);

            messageLineOD.AppendLine($"<tr><td  style=\"text-align:right;\">{invnUMBEROD}</td><td style=\"text-align:right;\">{InvoiceAmtOD}</td><td style=\"text-align:right;\">{InvoiceBalOD}</td><td style=\"text-align:right;\">{InvoiceDueOD}</td></tr>");
            var totalLineOD = $"<tr><td  style=\"text-align:right;\">Totals:</td><td style=\"text-align:right;\">{InvoiceTotOD}</td><td style=\"text-align:right;\">{InvoiceBalTOD}</td><td style=\"text-align:right;\"></td></tr>";

            if (ODNum > 1) {
              ODText = "invoices are";
            }

            emailBodyOverDue = "<br><br>The following " + ODText + " overdue:<br><br><table> <tr><th>Invoice</th> <th>Invoice Amount</th> <th>Invoice Balance</th> <th>Due Date</th></tr>" + messageLineOD + totalLineOD + "</table>";
          }
        }

        ///////////////////END OVERDUE////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

        emailBody = "<html>" + style + "<body><p><b>" + custName + "</b><br><br>Dear Customer,<br><br>This correspondence is to advise you of your current account status: " + emailBodyOverDue + emailBodyDueThisWeek + "Thank you and we look forward to your prompt payment.<br><br>Should you have any questions or require a copy of any invoices, please reply to this email or contact me on the details below.<br>If payment has already been made, thank you and please disregard this email.<br><br>Kind regards,<br><br><b style=\"color:red;\">FirstName LastName</b> | Accounts<br>P  1800 246 800<br>ar@yourcompany.com.au</p><BR>" + emailFooter + "<BR><p style=\"color: #c0c0c0;\">This email and any attached files may contain confidential information and/or copyright material and is intended for the use of the addressee only. If you have received this transmission in error you may not reproduce, copy, distribute or disclose its contents to anyone else; please notify Your Company by return email to: mail@yourcompany.com.au immediately and delete this email  from your computer system. Any views expressed in this email are those of the individual sender and may not necessarily reflect the views of Your Company.</p></body></html>";

        emailSubject = "Your Account Summary from Your Company";


      }
	  
	 var conSt = Db.ConnectionString;
      if (conSt.Contains("Pilot") || conSt.Contains("Test"))
	 {
		accntEmail = "test@yourcompany.com"; 
	 } 
	  
	  
	  
	  
      var mailer = this.GetMailer(async :false);
      var message = new Ice.Mail.SmtpMail();
      message.SetFrom("Accounts Receivable | Your Company<do-not-reply@yourcompany.com.au>"); //Accounts Receivable   ("do-not-reply@yourcompany.com.au");
      message.SetTo(accntEmail);
      message.SetReplyTo("ar@yourcompany.com.au");
      //  message.SetCC("");
      //  message.SetBcc("lrb@yourcompany.com.au"); 
      message.SetSubject(emailSubject);
      message.SetBody(emailBody);
      //message.IsBodyHtml = true;
      message.Priority = System.Net.Mail.MailPriority.High;
      mailer.Send(message);

    }
  }
}

You can use the above for a lot of different things
We also use it to follow up with suppliers for overdue Purchase orders

This does not send any attachments

Happy coding!!

3 Likes

You are awesome! Some really clever stuff in here. I did not know you could have a data directive on SysAgentSched.

Thank you for sharing! I’m going to dig into this next week and see if I can get it working.

Hello Lawson. I came across this while researching about sending reminders to our customers. I am a novice in regards to custom code like this so I have a few questions.
Can you show where in the code where I would put my email address as I am testing this?
Do I need to fill out the UD fields while testing or do I only do that when it’s live?
Is there a way to manually run this code instead of a schedule? If not, I’m assuming that if I change the day and time in the code that it will run accordingly?
I also noticed in your code about Db.ConnectionString and checks if it contains Pilot or Test. Is this something I need to check?
and lastly, you mentioned you use this on past due POs, can you share that code too? Thank you very much.

I prefer to use the BAQ BPM method of scheduling a process assuming you are on a version that doesn’t have functions

In the below example you can manually run it by hitting the GetList button in the UBAQ. Or I’m sure you could create a simple customization that calls the BAQ and in turn triggering the GetList method.

1 Like

Hello @JuyTWG

A couple of answers

Q. Can you show where in the code where I would put my email address as I am testing this?
A. change this

message.SetTo(accntEmail); 

to

 message.SetTo("Jeffrey@yourcompany.com");

Q. Do I need to fill out the UD fields while testing or do I only do that when it’s live?
A. No

Q. Is there a way to manually run this code instead of a schedule? If not, I’m assuming that if I change the day and time in the code that it will run accordingly?
A. I’ve never had to run it manually, when testing I use a different System Agent Schedule that I turn on and off to make the BPM fire off, if you only want to run it manually, you may want to move the BPM to a UD table and trigger it off a checkbox

Q. I also noticed in your code about Db.ConnectionString and checks if it contains Pilot or Test. Is this something I need to check?
A. This is so when we roll our Live DB to our Test environment it stops the emails going to our Customers

Q. and lastly, you mentioned you use this on past due POs, can you share that code too? Thank you very much.
A. This has a lot of things in it specific to our company, it will take a bit to clean it up…

I’ve modified the code to make it easier to test

int sysAgent = 0;
sysAgent = 209860; //Live runs every monday morning @ 6:15am
// sysAgent = 195235; //Test - run on demand 

bool tvSYS = ttSysAgentSched.Any(s => s.Updated() && ttSysAgentSched.Any(s1 => s1.Unchanged() && s1.SysRowID == s.SysRowID && s1.LastRunOn != s.LastRunOn && s.AgentSchedNum == sysAgent));

if (tvSYS == true) {

  var sunday = DateTime.Today.AddDays(-(int) DateTime.Today.DayOfWeek);
  var nextsunday = DateTime.Today.AddDays(-(int) DateTime.Today.DayOfWeek).AddDays(7);
  string emailSubject = "";
  string emailBody = "";
  bool noContact = false;

  var InvCusts = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.InvoiceBal > 1 && i.DueDate < nextsunday && Db.Customer.Any(c => c.CustNum == i.CustNum && c.NoContact == false && c.SendReminders_c == true)).Select(i => i.CustNum).Distinct().ToList();

  if (InvCusts != null) {
    foreach(var InvCust in InvCusts) {
      ////////Cust details////////////////////
      var style = "<style> table, th, td {border: 1px solid  #000; border-collapse: collapse; padding-left: 5px; padding-right: 5px;} th {background-color: #c0c0c0;} th {width:110px; text-align:centre;}</style>";
      string emailBodyDueThisWeek = "";
      string emailBodyOverDue = "";
      var emailSub = "";
      var emailTo = "";
      var emailFooter = "<a href=\"https://www.yourcompany.com.au\"><img src=\"https://yourcompany.com.au/es19/banner.png\" alt=\"Treadwell Logo\"></a>";

      var oBillTo = Db.Customer.Where(c => c.Company == Session.CompanyID && c.CustNum == InvCust).Select(x => new {
        x.BTAccntEmail_c, x.BTStmntEmail_c, x.EMailAddress, x.BTInvEmail_c, x.Name
      }).FirstOrDefault();

      var custName = oBillTo.Name;

      var accntEmail = oBillTo.BTAccntEmail_c;
      if (!accntEmail.Contains("@")) {
        accntEmail = oBillTo.BTStmntEmail_c;
      }
      if (!accntEmail.Contains("@")) {
        accntEmail = oBillTo.EMailAddress;
      }
      if (!accntEmail.Contains("@")) {
        accntEmail = oBillTo.BTInvEmail_c;
      }
      if (!accntEmail.Contains("@")) {
        accntEmail = "ar@yourcompany.com.au";
      }

      ////////End Cust details//////////////////

      System.Text.StringBuilder messageLine = new System.Text.StringBuilder();
      System.Text.StringBuilder messageLineOD = new System.Text.StringBuilder();
      decimal balInv = 0;
      decimal totalInv = 0;
      decimal balInvOD = 0;
      decimal totalInvOD = 0;
      int DTW = 0;
      int ODNum = 0;

      string DTWText = "invoice is";
      string ODText = "invoice is";

      ///////////////////Due this week/////////

     var Invs = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.InvoiceBal > 1 && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.CustNum == InvCust && i.DueDate < nextsunday).ToList();
      if (Invs != null) {
        foreach(var Inv in Invs) {
          if (Inv.DueDate > sunday) {

            DTW = DTW + 1;
            balInv = balInv + Inv.InvoiceBal;
            totalInv = totalInv + Inv.InvoiceAmt;

            var invnUMBER = Inv.InvoiceNum;

            var InvoiceAmt = string.Format("{0:C2}", Inv.InvoiceAmt);
            var InvoiceBal = string.Format("{0:C2}", Inv.InvoiceBal);
            var InvoiceDue = string.Format("{0:dd/MM/yyyy}", Inv.DueDate);
            var InvoiceTot = string.Format("{0:C2}", totalInv);
            var InvoiceBalT = string.Format("{0:C2}", balInv);

            messageLine.AppendLine($"<tr><td  style=\"text-align:right;\">{invnUMBER}</td><td style=\"text-align:right;\">{InvoiceAmt}</td><td style=\"text-align:right;\">{InvoiceBal}</td><td style=\"text-align:right;\">{InvoiceDue}</td></tr>");
            var totalLine = $"<tr><td  style=\"text-align:right;\">Totals:</td><td style=\"text-align:right;\">{InvoiceTot}</td><td style=\"text-align:right;\">{InvoiceBalT}</td><td style=\"text-align:right;\"></td></tr>";

            if (DTW > 1) {
              DTWText = "invoices are";
            }

            emailBodyDueThisWeek = "<br><br>The following " + DTWText + " due to be paid this week:<br><br><table> <tr><th>Invoice</th> <th>Invoice Amount</th> <th>Invoice Balance</th> <th>Due Date</th></tr>" + messageLine + totalLine + "</table><br><br>";

            ///////////////////END Due this week//////////////////////
          }

          ///////////////////OVERDUE//////////////////////////////

          if (Inv.DueDate <= sunday) {

            balInvOD = balInvOD + Inv.InvoiceBal;
            totalInvOD = totalInvOD + Inv.InvoiceAmt;
            ODNum = ODNum + 1;

            var invnUMBEROD = Inv.InvoiceNum;

            var InvoiceAmtOD = string.Format("{0:C2}", Inv.InvoiceAmt);
            var InvoiceBalOD = string.Format("{0:C2}", Inv.InvoiceBal);
            var InvoiceDueOD = string.Format("{0:dd/MM/yyyy}", Inv.DueDate);
            var InvoiceTotOD = string.Format("{0:C2}", totalInvOD);
            var InvoiceBalTOD = string.Format("{0:C2}", balInvOD);

            messageLineOD.AppendLine($"<tr><td  style=\"text-align:right;\">{invnUMBEROD}</td><td style=\"text-align:right;\">{InvoiceAmtOD}</td><td style=\"text-align:right;\">{InvoiceBalOD}</td><td style=\"text-align:right;\">{InvoiceDueOD}</td></tr>");
            var totalLineOD = $"<tr><td  style=\"text-align:right;\">Totals:</td><td style=\"text-align:right;\">{InvoiceTotOD}</td><td style=\"text-align:right;\">{InvoiceBalTOD}</td><td style=\"text-align:right;\"></td></tr>";

            if (ODNum > 1) {
              ODText = "invoices are";
            }

            emailBodyOverDue = "<br><br>The following " + ODText + " overdue:<br><br><table> <tr><th>Invoice</th> <th>Invoice Amount</th> <th>Invoice Balance</th> <th>Due Date</th></tr>" + messageLineOD + totalLineOD + "</table>";
          }
        }

        ///////////////////END OVERDUE//////////////////// 

        emailBody = "<html>" + style + "<body><p><b>" + custName + "</b><br><br>Dear Customer,<br><br>This correspondence is to advise you of your current account status: " + emailBodyOverDue + emailBodyDueThisWeek + "Thank you and we look forward to your prompt payment.<br><br>Should you have any questions or require a copy of any invoices, please reply to this email or contact me on the details below.<br>If payment has already been made, thank you and please disregard this email.<br><br>Kind regards,<br><br><b style=\"color:red;\">FirstName LastName</b> | Accounts<br>P  1800 246 800<br>ar@yourcompany.com.au</p><BR>" + emailFooter + "<BR><p style=\"color: #c0c0c0;\">This email and any attached files may contain confidential information and/or copyright material and is intended for the use of the addressee only. If you have received this transmission in error you may not reproduce, copy, distribute or disclose its contents to anyone else; please notify Your Company by return email to: mail@yourcompany.com.au immediately and delete this email  from your computer system. Any views expressed in this email are those of the individual sender and may not necessarily reflect the views of Your Company.</p></body></html>";

        emailSubject = "Your Account Summary from Your Company";


      }
	  
	 var conSt = Db.ConnectionString;
      if (conSt.Contains("Pilot") || conSt.Contains("Test") || sysAgent == 195235 )
	 {
		accntEmail = "test@yourcompany.com"; 
	 } 
	  
	  
	  
	  
      var mailer = this.GetMailer(async :false);
      var message = new Ice.Mail.SmtpMail();
      message.SetFrom("Accounts Receivable | Your Company<do-not-reply@yourcompany.com.au>"); //Accounts Receivable   ("do-not-reply@yourcompany.com.au");
   //   message.SetTo(accntEmail);
      message.SetTo("Jeff@yourcompany.com");
      message.SetReplyTo("ar@yourcompany.com.au");
      //  message.SetCC("");
      //  message.SetBcc(""); 
      message.SetSubject(emailSubject);
      message.SetBody(emailBody);
      // message.IsBodyHtml = true;
      message.Priority = System.Net.Mail.MailPriority.High;
      mailer.Send(message);

    }
  }
}

Thank you Lawson. I just got back from a long weekend vacation. I will test this and let you know how it goes. Thank you again for taking the time to answer my questions. have a great day!!