TABLE OF CONTENTS

Introduction


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 a 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 Email Template


Before we can send and email we need to create an Email Template and as these reports can come from any module.


  • Switch to Administration->Email Templates (1).
  • Click Click here to add new item (2).
  • Enter the following under the headings (3)
  • Email Name:DMReportEmailSend.
  • Ensure Is enabled is ticked.
  • Subject:[CustomEmailSubject].
  • Edit the email by clicking the small scroll icon and add the following two lines only (4)
  • [CustomEmailText1]
  • [CustomEmailText2]
  • Click OK (5).
  • Click Save Changes (6).
  • Make a note of the Email template Id (7).




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 PowerShell


Now we need to add the PowerShell.

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



  • Enter the following within the specified columns


ColumnsDetails
Script NameDataminingToExcel
EnabledTicked
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

Do{
$VarName = $config.misc.UserVars.variable[$i]
$VarValue = $config.misc.UserVars.Value[$i]
$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}

$DS = New-MSDataminingDataSource -Ids $scriptArgs.Items

$RptInfo = Get-MSDataminingReportById -ReportId $DMReportID -HeaderFormat DisplayName -DataSource $DS

$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 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 - Setting 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\ReportsSave location where you need the report saved on a share.
EmailToDave@Migrationstudio.com;ServiceDesk@Migrationstudio.com ; 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, 4 & 5.



Further Support

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