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.
Sorting
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:
- Copy MicrosoftRSChart.dll and MicrosoftRSChartDesigner.dll from SSRS bin directory to Visual Studio private assemblies directory on your development machine.
- Update the Report Designer config file on your workstation
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
<Configuration>
<Extensions>
...
...
<ReportItemDesigner>
<ReportItem Name="EnterpriseManagementChartControl"
Type="Dundas.ReportingServices.DundasChartDesigner,
MicrosoftRSChartDesigner" />
</ReportItemDesigner>
<ReportItems>
<ReportItem Name="EnterpriseManagementChartControl"
Type="Dundas.ReportingServices.DundasChart,
MicrosoftRSChart" />
</ReportItems>
<ReportItemConverter>
<Converter Source="Chart" Target="EnterpriseManagementChartControl"
Type="Dundas.ReportingServices.RSChartConverter,
MicrosoftDundasRSChartDesigner" />
</ReportItemConverter>
</Extensions>
</Configuration>
No comments:
Post a Comment