Passing Parameters between OpsMgr and SSRS

This post provides information on passing parameters between Operations Manager 2007 and SQL Server Reporting Services (SSRS) when creating customised reports. It discusses Operations Manager Smart Controls, using dataset queries and populating parameters from a string.

This is the third in a series of posts on creating customised Operations Manager reports; see the posts 'OpsMgr 2007 Customized Reporting - SQL Queries' and 'OpsMgr 2007 SSRS Reports using SQL 2005 XML'.

Report parameters define input parameters to the report, which are then typically passed to dataset parameters, used to determine the data displayed in the report. The dataset parameters can be used in-line with text-based SQL queries, or passed to a SQL Stored Procedure.

Report Smart Controls

Operations Manager provides several controls tailored to OpsMgr specific reporting. For example:

• Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.MonitoringObjectXmlPicker
• Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.PerformanceRulePicker
• Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.TextBox

The controls are documented in the reporting guide and can be seen in use by unsealing the standard Microsoft Management Packs. Generally the controls relate to data stored in Operations Manager, such as the Monitoring Object or Performance Rule pickers, providing methods of selecting and passing existing OpsMgr data types as parameters.

The example below shows the definition of the control for a multi-value textbox, using the multiline property element to enable multiline support for the control. The idea of this control is to provide a text-string filter that would be passed as a parameter to SSRS to filter or determine the final output of a report.

<Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.TextBox" rowSpan="3" columnSpan="1">
  <ReportParameters>
    <ReportParameter name="InstanceFilter">
      <Prompt>Custom!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.InstanceFilter</Prompt>
    </ReportParameter>
  </ReportParameters>
  <Properties>
    <Property name="Multiline">
      <Value>True</Value>
    </Property>
  </Properties>
</Control>

Populating parameters from a string

Single and multi-valued strings can be used to provide default parameters to the report. Unfortunately, the multi-line support of this control does not map to a multi-value string input expected by SSRS, and only the first instance works. The correct method would be to use another control to select instances based on the object/rule and then pass this to the SQL query for filtering.

Populating parameters from a dataset query

Populating parameters from a dataset query is a flexible method of providing parameter defaults that can be designed to minimise changes in the future. For example, the default group target for a report can remain constant, with only the members of that group changing as reporting needs change. Rather than hard-coding a partulcar GUID, a lookup based on a well-known name can also save re-work.

For example, the following direct text SQL query returns the Rule GUID for the 'Logical Disk Free Megabytes' rule, which could be used to populate a default report parameter, providing a default when the report is opened:

SELECT vRule.RuleGuid from vRule
inner join vPerformanceRule ON vPerformanceRule.RuleRowID = vRule.RuleRowID
WHERE vRule.RuleDefaultName = 'Logical Disk Free Megabytes'

Another example is constructing XML to find the ManadedEntityRowID of a particular group, again useful for populating report parameter defaults:

SELECT '<Data><Objects><Object Use=''Containment''>' + Cast(ManagedEntityRowID as varchar) + '</Object></Objects></Data>' as XMLManagedEntity
FROM vManagedEntity WHERE ManagedEntityDefaultName = 'Custom Group Name'

Note the use of the double single-quotes, required to allow this text to be stored in an XML management pack definition while still resulting in well-formed XML.

References

OpsMgr 2007 Customized Reporting - SQL Queries
http://waynes-world-it.blogspot.com/2008/07/opsmgr-2007-customized-reporting-sql.html

OpsMgr 2007 SSRS Reports using SQL 2005 XML
http://waynes-world-it.blogspot.com/2008/05/opsmgr-2007-ssrs-reports-using-sql-2005.html



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