TABLE OF CONTENTS

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.



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:


  • Report Tiers and
  • Tools


Report Tiers

UI ElementDescription
Add Reporting TierTiers are linked to the parent report module. Additional tiers allow information from other modules to be added to a datamining report. For example a User Migration report and adding in the User's Apps and Devices.
Add Readiness TierThe Readiness tier uses the process Status of linked items to calculate the readiness of the parent items in the datamining reports. For example, a Datamining report of Users can have an Application Readiness tier added to see the Users 'readiness' in relation to their Applications. Multiple readiness tiers can be added to the datamining report to get an aggregate readiness over multiple modules. For example, a User datamining report can have Users, Applications and Device readiness tiers added to get a complete picture of readiness for the users.
Add Blueprint ColumnsThese are the Blueprint columns added to the grid. It will display the blueprint the parent report module is in.
Add Expression ColumnsThese are advanced column, that can be added to the grid. These are powerful and allows you to build complex expressions using a simple Domain Specific Language, reminiscent of formulas in many popular spreadsheet applications.
Column Layout, Filter, SortThis provides the ability to customise the report column layout, change the Display default column name,  filter and sort the report.


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


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 Users 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 1

  "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.  


Example 2

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 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 3

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')



Expression Data Types


Data TypeUse
StringText Operations i.e.   "AAAAA + BBBBBB"  or  "AAAA  Like  AA*"
DateTimeOnly used to display a calculated date based on today + or - X. 
BooleanFalse \ True   represented as a 0 or a 1
Int64Whole number integers only, will not return any decimal places and will round. i,e, 99.3% = 99%
DecimalWill return decimal places i.e. 99.33333% = 99.33333%


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.




Example 4

Produce a report to show all the users that are been migrated in the next 2 days in the Bedford deployment unit..

  • 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 with the default fields.
  • Click the Build report tab and click Add Expression Columns.
  • Specify a name for the Column.
  • Select String as the data type.
  • Click Add a column expression and select the Migration Slot Start column.
  • Ensure the following expression is entered.


UserMigrations_DeployUnitSlotStart > '[Today + 2]' And 

UserMigrations_DeployUnitSlotStart < '[Today + 3]'


  • The Expression will do a comparison of the Start date in that column and return a 'True' value if the start date is in 2 days time.
  • Click Run Datamining Report.
  • The resulting report will create a new column with the results of the comparison.





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 visualisations purposes. Here are the rules available:


==Determines if the column's text is equal to the specified value.
<>Determines if the column's text is not equal to the specified value.
>=  or  <=Greater than or equals to.  And less than or equals too
&&  'And' e.g. RAM >= 2 && RAM <= 8  
||'Or' e.g. RAM >= 2 || CPU >= 2.4
 == DBNull.ValueUsed to check if a value is blank. e.g. Device_RAM == DBNull.Value
StartsWithDetermines whether the beginning of this column's text matches the specified text.
EndsWithDetermines whether the end of this column's text matches the specified text.
ContainsReturns a value indicating whether the column's text occurs within this text.


Usage

A report creator may want to visually improve a report by highlighting all users whose First Name begins with 'Ruby'. 


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 colour it Green and if it is less than 2 colour it Red.

  • Click the Apply colour to columns and select the appropriate column(s) to apply this rule to.

  • Click Add Color Condition and add a seprate condition for Green and Red rules.

  • Click Run Datamining Report. The report should now highlight any users whose first name begins with 'Ruby'.


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. When it comes to report customizations, the Datamining Report column layout allows a user to customize 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 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.



UI ElementsDescription
Display Name (1)Edit this field to change the name of the column displayed.
Group Name (2)Edit this field to change the Headers.
Sort Order (3)Used to sort the corresponding column in Ascending order.
Sort Desc (4)Used to sort the corresponding column in Descending order.
Filter And/Or (5)Used to filter the corresponding column.
Filter Expressions (6)Used to filter the corresponding column.
Remove (7)Used to remove the corresponding column from the report.
Apply Changes (8)Used to apply the changes to the report.
Reset ChangesUsed to reset the report back to its default layout.
CancelUsed to cancel the changes.




Tools


UI ElementDescription
Expand All TiersClick this option to Expands the reporting tiers.
Collapse All TiersClick this option to collapse the reporting tiers.
Pivot as TableThis is used to summarise, sort, reorganise, group, count, total or average of the data on the grid.
Pivot as ChartThis is a visual representation of the pivot table.
Deduplicate RowsRemoves duplicate rows.
Internal Column NamesDisplays the internal column names of the fields.
Show Hidden FieldsDisplays all Hidden fields available within the modules selected.
Show History FieldsDisplays the History fields.
Open Guided TourProvides a walkthrough of the DMR.
HelpProvides access to the Online help article.



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 options available vary depending on the module, the data source tab generally consists of:


UI ElementDescription
All Applications (All Items)Clicking on this option will include all items as the data source.
Explicit Applications (Explicit Items)This option will only include the selected item(s) as the data source.
Rationalisation StatusProvides the ability to manage a report based on the rationalisation status of the Applications.
Select ProcessesIt enables the user to select based on the processes the item is in.
Select Processes StatusIt enables the user to select based on the processes status of the item.
Select BlueprintsOpens the Add Blueprints dialogue box.  It enables the user to select and exclude multiple blueprints.
Select Deployment UnitsOpens the Add Deployment Unit dialogue box. It enables the user to select and exclude multiple deployment units.
Include ArchivedInclude Archived items from the data source.
Include DeletedInclude Deleted items.


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. 



SyntaxDescription
Text (String)
UserMigrations_LastName like 'Gordon*'
UserMigrations_LastName Like '*Gordon*'
UserMigrations_LastName Like '*Gordon'
Display all Users on the grid with last name 'Gordon'.

UserMigrations_SamAccount Like '*amy.e.myers*' OR

UserMigrations_LastName Like 'Gordon*'


Display all users with a Lastname 'Gordon' as well as Sam account 'amy.e.myers'.
UserMigrations_LastName Not Like '*burton'
UserMigrations_LastName Not Like '*burton*'
UserMigrations_LastName Not Like '*burton'
Display all users on the grid that their last name is not 'Burton'.
UserMigrations_EmployeeId = ''Display all the Users with an Employee id that is Empty on the grid. An empty string is a string instance of zero length.
UserMigrations_EmployeeId <> ''Displays all the Users with an Employee id that is Not Empty and Not Null. i.e. return all employee ids with a value.
UserMigrations_EmployeeId Is NullDisplay all Users that their Employee id is a Null value. A null value string has no value at all.
UserMigrations_EmployeeId Is Not NullDisplay all Users that their Employee id is not a Null value.
Numbers
UserMigrations_MigrationId = '29715'Display the User with Migration id '29715'.

UserMigrations_MigrationId = '29715' OR

UserMigrations_MigrationId = '29716'


Display the Users with Migration ids '29715' as well as '29716'.
UserMigrations_MigrationId <> '29715'Display the Users with Migration ids greater or less than '29715'.
Boolean
UserMigrations_IsArchived = TrueDisplay all Users that have been archived. i.e. archived status set to True.
UserMigrations_IsArchived = FalseDisplay all Users that are un-archived. i.e. archived status set to False.




Sort Expression

SyntaxDescription
UserMigrations_FirstName DESCDisplay the User's FirstName in descending order
UserMigrations_Process ASCDisplay the Process column in Ascending order.


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 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.




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 this linking or retrieve more info about the links. The options available are:


UI ElementDescription
Add Link IdAdds the internal Link Id field as a column.
Add App StatusIncludes the 'App Status' field as a column. For example Accepted, Pending, Rationalised, Rejected.
Add Pending AppsIncludes the Pending Applications in the list of Application results.
Add Status InfoAdds the link status info. This could be 'Rejected'/'Linked'.
Add Device AffinityIncludes the device affinity field. The affinity name: Primary, Secondary, Tertiary
Add Last Used DateIncludes the Last Used Date field. The date, the item was last reported as being used from either SCCM, Snow, SysTrack etc. 
Add Last Modified ByIncludes the Last Modified by information.
Add Archived or DeletedIncludes Archived or Deleted field.



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 ElementDescription
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

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.



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.