TABLE OF CONTENTS

• Introduction
• Accessing the Datamining Report (DMR)
• Datamining Report Layout
  • Top Navigation Ribbon
    • Build Report
      • Report Tiers
• Custom Columns
  • Expression Columns
    • Example - Joining Fields
  • Expression Tools
  • Expression Examples
    • Example - Comparing Fields
    • Example - Formatting Dates
    • Example - IIF Statement (True / False)
    • Example - Html Buttons
    • Example - Date Range
    • Example - Separate word in a Column
    • Example - Applying Specific Formats to Numbers & Dates in a Report
    • Example - Adding Buttons to a Web Datamining Report via an Expression column
    • Expression Data Types
  • Colour Expression Columns
    • Colour Express Rules
    • Example
      • Modifying the Column Layout for a Report
      • Tools
    • Data Source
    • Adv. Filter & Sort Expression
    • Filter Expression Syntax
    • Sort Expression
    • Use Case
      • Remove Columns
    • Adv. Link Options
  • Report Builder Selector
  • Control Bar
  • Reporting Grid
  • Additional Reporting Tiers
  • Interaction Between Reporting Tiers and Readiness Tiers
    • What happens if the two are combined?
  • Datamining Output Examples
    • Example 1
    • Example 2
• Further Support

Introduction

The Datamining Report (DMR) is a reporting module in ManagementStudio used to extract usable data from a larger set of raw data. Businesses can learn more about their Users, Applications, Devices, Deployment Units, etc, to develop more effective strategies related to various business functions and in turn leverage resources in a more optimal and insightful manner. This helps businesses make better decisions. A user can save the information as a report, display as a dashboard, or export and share it. You can configure each ad hoc view and its corresponding report by selecting the data that you want to include.

The Datamining Report function is available within the following modules: Applications, User Migrations, Devices, Mailboxes, Deployment Units, Defects and Bespoke. The example that follows will use the Application module to illustrate the concept although this approach applies to all modules within ManagementStudio. 

Please refer to this article on How to create a favourite datamining report.

Accessing the Datamining Report (DMR)

To view the Datamining Report:

• Choose the relevant module from the vertical menu bar on the left (1), in the example, Applications.
• Click Datamining Report (2) from the ribbon at the top.

Datamining Report Layout

Although the look of the page and the options available will vary between modules, the layout consists of four core areas:

1. Top Navigation Ribbon
2. Report Builder Selector
3. Control Bar
4. Reporting grid

Top Navigation Ribbon

This is the menu users will use to navigate to build a report, select the data source and manipulate the report. The top navigation menu has four tabbed elements. These are:

• Build Report
• Data Source
• Adv. Filter & Sort
• Adv. Link Options

Build Report

This tabbed menu provides the ability to build the desired report required. It is broken down into two segments:

• Add Reporting Tier
  • Adds basic tiers such as Users, Apps, Surveys, Attachments etc
• Add Readiness Tier
  • Add a special tier to calculate readiness vs Apps, Users, Devices
• Add Time in Process
  • Time in process helps to calculate the total duration an item spends in a specific or entire process workflow. Please refer to this article for more info about the Time in-process tier. 

• Add Blueprint Columns
  • Add Blueprints to the report
  • Allows multiple columns of Blueprints to be added
  • Allows specific branches of the Blueprint Tree to be added
• Add Expression Columns
  • Column Expressions allow composite columns of data from other columns to be added
  • Web Buttons create html buttons that can be clicked when used in the DMR's web view
  • Colour Columns allow conditional logic to be used to colour in cells (e.g. Red if Ready < 10%)
• Column Layout, Filter, Sort
  • Rename Columns
  • Reorder Columns
  • Set Date / Number Format (e.g. dd/MM/yyyy)
  • Set sort order on multiple Columns
  • Add filters to Columns

Report Tiers

Each Readiness Tier adds six columns to the report, plus one additional column for the overall readiness across all tiers. Columns are: Total, item_ReadyCount, item_RemainingCount, item_ReadyPercent, IsReady, item_OutOfScopeCount and OverallReadiness

Custom Columns

Expression Columns

Expression Columns are advanced columns that can be added to the grid. It enables report creators to add custom-constructed formulas by combining, manipulating or calculating data from other columns on the report. For example, to create a field to include the User's proper name, the expression would combine FirstName LastName whilst adding a space between the fields. For further information about Expression columns, please refer to this Microsoft article.  

Example - Joining Fields

  "First Name" + ' ' +     "Last_Name" =     "FirstName LastName" 

            Dave       {Space}        Smith                     Dave Smith

The screenshot above shows the necessary expression statement to calculate the column Full Name (1) and is an example of an action performed on text i.e. a String.

The Data Type refers to the type of data that will be in the calculated column, NOT the data type you are performing the test on.  

Expression:

Contacts_FirstName + ' ' +

Contacts_LastName + ' [' +

Contacts_SamAccount + '] ' 

Result:

Expression Tools

Expression Examples

Example - Comparing Fields

Comparing a string with some known text will return a true-false, not the string itself. The following example be compare a string with a known text (Outlook) from the App Name column. 

• Click Add Expression Columns.
• Enter the name of the column.
• Select String as the Data type.
• Click the Add a column to the expression dropdown and select the column to perform the comparison. In the screenshot below, AppName [Applications_AppName] is selected.
• Enter Applications_AppName Like 'Outlook' (1).
• Click Run Datamining Report.
• The results displayed will show an additional new column with the values 'True' or 'False' (2).

Example - Formatting Dates

1. The Name of the Column to display in the report.

2. Format the date in 'Applications_CreatedOn' as dd/MM/yy e.g. 10/03/24

Note, formating dates is a stand along expression and can't be used in conjunction with multiple expressions in the same column.

Example - IIF Statement (True / False)

In this example, we'll be using the IIF() function. Return Yes if the condition is TRUE, or NO if the condition is FALSE

We will be returning Yes  or No if the Last name of the User is Gordon.

IIF (UserMigrations_LastName like 'Gordon*', 'Yes' , 'No')

The Nested IIF Statement can be used to specify multiple conditions.

IIF (Applications_Process In('4. QA', '5. UAT'), 'Rationalised', IIF(Applications_Process In('8. Retired', '3. Packaging'), 'In Testing', 'Tested'))

Results: If Application process is '4. QA' or '5. UAT', return 'Rationalised', if the Application process is '8. Retired', or '3. Packaging' return 'In Testing'. Otherwise, return 'Tested'.

Another IIF nesting example:

IIF (Blueprint_BlueprintColumn like '*americas*', 'AMER', IIF(Blueprint_BlueprintColumn like '*europe*', 'EMEA', 'APAC'))

Here the Blueprint column contains three regions. The nested IIF statement is shown in red. The Expression Column will simply display AMER, EMEA or APAC.

Idea - Think about using expression columns look at some chrs within a string. i.e. return  1st 5 chrs of a hostname - SUBSTRING(Devices_HostName,1,5)

Example to check if survey email has been sent, and set column text to either "Sent" or "Not Sent":

IIF(SelfSchedule_RequestEmailSent Is Not Null, 'Sent', 'Not Sent')

Example to check if survey email has been sent in the last seven days, and set column text to either "Sent" or "Not Sent":

IIF(SelfSchedule_RequestEmailSent >= '[Today - 7]', 'Sent', 'Not Sent')

Example - Html Buttons

For a DMR that will be ultimately displayed on a web page, it is possible to craft HTML that will be rendered as buttons in the browser. Using other fields from the report parameters can be subbed into the url called by the button. 

Using Expression to create a Url column

'https://MyServerName.com/1/p/a/script/' + UserMigrations_MigrationId + '/66/?skey=Y52DURQWEJ&ikey=' + UserMigrations_RandomKey

Further using the Url Column from above to inject into an Html Button

'<a class="btn btn-info btn-sm" href="' + Expression_UnlockUrl + '" role="button" target="_blank">Unlock</a>'

Example - Date Range

Produce a report to show all the users from the Bedford Deployment Unit that are to be migrated in two days time.

• Switch the User Migration module.
• Click Datamining Report button from the ribbon.
• Click Data Source tab.
• Select Deployment Units and click Add Deployment Units, add the Bedford deployment unit.
• Within the User Migrating Reporting Tier, ensure the Migration Slot Start is selected in addition to the default fields.
• Click Run Datamining Report to add the new columns to the grid.
• Click the Build Report tab and click Add Expression Columns.
• Enter a name for the column in the Name field, such as Is In Scope.
• Select String as the data type.
• Click Add a column expression and choose the Migration Slot Start column.
• Ensure the following expression is entered.

UserMigrations_DeployUnitSlotStart > '[Today + 2]' And 

UserMigrations_DeployUnitSlotStart < '[Today + 3]'

• The expression will perform a comparison of the Start date in that column and return TRUE if the start date falls in two days time.
• Click Run Datamining Report to rebuild the report.
• The resulting report will create a new column with the results of the comparison.

The following expression will return all Users that have their migration slot scheduled for today.

UserMigrations_DeployUnitSlotStart > '[Today]' AND

UserMigrations_DeployUnitSlotStart < '[Today +1]'

Example - Separate word in a Column

In this example, we'll be using the SubString function to separate words in a column. 

Syntax - Substring(expression, start, Length) where 

• Expression is the source string for the substring
• Start - Integer that specifies where the substring starts
• Length - Integer that specifies the length of the string

SUBSTRING(Blueprint_BlueprintColumn, 19, 50) 

Note:-  It is possible to search for a specific character in a string and then use that result in the substring command.

• IndexOf            Finds the 1st instance of the specified chr in a string and returns an integer.
• LastIndexOf      Finds the 1st instance of the specified chr in a string and returns an integer. 

So   IndexOf(Blueprint_BlueprintColumn, \)          will find the Backslash

       LastIndexOf(Blueprint_BlueprintColumn, \)    will return the last position of the backslash

Example - Applying Specific Formats to Numbers & Dates in a Report

In this example, we will run a User Migrations report to include the User’s Migration Id, their deployment unit start date, their migration slot Start date & Migration slot end date.

In the screenshot, we have the DU start date, Migration slot start/End as dates & the migration Id column as Numbers.

Click on the Column Layout, Filter, Sort button to apply the specific format.

Within the Data Format column enter the following (1):

• Migration ID row - N0
• Migration Slot Start - MMM dd
• Migration Slot End - MMMM dd-yyyy

Click Apply Changes (2).

The screenshot below displays the corresponding output within the report.

Please refer to this article on more ways to format a numbers column https://docs.textcontrol.com/textcontrol/wpf/article.techarticle.numberformat.htm

Please refer to this article for more ways to format a date column: https://docs.devexpress.com/WPF/10408/common-concepts/formatting-values/format-specifiers

Example - Adding Buttons to a Web Datamining Report via an Expression column

Please refer to this article on How to.

Expression Data Types

Notes:

• The source data for the calculated field must be selected in the report settings.
• Comparisons will return Boolean checkboxes or String False\True.
• Aggregated data may not be calculated i.e. "Count" "Average" "Sum".

Dates benefit from having a keyword that can be used for comparison [Today +2] is 2 days from today.

It is also possible to do date comparisons that include the time by using the keyword TimeStamp
i.e. UserMigrations_DeployUnitSlotStart < '[TimeStamp]' will return True if a slot time has passed.

Colour Expression Columns

By colouring the items in a report, a user can add an extra dimension to the data displayed. For instance, the use of colours to identify Users in a specific Blueprint, or distinguish different deployment units. In the Datamining report, the Add Colour Column allows the report creator to customize the colouring of their report. 

Colour Express Rules

Rules can be very useful when setting up colour schemes for visualisation purposes. Here are the rules available:

Example

A report creator may want to visually improve a report by highlighting fields that don't meet a particular value. 

Steps
Click Add Colour Column from within the Custom column expressions
Create a colour rule whereby if the value of a cell is greater than 2 then set the background colour to green. If the value is less than 2, set the background colour to red.  The colours are specified using HTML HEX codes (as in the example) or HTML colour names. Click the Apply colour to columns dropdown and select the appropriate column(s) to apply this rule to. Click Add Color Condition and add two new conditions for green and red colours.
Click Run Datamining Report. The report should now display green or red colouring on the results grid.

More Colour Expression Examples:

• Expression_FullName == 'Henry Barnes'
• Expression_FullName.Contains('Rachel')
• Expression_FullName.EndsWith('Daniels')
• Expression_FullName <> 'Joe Cruz'

Modifying the Column Layout for a Report

By default, a report will have a built-in report layout although additional options are available in the Datamining Report to customise the report layout. A user can select different columns to display on the report, and you can rearrange the order in which the selected columns are listed. Columns appear on the report, from left to right, in the order in which they are listed within the reporting tier. 

To Modify the default column layout for a report: 

From the Datamining report screen, Click on Column Layout, Filter, Sort from the report tiers. The following window will be presented.

Drag the Rows up and down to change the order the Columns are displayed in the report.

Tools

Data Source

Before or during a data analysis process, the report creator may want to make changes to the report data source. This can be done using the data source tab. It is used for managing the information contained in the report. Although the look of the tab and the options available vary depending on the module, the data source tab generally consists of:

Adv. Filter & Sort Expression

There are two special functions called 'Filter Expression' and 'Sort Expression' (1). These are applied to the server-side results after the data mining report has been built. Both use pseudo-SQL as a way of building expressions. The filter works like the SQL 'Where' clause but without the 'Where' keyword and the sort works like an SQL 'Sort' without the 'Sort' keyword.  Use the prefixed version of the column names when building expressions. The filter and sorting can only be applied to columns that are in the resulting report. 

Filter Expression Syntax

An empty string is a string instance of zero length, whereas a null string has no value at all. An empty string is represented as ''. A null string is represented by null. 

Sort Expression

Use Case

A Project Admin might want to arrange a list of Users in alphabetical order or compile a list of Application versions from highest to lowest. Filtering & Sorting data helps you quickly visualise and understand your data better, organize and find the data that you want and ultimately make more effective decisions. 

The Filter Expression is applied after the report has been built, it can not be used to make a report run faster. To make a DM report run fast as possible, filtering should be done first. 

Example

A project Admin is required to produce an Applications report of their entire estate and then filter the report to only show the applications with App vendor names 'Adobe' & 'Lenovo' and sorted in by the Process in Ascending order.

• Switch to the Applications module.
• Click Datamining Report from the ribbon.
• Ensure the default fields are selected.
• Click Run Datamining Report.
• The resulting report will display all applications in the estate.
• Click Filter & Sort from the tabbed menu.
• Click Add Column within the Filter expression menu and select AppVendor [Applications_AppVendor].
• Click Add Column within the Filter expression menu and select AppVendor [Applications_AppVendor].
• Enter the following statement:

Applications_AppVendor Like 'Lenovo' OR
Applications_AppVendor Like 'Adobe'

• Click Add Column within the Sort Expression menu and select Process [Applications_Process].
• Enter the following statement to sort the process in Ascending order:
• 
  

Applications_Process ASC

If there is a requirement to sort the process in Descending order, enter the following statement:

Applications_Process DESC

• Click Run Datamining Report.
• The resulting report should display all Applications with vendor names Adobe & Lenovo and the process sorted in ascending order.

Remove Columns

Some report options such as Readiness Tiers and Link Options add fixed columns to the report table. It's possible to remove these columns from the results using the Remove Columns (2) option. 

It's important to note the columns are removed after the Filter and Sort expressions are run. This allows a user to, for example, add the IsArchived column then use it as a filter, and then remove the column without returning to the client.

Adv. Link Options

This section provides additional advanced link options. When two or more modules such as Applications, Users, or Devices are selected using additional tiers they will automatically be linked together into a single report by ManagementStudio. However, there are additional options that can be used to control  or retrieve more info about the links. The options available are:

Report Builder Selector

The Report Builder Selector allows the user to define the fields they want to be generated in the reports for them. Users select fields from a range of tables. The selection can also be saved for future use.

Control Bar

This section provides quick access to common features. 

From left to right:

UI Element	Description
Search Grid (1)	Allows you to enter a piece of text to search/filter the grid for, the grid will show which fields are matching in red. You can clear the search filter by clicking on the symbol to the right of the control that looks like a crossed circle:
Toggle Group Bar (2)	This allows you to show/hide the Group bar. The group bar allows you to drag any field in the grid to the group bar and the grid will be grouped on that field.
Select All (3)	Selects all the items in the Grid.
Select None (3)	Deselects all the items in the Grid.
Invert Selection (3)	This will invert the current selections in the grid.
Copy to Clipboard (4)	Provides the ability to copy items from the grid to the clipboard.
Export to Excel (All) (4)	Provides the ability to Export the Grids' contents to Excel. To export, see the exporting records article.
Export to CSV (All) (4)	Provides the ability to export the Grids' contents to CSV (Comma-Separated Values). To export, see the exporting records article.

Reporting Grid

Use the reports grid to review the fields and the rows of the data contained in the data source. The reports grid can be used to make general modifications to the data source like sorting, or hiding fields; renaming fields, or resetting field names; creating calculations; changing the column or adding aliases. The columns displayed in the section will depend on the fields selected within the report builder selector.

Additional Reporting Tiers

In addition to the top-level Datamining Reports, there are more reports available that can only be added via an additional tier. These reports can not be a top-level part of the DMR because they are linked to one or more top level module. The reports are:

• Attachments
• Surveys
• Test Sign-Offs
• Contacts
• Task
• Dependency
• Email Report
• Self-Schedule

Interaction Between Reporting Tiers and Readiness Tiers

When a Datamining report is run on a group of Users and their corresponding Application List, it creates a one-to-many result of one row per User-Application relationship. This means a 5 User report could have 50 rows of results. In contrast, when doing a User and their Applications Readiness it creates a one-to-one result of the Users and their aggregate Application readiness of all of their Apps. For example, a 5 User Application Readiness report will have only 5 rows in the results. 

An example of a User Application report.

An example of a User Application readiness report.

What happens if the two are combined?

When a Datamining Report is run that has the same tier (e.g. Applications) in both the Additional Tiers and Readiness Tiers, ManagementStudio will not return the aggregate Application readiness for the User but rather expand out the individual readiness per Application. This allows the Applications that are holding up a User to be seen on a User by Application basis on the grid.

An example.

Datamining Output Examples

Example 1

Before building a report, the creator should take a question, ask follow-up questions, write requirements, and then map those requirements to report criteria.

Take the story of Joe Blog, the PM at Blogs Enterprise. One day his CEO, Jonathan Blogger, came to him with questions about the number of Applications they had within their estate and specifically, he wanted to know the users who were using these applications. For this scenario, a report can provide the insight Jonathan needs to take action.

To create a basic Applications report:

• From the Applications Module, select a list of App(s) to report against and click Datamining Report.
• Within the Report Builder view, Choose the fields required in the report.
• Click Run Datamining Report.

By Default each Report module will have several fields selected.

• This will display the relevant information listed against the selected Applications on the grid.
• Next step,  is to establish the users who have been using these applications.
• Click Add Report Tier from the report tiers.
• Select User Migrations from the drop-down menu.
• Click Run Datamining Report.
• The resulting report displayed is the Application-Users report. An example of a cross-module report.
• At the top of the report, click the spanner symbol to either:
• Save Report.
• Save As.

Example 2

Joe Bloggs is a Project manager and needs to view the Applications readiness report of the users based in the Plymouth Office (Deployment unit). The following steps outlines the process in producing the required report.

• Switch to the Deployment Units module.
• Click on the Datamining Report button.
• Click Add Reporting Tier and select User Migrations.
• Click the Data Source tab.
• Click Select Deployment unit, Select SW-Plymouth from the Add Deployment unit dropdown menu.
• Click Run Datamining Report.
• The grid will display all the Users currently in the SW-Plymouth deployment unit.
• Next step is to w need to add the Applications readiness tier to the report.
• Click on Add Readiness Tier and select Applications.
• Click Run Datamining Report.
• The grid will display all the Users including their application readiness.

Please refer to this article on How to create a favorite datamining report

Further Support

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