Wednesday, August 6, 2008

OpsMgr 2007 Reports - Sorting, Filtering, Charting

This post provides information on sorting, filtering and adding reporting controls to custom Operations Manager 2007 reports. This is the fourth in a series of posts on creating customised Operations Manager reports; see the posts 'OpsMgr 2007 Customized Reporting - SQL Queries', 'OpsMgr 2007 SSRS Reports using SQL 2005 XML', and 'Passing Parameters between OpsMgr and SSRS'.

Data filtering and sorting

When creating tables and graphs in a report, the data returned in the SQL result set displayed in the table can be both filtered and sorted. Depending on the type of report, it may be more practical or flexible to sort and filter the data within the table.

The filters can be based on expressions, retrieving input from single or multi-valued controls to filter the data. When filtering based on a multi-valued text-box, the ‘in’ operator automatically filters based on each parameter, but unfortunately there is no ‘not in’ operator.


Using the example of reporting current disk free space, a default sort of the report table could be:

‘=Fields!Path.Value’ and ‘=Fields!Instancename.Value’ in ascending order.

The choice of whether to sort and filter within the report or the stored procedures is left to the author, typically based on which process is easier to follow – updating a SQL stored procedure or updating an SSRS report and the associated source Management Pack XML. Another consideration is the ability to interactively filter and sort data in a report, as opposed to the static data returned from the SQL query.

Filtering columns

Columns in a result table can be filtered based on an expression. In the example of reporting the current free disk space, this value is returned in bytes and another field could be added to the table to show the value calculated as a number of gigabytes. However, if you’re using this as a generic report, you may not be returning a number, and you would want to hide the gigabytes field.

Filters can be set on a detail body field, a column in a table, or the whole table itself. The following expression could be set on a row in a table, to determine whether the row is hidden or not – based on the rule GUID being reported matching the default free space GUID from the default dataset query. This would be set in the Visibility Hidden property of a table row:

=UCase(Parameters!RuleInstance.Value) <> UCase(First(Fields!RuleGuid.Value, "DefaultLogicalDiskFreeMegabytes"))

Adding the Microsoft Chart Control DLLs

To add the ability to create charts using the Reporting Services Chart controls on a development workstation, the following must be done:

  1. Copy MicrosoftRSChart.dll and MicrosoftRSChartDesigner.dll from SSRS bin directory to Visual Studio private assemblies directory on your development machine.
  2. Update the Report Designer config file on your workstation

This was taken from the report authoring guide, see ‘Enabling the EnterpriseManagementChartControl’ in the references section. Copying the chart control files The location may vary, but for a typical installation, the files are in: \\ssrs_server\c$\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin On your local workstation, the files need to be copied to the Visual Studio private assemblies directory, typically: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies Note that the source also contains the Dundas web chart control, also referenced in the report guide, but not used in this post.

Directory of \\ssrs_server\c$\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin

10/02/2007 05:15 AM 755,056 DundasWebChart.dll
16/02/2008 10:18 AM 1,549,360 MicrosoftRSChart.dll
16/02/2008 10:19 AM 9,884,720 MicrosoftRSChartDesigner.dll

Updating the report designer config In RSReportDesigner.config file in the Visual Studio private assemblies directory (C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies) and add the following elements, and then restart Visual Studio

            <ReportItem Name="EnterpriseManagementChartControl"
                              MicrosoftRSChartDesigner" />
            <ReportItem Name="EnterpriseManagementChartControl"
                              MicrosoftRSChart" />
            <Converter Source="Chart" Target="EnterpriseManagementChartControl"
                              MicrosoftDundasRSChartDesigner" />


Operations Manager Report Authoring Guide

Microsoft Operations Manager 2007 Management Pack Authoring Guide

Introduction to the Operations Manager 2007 Design Guide

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

No comments:

All Posts

printQueue AD objects for 2003 ClusterVirtualCenter Physical to VirtualVirtual 2003 MSCS Cluster in ESX VI3
Finding duplicate DNS recordsCommand-line automation – Echo and macrosCommand-line automation – set
Command-line automation - errorlevels and ifCommand-line automation - find and findstrBuilding blocks of command-line automation - FOR
Useful PowerShell command-line operationsMSCS 2003 Cluster Virtual Server ComponentsServer-side process for simple file access
OpsMgr 2007 performance script - VMware datastores...Enumerating URLs in Internet ExplorerNTLM Trusts between 2003 and NT4
2003 Servers with Hibernation enabledReading Shortcuts with PowerShell and VBSModifying DLL Resources
Automatically mapping printersSimple string encryption with PowerShellUseful NTFS and security command-line operations
Useful Windows Printer command-line operationsUseful Windows MSCS Cluster command-line operation...Useful VMware ESX and VC command-line operations
Useful general command-line operationsUseful DNS, DHCP and WINS command-line operationsUseful Active Directory command-line operations
Useful command-linesCreating secedit templates with PowerShellFixing Permissions with NTFS intra-volume moves
Converting filetime with vbs and PowerShellDifference between bat and cmdReplica Domain for Authentication
Troubleshooting Windows PrintingRenaming a user account in ADOpsMgr 2007 Reports - Sorting, Filtering, Charting...
WMIC XSL CSV output formattingEnumerating File Server ResourcesWMIC Custom Alias and Format
AD site discoveryPassing Parameters between OpsMgr and SSRSAnalyzing Windows Kernel Dumps
Process list with command-line argumentsOpsMgr 2007 Customized Reporting - SQL QueriesPreventing accidental NTFS data moves
FSRM and NTFS Quotas in 2003 R2PowerShell Deleting NTFS Alternate Data StreamsNTFS links - reparse, symbolic, hard, junction
IE Warnings when files are executedPowerShell Low-level keyboard hookCross-forest authentication and GP processing
Deleting Invalid SMS 2003 Distribution PointsCross-forest authentication and site synchronizati...Determining AD attribute replication
AD Security vs Distribution GroupsTroubleshooting cross-forest trust secure channels...RIS cross-domain access
Large SMS Web Reports return Error 500Troubleshooting SMS 2003 MP and SLPRemotely determine physical memory
VMware SDK with PowershellSpinning Excel Pie ChartPoke-Info PowerShell script
Reading web content with PowerShellAutomated Cluster File Security and PurgingManaging printers at the command-line
File System Filters and minifiltersOpsMgr 2007 SSRS Reports using SQL 2005 XMLAccess Based Enumeration in 2003 and MSCS
Find VM snapshots in ESX/VCComparing MSCS/VMware/DFS File & PrintModifying Exchange mailbox permissions
Nested 'for /f' catch-allPowerShell FindFirstFileW bypassing MAX_PATHRunning PowerSell Scripts from ASP.Net
Binary <-> Hex String files with PowershellOpsMgr 2007 Current Performance InstancesImpersonating a user without passwords
Running a process in the secure winlogon desktopShadow an XP Terminal Services sessionFind where a user is logged on from
Active Directory _msdcs DNS zonesUnlocking XP/2003 without passwords2003 Cluster-enabled scheduled tasks
Purging aged files from the filesystemFinding customised ADM templates in ADDomain local security groups for cross-forest secu...
Account Management eventlog auditingVMware cluster/Virtual Center StatisticsRunning scheduled tasks as a non-administrator
Audit Windows 2003 print server usageActive Directory DiagnosticsViewing NTFS information with nfi and diskedit
Performance Tuning for 2003 File ServersChecking ESX/VC VMs for snapshotsShowing non-persistent devices in device manager
Implementing an MSCS 2003 server clusterFinding users on a subnetWMI filter for subnet filtered Group Policy
Testing DNS records for scavengingRefreshing Computer Account AD Group MembershipTesting Network Ports from Windows
Using Recovery Console with RISPAE Boot.ini Switch for DEP or 4GB+ memoryUsing 32-bit COM objects on x64 platforms
Active Directory Organizational Unit (OU) DesignTroubleshooting computer accounts in an Active Dir...260+ character MAX_PATH limitations in filenames
Create or modify a security template for NTFS perm...Find where a user is connecting from through WMISDDL syntax in secedit security templates

About Me

I’ve worked in IT for over 20 years, and I know just about enough to realise that I don’t know very much.