Automatically Email or Save a Datamining Report

Modified on Fri, 31 Mar 2023 at 05:49 PM



ManagementStudio and a few lines of PowerShell its possible to run a datamining report, inject it in to a Excel template and either Email it, Save it to a share or both.

Step 1 - Create the Datamining report

  • Switch to the relevant module from the vertical menu. In this example, Applications (1).
  • Click Datamining Report (2) from the ribbon.

Select the fields required within the report (1).

Click Run Datamining Report (2).

  • Perform any sorting, filtering, reordering and removing of columns in the DMR so that only the columns/rows you want to display in the report remains. 
  • Click Run Datamining Report.
  • Click Save Report button.
  • Take a note of the Saved Report Id in the window title. E.g 26

Step 2 - Create the Email Template

Before we can send and email we need to create an Email Template.

  • Switch to Administration->PS Scripts, Emails, Buttons (1)
  • Scroll down to Email Templates and click on ManagementStudio (2)
  • Click Click here to add new item (3)

  • Enter the following under the headings (1)
  • Email Name:DMReportEmailSend (Note - This name must be used)
  • Ensure Is enabled is ticked
  • Subject:[CustomEmailSubject]
  • Edit the email by clicking the small scroll icon and add the following two lines only (2)
  • [CustomEmailText1]
  • [CustomEmailText2]
  • Click OK (3)
  • Ensure module is ManagementStudio
  • Click Save Changes (4)
  • Make a note of the Email template Id (5)

Save the email and make a note of the Id that the email acquires once saved. Note the square brackets around the subject and two email text lines.

Step 3 - Configure the PowerShell Script

Now we need to add the PowerShell. Please note, the PowerShell will need to be created within the target module same as the report module eg Application report will have the corresponding PowerShell created within the Application module.

  • Switch to Administration-> Applications ->PowerShell Scripts (1)
  • Click Click here to add new item (2)

  • Enter the following within the specified columns

Script NameDataminingToExcel
CategoryInternal - Inject DMR to Excel
DescriptionScript to inject a datamining report into Excel
Click Edit to edit the PowerShell scriptPaste in the below script. See note below for changes that are required.
ModuleSelect the DMR module required.
Run AsApi Account
Grant AccessApi Connectors

Change the last line in the script containing EmailTemplateId to match the template id you created from step 1.

$config = Get-MSExtensionConfigData -ExtInstanceId $scriptArgs.CallerInput
$VarCount = ($config.misc.UserVars.count - 1)
$i = 0

$VarName = $config.misc.UserVars.variable[$i]
$VarValue = $config.misc.UserVars.Value[$i]
New-Variable -Name $Varname -Value $VarValue
Write-MSDebug -LogText "Variable $($VarName) = $($VarValue)`r`n"
}While ($i -le $VarCount)

$CustomKeyWords = @()
$CustomKeyWords += @{ Keyword = "[CustomEmailSubject]"; Value = $CustomEmailSubject}
$CustomKeyWords += @{ Keyword = "[CustomEmailText1]"; Value = $CustomEmailText1}
$CustomKeyWords += @{ Keyword = "[CustomEmailText2]"; Value = $CustomEmailText2}

## Get the Existing DMR Schema
$schema = (Get-MSDataminingReportSchema -ReportId $DMReportID).DataSource

## Create a new DataSource and reuse the Adv. Filter from the existing DMR
$DS = New-MSDataminingDataSource -Ids $scriptArgs.Items -FilterExpression $schema.FilterExpression -SortExpression $schema.SortExpression

## Run the DMR
$RptInfo = Get-MSDataminingReportById -ReportId $DMReportID -HeaderFormat DisplayName -DataSource $DS

## Convert the DMR results to Excel File on the Server
$DataPage1 = New-MSExcelReportEntry -data $RptInfo.Data -SheetName $InjectToTabName -IncludeColumnHeaders -ExcelTemplateResourceName $ExcelTemplateName

If($SaveLocation -ne $null){
    $OutputFile = New-MSExcelReport -Reports @($DataPage1) -SaveTo $SaveLocation

If($EmailTo -ne $null){
    $OutputFile = New-MSExcelReport -Reports @($DataPage1)
    Send-MSEmails -Module ManagementStudio -ToIds 1 -EmailTemplateId 1190 -CustomKeyWords $CustomKeyWords -SpreadOverHours 0 -FileAttachments $OutputFile.Reports -OverrideSendTo $EmailTo

Step 4 - Prepare the Excel Template

We need to add an Excel template that the data can be injected. Create an Excel file. Format it as you wish with headers or any static data. There should be a Excel Tab that the data will be injected in to. If that Tab does not exist in the template ManagementStudio will create the Tab as it merges the data with the template. Having the Tab already in place means that you can preformat the cells that the data will land in. The template must then be uploaded to ManagementStudio as a project resource.
  • Switch to Administration->Project Resources (1)
  • Click Add Resource (2) and upload the file
  • Template attached (3)

Step 5 - Set up the ESM Plan

Finally we need an ESM Plan to select the data for the report.
  • Switch to Administration->Extensions->Service Plans->[The Report Module Plans] (1). The Screenshot is an Applications plan
  • Click Service Plans (2)
  • Select New Service Plan (3)
  • Enter the name of the Plan and click OK

  • Specify the data source for the plan (1).
  • Enter the evaluation rule plan (2).
  • Ensure the Publisher rule (3) is set to:
  • Event - On Publish
  • Action - Run PowerShell
  • Target - DataminingToExcel

  • Scroll down to the bottom of the screen to the Service Plan Misc panel
  • Ensure the variable fields are populated as required
  • Specify when the Plan is to run
  • Click Save at the top to commit the changes

A user can either save or email the report.


Variable NameVariable ValueDescription
DMReportID23The datamining report id created earlier.
ExcelTemplateNameReportTemplate.xlsxThe name of the excel template saved to project resources.
InjectToTabNameDataThe name of the tab created within the template.
CustomEmailSubjectDatamining Report - All App StatusA custom email subject.
CustomEmailText1This is the custom Email Text Relating to this reportCustom Email text 1
CustomEmailText2This is additional custom Email textCustom Email text 2
SaveLocation\\ServerName\ReportsOptional. Save location where you need the report saved on a share. Not needed if you are only emailing the report.; ; delimiter to send to multiple email addresses.

Please note, if you require another report to be emailed or saved, you'll only have to repeat Steps 1,3, 4 & 5.

Testing the Email

  • In the ESM Plan click Reset, Eval, Schedule and Publish
  • Check Email Queue, a new email should be visible
  • This email, with the attached DM report, should be delivered within a few minutes

Further Support

If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.