Export (0) Print
Expand All

Custom Report Parameters

Updated: June 20, 2011

The Transact-SQL queries described in Creating Custom Queries can retrieve a wide variety of data from the OperationsManagerDW database, but the reports are limited to a specific set of objects at a specific time. To offer flexibility in the data that is reported, you can use parameters to let users define the terms of the report.

When you create a report in Microsoft SQL Server Business Intelligence Development Studio (BIDS), a number of parameters are available to use in your reports.

In addition to BIDS, Operations Manager 2007 R2 includes the smart parameter block to enable you to easily offer controls for selecting parameters that are intuitive to report users, such as a date/time picker that can be limited to business hours that you define, or an object picker that makes it easy to select specific objects in Operations Manager.

To use the smart parameter block, you must first include the report in a management pack, and then import the management pack into Operations Manager, as described in Deploying Reports. After the report has been deployed, you can edit the parameter block with the Authoring console.

noteNote
The Authoring console is available as part of the System Center Operations Manager Resource Kit. For more information about the Authoring console, see the Introduction to the Authoring Console.

Using Parameters in BIDS

If you do not require the enhanced controls in the smart parameter block, you can create parameters for your report by using the parameter tools in Business Intelligence Development Studio.

To add parameters in BIDS

  1. Open the report in BIDS.

  2. BIDS automatically adds parameters to your report based on the parameters that are used in the query. To edit the query, right-click the dataset for your report (by default, DataSet1) and select Query. The Query Designer opens.

  3. In the query, edit the FROM clause to include the parameters that you want the user to select in the report. Preface each parameter with an @ symbol. The following code is an example of a query with hard-coded dates.

    vPerf.DateTime >= '2010-06-28 15:19:59.387' 
    and vPerf.DateTime < '2010-07-10 15:19:59.387'
    

    If you replace those hard-coded dates with parameters, the query resembles the following code.

    vPerf.DateTime >= @StartDate 
    and vPerf.DateTime < @EndDate
    
  4. Click the ! button to run the query. The Query Designer opens a Define Query Parameters window. You do not have to enter values for the parameters at this time, unless you want to test the query. Click OK to close the Define Query Parameters window, and click OK to close the Query Designer.

  5. BIDS has automatically created parameters as part of the report. However, you must define the data type of the parameters so that BIDS can provide the correct controls in the report. In the Report Data pane, expand Parameters. The parameters that you added to your query appear. Double-click one of the parameters, for example, @StartDate, to open the Report Parameter Properties dialog box for that parameter. On the General tab of this dialog box, you can set basic information about this parameter. Most of the settings are optional, but name and data type are required.

    • Name: The name of the parameter that you entered in the query. This field will be populated for you.

    • Prompt: The text that will be displayed in the report next to the control that sets this parameter. Enter whatever prompt makes sense for your report users, for example “Start date” instead of “StartDate”.

    • Data type: Use the drop-down list to select the type of data that this parameter represents.

      • Text: For plain text data. This type is the default, and the report generates a standard text entry field for this parameter.

      • Boolean: For true/false values. The report generates a check box for this parameter.

      • Date/Time: For date and time values. The report generates a text field with a calendar control for this parameter.

      • Integer: For whole numbers. The report generates a text field for this parameter.

      • Float: For decimal numbers. The report generates a text field for this parameter.

    • The next three check boxes let you select whether blank values are permitted, or null values, or multiple values. Selecting Allow multiple values causes the report to render the control for this parameter as a drop-down list.

    • Select the visibility of the parameters (Visible, Hidden, or Internal). Most of the time, you should select Visible, so the report users can use this control. You might select Hidden or Internal if you intend to calculate the value of the parameter from other data, but do not want the report user to have direct access.

  6. Select the Available Values tab if you want to restrict the values that the report user can enter. For more information, see Creating Report Parameters and Setting Report Parameter Properties (http://go.microsoft.com/fwlink/?LinkId=220277).

  7. Select the Default Values tab if you want to prepopulate this control with default data. For more information, see Creating Report Parameters and Setting Report Parameter Properties (http://go.microsoft.com/fwlink/?LinkId=220277).

  8. Select the Advanced tab to specify whether the report immediately refreshes when the value of the control changes. By default, the report automatically determines when to refresh.

  9. Click OK when you have finished setting the properties for this parameter. Set the properties for any other parameters used in this report.

  10. Run the report by selecting the Preview tab in BIDS. The controls that you configured appear at the top of the report. You can enter data for these parameters, and click View Report to see a preview of the results.

  11. Save your report when you are finished.

Adding Parameters in the Smart Parameter Block

The smart parameter block lets you use several tools that are not available through BIDS, such as the relative date-time picker. To use the smart parameter block, you must use the Authoring console, and also edit the XML of your management pack directly. For information about the controls that are available, see Report Controls. However, linked reports involve creating a report from an existing base report. Custom reports have no base report and therefore require more code for the smart parameter block to work.

To add parameters in the smart parameter block

  1. Open the management pack in the Authoring console.

  2. Click the Reporting tab, and then click Reports in the Reporting pane. The reports that are part of the management pack appear in the results pane.

  3. Right-click the report that you want to edit, and then select Properties. The Properties window for the report appears.

  4. Click the Parameter Block tab. The Parameter Block field is most likely empty. Click Edit in in external editor. The button starts the external editor, and opens the .rpdl file that is a part of the management pack.

  5. Decide on the parameters that you want for this report. For information about which parameters are available, see Report Parameters.

  6. Locate the XML code for the control that you want to use in Report Controls and its subsections. Copy that XML into the external editor. Make a note of the ReportParameter name element for the control that you use, because you have to copy it to the query in the report definition.

  7. Add a Controls tag to enclose the Control tag that you just added, as the following example shows.

    <Controls>
       <Control…>
          …
       </Control>
    </Controls>
    
    
  8. Add a ParameterBlock tag to enclose the Controls tag. The columns property of this tag defines the width of the parameter block in the report. The xmlns property of this tag defines the namespace for this element, and must be set to http://schemas.microsoft.com/mom/reporting/2007/ReportParameterSettings. The XML for the parameter block should now look like this:

    <ParameterBlock columns="6" xmlns="http://schemas.microsoft.com/mom/reporting/2007/ReportParameterSettings">
       <Controls>
          <Control…>
             …
          </Control>
       </Controls>
    </ParameterBlock>
    
  9. Save the file, and then close the external editor. The XML you just entered appears on the Properties window.

  10. You now must edit the query in the report definition. Click the Definition tab to see the .rpdl file for the report. You can edit the query directly on this window, or click Edit in external editor to edit the file. However, editing the report in BIDS instead of the external editor makes the next step easier.

    To edit the query in BIDS

    1. Open the report project that contains the report that you want to edit.

    2. In the Report Data pane, right-click the dataset to select Query to open the Query Designer window.

    3. The control you want to use dictates how the parameter will be used in the query, but the parameter will always be used in the WHERE clause. In the editor, delete the value that you want to replace with the parameter (if any), and insert the ReportParameter name associated with the control from the .rpdl file. If the parameter can have a single value, preface the name with an @ character. You must use the exact report parameter name, or the query cannot associate the query parameter with the control name in the parameter block.

    4. Click OK to save the revised query, and then save the report in BIDS.

    5. You now must add the revised report definition to the management pack. In the Authoring console, open the Properties window for the report if it is not already open, click the Definition tab, click Load content from file, and then select the .rdl file that you just saved in BIDS. The code now appears in the RDL field.

    6. Delete the line that begins:

      <?xml version
      
  11. You now must add a ReportParameters element to the report definition. This is separate from the ReportParameters section for the parameter block. In the Properties window for the report, click the Definition tab, and then click the Edit in External Editor button. The report opens in the XML editor of your choice. Following the closing /DataSources tag in the report, add the following code:

    <ReportParameters>
       <ReportParameter Name=" ">
          <DataType></DataType>
          <Prompt></Prompt>
       </ReportParameter>
    </ReportParameters>
    

    The code used in the ReportParameters section depends on the control you choose. Open the .xml file for the Microsoft Generic Report Library and search for a report that uses the control that you want to use. Copy the report parameters section for that report from the Generic Report Library and paste it into your report.

    If you edited the query in BIDS, and a ReportParamters section already exists in the report, delete it, and then replace it with the ReportParameters section you selected from the Generic Report Library.

  12. You must now add the query parameters to the DataSet for this report. Following the /CommandText tag that contains the query for the report, add the following code:

    <QueryParameters>
       <QueryParameter Name="">
          <Value></Value>
       </QueryParameter>
    </QueryParameters>
    

    You must define a QueryParameter section for each parameter used in the query. If you edited the query in BIDS, and the QueryParameter section already exists, you only have to replace the Value elements. In the Name property, provide the name of the parameter used in the query. In the Value element, you must provide the appropriate function to define the value for this parameter. The function differs depending on the value you want to obtain; each function is defined in the code block that you will add next.

  13. The smart parameter block requires additional code to process the report parameters. After the closing /DataSets tag, add the following Code element, and insert the following code:

    <Code>
    Const TimeZoneParameterName As String = "TimeZone"
    Const SD_BaseTypeParameterName As String = "StartDate_BaseType"
    Const SD_BaseValueParameterName As String = "StartDate_BaseValue"
    Const SD_OffsetTypeParameterName As String = "StartDate_OffsetType"
    Const SD_OffsetValueParameterName As String = "StartDate_OffsetValue"
    Const ED_BaseTypeParameterName As String = "EndDate_BaseType"
    Const ED_BaseValueParameterName As String = "EndDate_BaseValue"
    Const ED_OffsetTypeParameterName As String = "EndDate_OffsetType"
    Const ED_OffsetValueParameterName As String = "EndDate_OffsetValue"
    Const IsRelativeTimeSupported As Boolean = False
    Const TimeTypeParameterName As String = "TimeType"
    Const TimeWeekMapParameterName As String = "TimeWeekMap"
    
    Dim ReportTimeZone As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
    Dim ReportStartDate As DateTime
    Dim ReportEndDate As DateTime
    Dim ReportTime As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
    Dim ReportCulture As System.Globalization.CultureInfo
    Dim ParameterProcessor As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor
    
    Protected Overrides Sub OnInit()
      ReportTimeZone = Nothing
      ReportStartDate = DateTime.MinValue
      ReportEndDate = DateTime.MinValue
      ReportTime = Nothing
      ReportCulture = System.Globalization.CultureInfo.GetCultureInfo(Report.User("Language"))
      ParameterProcessor = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor(ReportCulture)
    End Sub
    
    Public Function GetCallingManagementGroupId() As String
       Return Microsoft.EnterpriseManagement.Reporting.ReportingConfiguration.ManagementGroupId
    End Function
    
    
    Public Function GetReportTimeZone() As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
       If IsNothing(ReportTimeZone) Then ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation.FromValueString(Report.Parameters(TimeZoneParameterName).Value)
       Return ReportTimeZone
    End Function
    
    Public Function ToDbDate(ByVal DateValue As DateTime) As DateTime
       return GetReportTimeZone.ToUniversalTime(DateValue)
    End Function
    
    Public Function ToReportDate(ByVal DateValue As DateTime) As DateTime
       return GetReportTimeZone.ToLocalTime(DateValue)
    End Function
    
    Public Function GetReportStartDate() As DateTime
      If (ReportStartDate = DateTime.MinValue) Then
        If (IsRelativeTimeSupported) Then
          ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
        Else
          ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value)
        End if
      End If
      return ReportStartDate
    End Function
    
    Public Function GetReportEndDate() As DateTime
      If (ReportEndDate = DateTime.MinValue) Then
        If (IsRelativeTimeSupported) Then
          ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
          If IsBusinessHours(GetReportTimeFilter()) Then ReportEndDate = ReportCulture.Calendar.AddDays(ReportEndDate, 1)
        Else
          ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value)
        End if
      End If
      return ReportEndDate
    End Function
    
    Public Function GetReportTimeFilter() As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
      If IsNothing(ReportTime) Then ReportTime = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime(Report.Parameters(TimeTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, CStr(Join(Report.Parameters(TimeWeekMapParameterName).Value, ",")))
      return ReportTime
    End Function
    
    Public Function IsBusinessHours(Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As Boolean
      return (Not IsNothing(Value)) And (Value.TimeType = Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTimeType.Business)
    End Function
    
    Public Function FormatDateTime(Format As String, Value As DateTime) As String
      return Value.ToString(Format, ReportCulture)
    End Function
    
    Public Function FormatNumber(Format As String, Value As Decimal) As String
      return Value.ToString(Format, ReportCulture)
    End Function
    
    Public Function FormatString(Format As String, ParamArray Values() as  Object) As String
      return String.Format(ReportCulture, Format, Values)
    End Function
    
    Public Function NullFormatString(Format As String, Value as  String) As String
      return IIF(String.IsNullOrEmpty(Value), String.Empty, String.Format(ReportCulture, Format, Value))
    End Function
    
    Public Function FormatBusinessHours(Format As String, Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As String
      Dim result As String
    
      If IsBusinessHours(Value) Then
     
        Dim firstDay As DayOfWeek
        Dim days As System.Collections.Generic.List(Of String)
    
        firstDay = ReportCulture.DateTimeFormat.FirstDayOfWeek
        days = new System.Collections.Generic.List(Of String)()
    
        For loopDay As DayOfWeek = DayOfWeek.Sunday To DayOfWeek.Saturday
          Dim day As DayOfWeek
          day = CType((CInt(loopDay) + CInt(firstDay)) Mod 7, DayOfWeek)
    
          If value.WeekMap.Contains(day) Then days.Add(ReportCulture.DateTimeFormat.GetAbbreviatedDayName(day))
        Next loopDay
    
         result = FormatString(Format, DateTime.Today.Add(Value.StartTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), DateTime.Today.Add(Value.EndTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), String.Join(",", days.ToArray()))
    
      Else
         result = String.Empty
      End if
      
      return result
    End Function
    
    Public Function BuildXmlValueList(ByVal ValueList() As Object) As String
        Return Microsoft.EnterpriseManagement.Reporting.MultiValueParameter.ToXml("Data", "Value", ValueList)
    End Function
    </Code>
    

    This is the entire code section from the System Center Report Library, and contains more code than you require for a single control. However, including this code block guarantees that you have included the code you require for your chosen control.

  14. After the /Code tag, add the following code:

    <CodeModules>
       <CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=6.0.0.0, Culture=neutral</CodeModule>
    </CodeModules>
    
    

    This element is required to enable the code in the Code section to work correctly.

  15. Save the report definition in the external editor. The report definition now appears in the Properties window for the report in the RDL field. Delete the first line in that field, which looks like this:

    <?xml version="1.0" encoding="utf-8"?>
    

    Click OK to close the Properties window. Save the management pack.

  16. Finally, you must add a reference to the System Center Data Warehouse Report Library. This cannot be done from within the Authoring console. Locate the file where your management pack is saved, and open it with the XML editor of your choice. Within the References section of the file, add the following code:

    <Reference Alias="MicrosoftSystemCenterDataWarehouseReportLibrary">
       <ID>Microsoft.SystemCenter.DataWarehouse.Report.Library</ID>
       <Version>6.1.7221.0</Version>
       <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
    
  17. Save the management pack, and then deploy it as discussed in Deploying Reports.

Example: Adding a Relative Date Time Picker to the Alert Report

In this example, you’ll add a Relative Date Time Picker control to the smart parameter block of the Alert Report created in Creating Cutom Queries (Creating Custom Queries)

To add a custom control to the Alert Report

  1. Open the management pack containing the Alert report in the Authoring console. Select the Reporting tab, right-click the Alert report, and open its Properties window.

  2. Click the Parameter Block tab, and click Edit in external editor. The external editor of your choice opens, showing a blank .rpdl file.

  3. To add a Relative Date Time Picker control, find the XML code for the control in (Date Report Controls). The code in question looks like this:

    <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.RelativeDateTimePicker" columnSpan="2" rowSpan="2">
       <ReportParameters>
          <ReportParameter name="TimeZone" binding="TimeZone">
             <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.TimeZone</Prompt>
       </ReportParameter>
       <ReportParameter name="TimeZoneName" binding="TimeZoneName" />
       <ReportParameter name="StartDate_BaseType" binding="StartDate_BaseType" />
       <ReportParameter name="StartDate_BaseValue" binding="StartDate_BaseValue">
             <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.StartDateTime</Prompt>
       </ReportParameter>
             <ReportParameter name="StartDate_OffsetType" binding="StartDate_OffsetType" />
             <ReportParameter name="StartDate_OffsetValue" binding="StartDate_OffsetValue" />
             <ReportParameter name="EndDate_BaseType" binding="EndDate_BaseType" />
             <ReportParameter name="EndDate_BaseValue" binding="EndDate_BaseValue">
             <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.EndDateTime</Prompt>
             </ReportParameter>
             <ReportParameter name="EndDate_OffsetType" binding="EndDate_OffsetType" />
             <ReportParameter name="EndDate_OffsetValue" binding="EndDate_OffsetValue" />
       </ReportParameters>
    </Control>
    
    
  4. Copy this code into your blank .rpdl file.

  5. You also must add the ParameterBlock and Controls tags to enclose the Control tag. With the parameter block tags added, the code looks like this:

    <ParameterBlock columns="6" xmlns="http://schemas.microsoft.com/mom/reporting/2007/ReportParameterSettings">
       <Controls>
          <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.RelativeDateTimePicker" columnSpan="2" rowSpan="2">
             <ReportParameters>
                <ReportParameter name="TimeZone" binding="TimeZone">
                   <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.TimeZone</Prompt>
                </ReportParameter>
                <ReportParameter name="TimeZoneName" binding="TimeZoneName" />
                <ReportParameter name="StartDate_BaseType" binding="StartDate_BaseType" />
                <ReportParameter name="StartDate_BaseValue" binding="StartDate_BaseValue">
                   <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.StartDateTime</Prompt>
                </ReportParameter>
                <ReportParameter name="StartDate_OffsetType" binding="StartDate_OffsetType" />
                <ReportParameter name="StartDate_OffsetValue" binding="StartDate_OffsetValue" />
                <ReportParameter name="EndDate_BaseType" binding="EndDate_BaseType" />
                <ReportParameter name="EndDate_BaseValue" binding="EndDate_BaseValue">
                   <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.EndDateTime</Prompt>
                </ReportParameter>
                <ReportParameter name="EndDate_OffsetType" binding="EndDate_OffsetType" />
                <ReportParameter name="EndDate_OffsetValue" binding="EndDate_OffsetValue" />
             </ReportParameters>
          </Control>
       </Controls>
    </ParameterBlock>
    
  6. Save the file, and then close the external editor. The code now appears in the Parameter Block field.

  7. Now you must change the query in the report definition to use the parameters. Open the report file in BIDS.

  8. Right-click DataSet1 to select Query. The Query Designer opens, displaying the query for this dataset.

  9. In the Query Designer, edit the WHERE clause, remove the hard-coded dates, and replace them with the parameters @StartDate and @EndDate. The additional CONVERT functions are necessary to convert the parameters to the DATETIME type. The WHERE clause of the query now looks like this:

    WHERE     (vAlertResolutionState.ResolutionState = 255) AND (vAlertParameter.ParameterIndex = 1)
    AND (
      CONVERT(DATETIME, CONVERT(VARCHAR, vAlert.RaisedDateTime, 101))
      BETWEEN
      CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101))
      AND
      CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101))
    )
    AND (vMonitor.MonitorDefaultName = 'Availability') AND (vManagedEntityType.ManagedEntityTypeDefaultName = 'Computer')
    ORDER BY vAlert.RaisedDateTime
    
  10. Return to the Authoring console. In the Authoring console, in the Properties window for this report, on the Definition tab, click Load content from file, and then select the file for the report that you just updated. The content of the .rdl file appears in the text field.

  11. You now must add a ReportParameters element to the report definition. In the Properties window for the report, click the Definition tab, and then click Edit in External Editor. The report opens in the XML editor of your choice. Search for a ReportParameters section in the file, and if it exists, delete it. Following the closing /DataSources tag in the report, add the following code:

    <ReportParameters>
       <ReportParameter Name="StartDate_BaseType">
          <DataType>String</DataType>
          <Prompt>[Start Date Base]</Prompt>
       </ReportParameter>
       <ReportParameter Name="StartDate_BaseValue">
          <DataType>DateTime</DataType>
          <Prompt>[From]</Prompt>
       </ReportParameter>
       <ReportParameter Name="StartDate_OffsetType">
          <DataType>String</DataType>
          <Prompt>[Start Date Offset Type]</Prompt>
       </ReportParameter>
       <ReportParameter Name="StartDate_OffsetValue">
          <DataType>Integer</DataType>
          <AllowBlank>true</AllowBlank>
          <Prompt>[Start Date Offset]</Prompt>
       </ReportParameter>
       <ReportParameter Name="EndDate_BaseType">
          <DataType>String</DataType>
          <Prompt>[End Date Base]</Prompt>
       </ReportParameter>
       <ReportParameter Name="EndDate_BaseValue">
          <DataType>DateTime</DataType>
          <Prompt>[To]</Prompt>
       </ReportParameter>
       <ReportParameter Name="EndDate_OffsetType">
          <DataType>String</DataType>
          <Prompt>[End Date Offset Type]</Prompt>
       </ReportParameter>
       <ReportParameter Name="EndDate_OffsetValue">
          <DataType>Integer</DataType>
          <AllowBlank>true</AllowBlank>
          <Prompt>[End Date Offset]</Prompt>
       </ReportParameter>
       <ReportParameter Name="TimeZone">
          <DataType>String</DataType>
          <Prompt>[Time Zone]</Prompt>
       </ReportParameter>
       <ReportParameter Name="TimeZoneName">
          <DataType>String</DataType>
          <Nullable>true</Nullable>
          <AllowBlank>true</AllowBlank>
          <Prompt>[Time Zone Name]</Prompt>
       </ReportParameter>
    </ReportParameters>
    
  12. You must now add the query parameters to the DataSet for this report. Following the /CommandText tag that contains the query for the report, add the following code:

    <QueryParameters>
       <QueryParameter Name="@StartDate">
          <Value>=Code.ToDbDate(Code.GetReportStartDate())</Value>
       </QueryParameter>
       <QueryParameter Name="@EndDate">
          <Value>=Code.ToDbDate(Code.GetReportEndDate())</Value>
       </QueryParameter>
    </QueryParameters>
    

    If you edited the query in BIDS, and the QueryParameter section already exists, you only have to replace the Value elements.

  13. The smart parameter block requires additional code to process the report parameters. Locate the closing /DataSets tag, add the Code element, and then insert the code block from the previous section.

  14. After the /Code tag, add the following code:

    <CodeModules>
       <CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=6.0.0.0, Culture=neutral</CodeModule>
    </CodeModules>
    
    

    This element is required to enable the code in the Code section to work correctly.

  15. Save the .rdl file, and then close the external editor. The updated .rdl file appears in the RDL field on the Properties window. Delete the first line in that field, which looks like this:

    <?xml version="1.0" encoding="utf-8"?>
    

    Click OK to close the Properties window. Save the management pack.

  16. Locate the file where your management pack is saved, and open it with the XML editor of your choice. Within the References section of the file, add the following code:

    <Reference Alias="MicrosoftSystemCenterDataWarehouseReportLibrary">
       <ID>Microsoft.SystemCenter.DataWarehouse.Report.Library</ID>
       <Version>6.1.7221.0</Version>
       <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
    
  17. Save the report, and then deploy it as discussed in Deploying Reports.

 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft