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. 

Please refer to this article on How to create a favorite 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 on 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/yyy)
    • Set sort order on multiple Columns
    • Add filters to Columns



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 Time in Process

Time in process helps to calculate the total duration an item spends in a specific or entire process workflow. For example, track the number of days/hours/minutes an application was in a specific process workflow. Please refer to this article on more info about the Time in process tier.


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



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


Example 2 - 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 3 - 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')




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 4 - 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 5 - Date Range

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.


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

UserMigrations_DeployUnitSlotStart > '[Today]' AND

UserMigrations_DeployUnitSlotStart < '[Today +1]'



Example 6 - 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) 





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



ColumnFormatOutput
DateMMMM dd-yyyy (dddd)August 28, 2022 (Friday)
DateMMMM ddJan 02
DateMMMM dd-yyyyAugust 24-2022
Numbers"EUR"#.00EUR1012.00
Numbers#%45%
NumbersN010,985


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

Please refer to this article on How to.


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.




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.


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



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