TABLE OF CONTENTS

• Overview
• Steps
  • 1. Create a Data Mining Report (DMR)
  • 2. Create an Email Template
  • 3. Create a PowerShell Script to Send the Email Weekly
  • 4. Test the Script
• Further Support

Overview

When using the ACE engine to automatically process Pending applications, new applications may appear in the Accepted Applications queue without manual intervention. To keep the applications team informed, you can configure a weekly summary email listing which applications have been processed.

Steps

Follow these steps to set up a weekly summary email:

1. Create a Data Mining Report (DMR)
2. Create an Email Template
3. Create a PowerShell script to send the email weekly

1. Create a Data Mining Report (DMR)

• Navigate to Applications -> All Applications.
• Click the Datamining Report button in the Ribbon.
• Click Show History Fields in the Ribbon.
• Tick both boxes next to Rationalisation Status.
  
• Untick Process.
• In the Data Source tab of the Ribbon:
  • Ensure All Applications is selected.
  • Click Rationalisation Status and select: Accepted, Rejected, Rationalised.
    
• Click Run Datamining Report.
• In the Build Report area, click Edit Column Layout, Filter, Sort and configure the following:
  • Rationalisation Status Previous Value, Hide Column: Checked
  • Rationalisation Status Modified By, Hide Column: Checked
  • Rationalisation Status Previous Date, Filter Expression:> '[Today - 7]'
  • Rationalisation Status Previous Date, Rename to Date (click text to rename)
  • Rationalisation Status Previous Date, Display Format:dd/MM/yyyy
  • Rationalisation Status Modified By, Filter Expression: like 'ace*'
    
• Click Apply Changes. The report will now display applications processed by ACE in the last week.
• Save the DMR, assign a meaningful name, and note its ID.

2. Create an Email Template

Create the email template in the ManagementStudio global module, not the Applications module.

• Navigate to Administration → PS Scripts, Emails, Buttons.
• Untick Scripts and Buttons.
• Under Email Templates, select the current project name (the first icon), then click Click here to add new item.
• Enter ACE Apps Processed in Last 7 Days as the Email Name and the Subject.
• Use the column picker to add the CC field.
  
• Enter one or more email addresses (or a distribution group) in the CC field.
• Click Save Changes.
• Click the Edit button.
• Drag two Paragraph UI Elements into the body of the email.
• In the first Paragraph Element add text such as:
  These are the Pending applications processed by the ACE connector in the last seven days.<br><br>
• In the second Paragraph Element add this text:

<style>
table.table {
border:solid 1px #c0c0c0;
border-collapse: collapse;
}
tbody th, tbody td, thead th {
padding: 3px;
border-width: 1px;
border-style: solid;
border-color: #c0c0c0;
vertical-align: top;
font-size: 12px;
}
thead th {
text-align: center
}
</style>

[A-DMR-XX-ScopeDMR-AsTable]

• In the last line replace XX with the Data Mining Report ID.
• Under the UI Element Style for the second Paragraph turn on Enable Markdown

• To include the report as a file attachment, add this to the bottom of the second Paragraph:
  [A-DMR-XX-ScopeDMR-AsAttachment]
  Replace XX with the Data Mining Report ID.
• Click Save Changes.
• Click Preview Email -> Preview Email in mail client; an email will be sent to your email address within a few minutes.
• Close the Email Builder.
• Note the email template ID from the furthest right column.

3. Create a PowerShell Script to Send the Email Weekly

• Navigate to Administration → PS Scripts, Emails, Buttons.

• Untick Emails and Buttons.

• Under PowerShell Scripts, select the current project name, then click Click here to add new item.

• Give the script a meaningful name such as ACE Apps Processed in Last 7 Days.

• Click the Edit button (scroll icon).

• Add the following command (replace YY with the Email Template ID) and click OK:

  Send-MSEmails -Module ManagementStudio -ToIds 1 -EmailTemplateId YY -SpreadOverHours 0 -OverrideSendTo ''
  

• Use the column picker to display the Grant Access 2 column.

• Assign the Project Admins role to Grant Access 2.

• Click Save Changes.

• Schedule the PowerShell Script to run weekly, for example every Monday at 12pm.

• 
  

4. Test the Script

• Click the play button to test the script.
• Confirm that an email is sent containing the DMR.

Further Support

For additional assistance, visit the ManagementStudio Service Desk to search the knowledge base or submit a new support ticket.