none
Require assistance in using Relative dates with custom SCOM Reports

    Question

  • Hello,

    Is there someone out there that can provide assistance in using relative dates with custom SCOM reports?

    I have created a custom management pack which contains a report using Visual Studio. Unfortunately Visual studio does not contain relative dates as parameter controls. The problem I have then is that I cannot create a single weekly schedule in SCOM reporting, which contains dates from previous weeks, without having to create a new schedule for each week and then supplying static data. SCOM does not allow you to use SQL parameters, even in a text field, when scheduling reports (Visual Studio does). It only allows static parameter data.

    I have looked at various forums however I have not been able to understand any of these as they are written for advanced authors who have extensive knowledge in XML. I have managed to create a custom report in Visual Studio and then added it to a custom management pack within the authoring console by copying and pasting some xml data as explained in the linked article below:

    http://systemcentercentral.com/BlogDetails/tabid/143/indexid/60805/Default.aspx

    Unfortunately I am not an expert at XML and therefore I am unable to find a simple explanation on how I may be able to use relative dates. I have a simple report that displays the amount of alerts per monitoring object and displays them from the highest to the lowest count for a given period. I therefore require the use of relative dates so I can create a single schedule to run weekly and provide data from the previous week by selecting a relative dates.

    It would be appreciated if someone could assist me with this request. I can attach the xml management pack I have if required.

    Regards

    Sunday, March 27, 2011 11:29 PM

Answers

  • Hi Diacos,

    Do you have the TimeZone parameters in the <ReportParameters> block under <Definition>?

     

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

    thanks,

    chris

     


    Chris Harris [MSFT] Program Manager System Center Operations Manager
    Friday, April 29, 2011 6:09 AM
    Owner

All replies

  • After many hours of working at this I have worked out a simple way of enabling the relative date parameter control. However by enabling this control it also enables the timezone parameter. My SQL query behind my report does not utilise the time zone data. This has created a problem in that the report will not run as it reports back saying there is no reference in my report for a time zone. Does anyone know how I may be able to remove the time zone reference, if not then how do I incorporate or utilise this time zone data? I do not have experience in using timezone data in SQL queries.

    incorporating relative data parameters into custom SCOM reports

    For anyone who is interested.

    1. Create your report in the standard way using Visual Studio (Business Intelligence)

    2. Extract the XML data generated by the report.

    3. Locate the sections containing the report parameters. The section will be between and inclusive of <ReportParameterss> and </ReportParameters> and then replace this with the parameter block information below:

      <ParameterBlock xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
        <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.RelativeDateTimePicker" rowSpan="2" columnSpan="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>
      </ParameterBlock>

    4. Copy your xml data into the custom management pack as per the article below:

    http://systemcentercentral.com/BlogDetails/tabid/143/indexid/60805/Default.aspx

    I still have the issue about handling the timexone information. Is anyone able to shed some light as to hopw I reference the time zone data in my report or alternateley how I may be able to disable it all together. The error I get when I try to run the report is: Parameter 'TimeZone' does not exist on this report.

    Assitance would be much appreciated.

    Regards

     

    Monday, March 28, 2011 10:59 PM
  • Hi Diacos,

    Do you have the TimeZone parameters in the <ReportParameters> block under <Definition>?

     

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

    thanks,

    chris

     


    Chris Harris [MSFT] Program Manager System Center Operations Manager
    Friday, April 29, 2011 6:09 AM
    Owner
  • Hey, I know its late, but for others to benefit, here is some light to this.  I just started doing the custom reports and had the same questions like you did. This article explains everything you need to know but its very in depth.  Few points to add to the article.

    1) At one point they ask you to get the report parameters from a sample report published by another base MP. You can just run this query against "ReportServer" database to get all reports that are available:

    SELECT

    Name, CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportXML

    FROM

    ReportServer.dbo.

     

     Type = 2

    order

     by Name

    Copy out the XML for the report such as "Microsoft.SystemCenter.DataWarehouse.Report.Performance"  - this will give you the source code to show you as to how Microsoft did it. I extracted from there the parameter list:

    <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="TimeType">
    <DataType>String</DataType>
    <AllowBlank>true</AllowBlank>
    <Prompt>[Time Type]</Prompt>
    </ReportParameter>
    <ReportParameter Name="TimeWeekMap">
    <DataType>String</DataType>
    <Prompt>[Week Days]</Prompt>
    <MultiValue>true</MultiValue>
    </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>
    <Hidden>true</Hidden>
    </ReportParameter>

      

    It gets more involved when passing the parameters to the actual query where you call on one of the functions that the link I provided tells you to add to your report xml source. Example

     

     <DataSourceName>DataWarehousePerf</DataSourceName>

      <CommandType>StoredProcedure</CommandType>
      <CommandText>Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet</CommandText>
    - <QueryParameters>
    - <QueryParameter Name="@StartDate">
      <Value>=Code.ToDbDate(Code.GetReportStartDate())</Value>
      </QueryParameter>
    - <QueryParameter Name="@EndDate">
      <Value>=Code.ToDbDate(Code.GetReportEndDate())</Value>
      </QueryParameter>
    - <QueryParameter Name="@OptionList">
      <Value>=Parameters!ObjectList.Value</Value>
      </QueryParameter>
    - <QueryParameter Name="@DataAggregation">
      <Value>=Parameters!DataAggregation.Value</Value>
      </QueryParameter>
    + <QueryParameter Name="@LanguageCode">
      <Value>=Code.GetReportLocLanguageCode()</Value>
      </QueryParameter>
      </QueryParameters>
      </Query>

    So basically you are doing this:

    1) Create your report in BIDS

    2) Create a MP and report

    3) Add the Custom Parameter script block to the report in authoring console on Parameter Script Tab

    4) Edit the report in BIDS by viewing the XML code for it

    5) Add the report parameters you got from the base report as I mentioned earlier

    6) Edit the Query parameters to have them mapped to the report parameters by using Functions

    7) Add the code block with functions that will translate all the timezone, business days, etc into @StartDate and @EndDate parameters for SQL query to use

    8) Add the <CodeModules> code from the link I provided

    9) Load the rpdl into report in authoring console

    10) remove the first line of the xml that you loaded

    11) Save the report\MP and close it in authoring console

    12) Edit the MP XML and add the reference XML tag to it

    follow the rest for testing it out.

    PS: You need lots of patience to do this :) Hope this helps someone as it took me a while how to connect these pieces.

    Added Note:

    You need to add a filter to each DataSet in your report for the BusinessHours etc to work:

    <Filters

    ><Filter><FilterExpression>=Code.GetReportTimeFilter().Contains(Code.ToReportDate(Fields!DateTime.Value))</FilterExpression><Operator>Equal</Operator><FilterValues><FilterValue>=True</FilterValue> </FilterValues> </Filter></Filters><Query> etc.....

    • Proposed as answer by kpsols Friday, December 09, 2011 9:21 PM
    • Edited by kpsols Wednesday, December 14, 2011 2:31 PM
    Friday, December 09, 2011 9:03 PM
  • kpsols... your post refers several times to a linked article, but i don't see the link. 
    Monday, June 11, 2012 4:49 PM
  • Hi kpsols...

    I managed to get my report all working with the relative date parameters however I am having one last little problem. When the report runs the start date and end dates are displayed 2 hours in advanced of those specified or selected via the parameters.

    Eg: if I enter in the report dates of say Start Date/Time: 01/06/2012 @ 12:00am and End Date/Time: 08/06/2012 @ 11:59pm the reports runs and display the dates as Start Date/Time: 01/06/2012 @ 02:00am adn End Date/Time: 09/06/2012 @ 01:59am.

    Is there something that I am missing and is there a reason why there is two hours differenece?

    Thanks

    Thursday, June 28, 2012 5:03 AM