Citrix XenServer Workload Balancing, among other things, allows for the implementation of custom designed reports that can be visible to XenCenter users.  This entry describes the basic steps required to implement your own custom reports using XenServer Workload Balancing.

To effectively implement custom reports, basic knowledge of the following skills is required:

-   T-SQL query skills using Microsoft SQL Server Management Studio.
-   Designing RDL based reports using Microsoft Business Intelligence Development Studio.
-   Manually editing XML markup.

A custom report can easily be created, deployed and displayed to XenCenter users by executing the following four steps:

1) Within the Workload Balancing database, create a stored procedure that queries and returns the data you wish to collect.  The source code from one of the existing report query stored procedures can be copied, renamed, and modified for your specific needs.  Preexisting stored procedures can be located by connecting to the WLB database with Microsoft SQL Server Management Studio and browsing the stored procedure collection.  Report query procedures are prefixed with “rp_”.

XenServer Workload Balancing exposes several denormalized views for general consumption.  Additional information about these views can be found in the following knowledgebase article: https://support.citrix.com/servlet/KbServlet/download/20474-102-280979/xs-wlb-sql-db-views.pdf

2) Now that the report query stored procedure is defined,  create a report title label that will be visible to XenCenter users in the Workload Reporting interface.  This label can be localized in English or Japanese.  To define the report label, paste the T-SQL code shown below into Microsoft SQL Server Management Studio.  Modify the four variables accordingly and then execute the entire statement.  Note that you will need to use the label name in step 3b below.

-———————--- Begin of T-SQL Statement –-——————————-

-- Enter a unique name for your report label. This name must be prefixed
-- with LBL_ andcannot contain any whitespace characters. All
-- numeric/uppercase only. Make note of the report label name (@name) for
-- step 3b below.

declare @name nvarchar(40) = ‘LBL_CUSTOM_CHARGEBACK_REPORT’

-- Specify locale for this label. ‘en’ for English or ‘ja’ for Japanese

declare @two_letter_code nchar(2) = ‘en’

-- Enter the actual report title here. This will be displayed to XenCenter users.

declare @description nvarchar(255) = ‘Custom Chargeback Report’

-- Long description for this label for your own use (not exposed)

declare @long_description nvarchar(512) = ‘My Chargeback report title label’
insert into report_label values (@name, 0x0100)
declare @rlid int = (select rlid from report_label where label_name = @name)
insert into report_label_locale(rlid, two_letter_code, [description], long_description)
values (@rlid, @two_letter_code, @description, @long_description)

-———————--- End of T-SQL Statement –-——————————–-


3) Author an RDLC file to report against your stored procedure.  There are some simple requirements that must be met within your RDLC markup for WLB to properly execute the report and return data. 



a. Be sure the DataSources element of your report is identical to the following in your RDLC markup:

<DataSources>
    <DataSource Name=KirkwoodDBConnectionString>
      <rd:DataSourceID>c238093e-a6F8-4902-a164-f90C3a77ae0e</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString />
      </ConnectionProperties>
    </DataSource>
</DataSources>

b. The following report parameters must be present in the “ReportParameters” element of your RDLC markup.  Add any additional parameters you wish to use.

Locale code passed from the XenCenter client.

<ReportParameter Name=LocaleCode>
      <DataType>String</DataType>
      <Prompt>LocaleCode</Prompt>
</ReportParameter>
   

Start date for the query passed from the XenCenter client.

<ReportParameter Name=Start>
      <DataType>Integer</DataType>
      <Prompt>Start</Prompt>
    </ReportParameter>

End date for the query passed from the XenCenter client.

    <ReportParameter Name=End>
      <DataType>Integer</DataType>
      <Prompt>End</Prompt>
    </ReportParameter>

Uuid of the pool passed from the XenCenter client (optional for cross pool queries).

    <ReportParameter Name=PoolID>
      <DataType>String</DataType>
      <Prompt>PoolID</Prompt>
    </ReportParameter>

Used for localization

    <ReportParameter Name=ParamLabels>
      <DataType>String</DataType>
      <Prompt>ParamLabels</Prompt>
    </ReportParameter>

Used for localization.

    <ReportParameter Name=ParamValues>
      <DataType>String</DataType>
      <Prompt>ParamValues</Prompt>
    </ReportParameter>

UTC offset time from the XenCenter client to assist with offsetting UTC times from the WLB database.

    <ReportParameter Name=UTCOffset>
      <DataType>Integer</DataType>
      <Prompt>UTCOffset</Prompt>
    </ReportParameter>

Name of the stored procedure to execute in the WLB database.

    <ReportParameter Name=SPROC_NAME>
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>rp_custom_chargeback</Value>
        </Values>
      </DefaultValue>
      <Hidden>true</Hidden>
    </ReportParameter>

Name of the report title label from step 2 above.

    <ReportParameter Name=REPORT_NAME_LABEL>
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>LBL_CUSTOM_CHARGEBACK_REPORT</Value>
        </Values>
      </DefaultValue>
      <MultiValue>true</MultiValue>
      <Hidden>true</Hidden>
    </ReportParameter>
</ReportParameters>

c. The DataSets element of the RDLC file must contain the following general structure.  In this section add in your fields and any query parameters.

<DataSets>
    <DataSet Name=KirkwoodDBDataSetLocal>
      <Fields>

              Overwrite with your field names in this section to align with your query results.

          <Field Name=vmname>
             <DataField>vmname</DataField>
             <rd:TypeName>System.String</rd:TypeName>
          </Field>
      
    </Fields>
      <Query>
        <DataSourceName>KirkwoodDBConnectionString</DataSourceName>
        <CommandText />
        <QueryParameters>
      Insert any parameters required by the query stored procedure in this section.
          <QueryParameter Name=@Start>
            <Value>=Parameters!Start.Value</Value>
             </QueryParameter>
          </QueryParameters>
    
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
</DataSets>


4) Run the WLB Report Import utility located in your Citrix Workload Balancing program file directory.  This will load all RDLC reports located in the specified directory (designated with the --p flag).  In the example below, RDLC files in the C:\temp\reports directory will be uploaded to Workload Balancing.

    C:\Program Files\Citrix\Workload Balancing\Agent\ReportImport.exe -p C:\temp\reports

You will now be able to open XenCenter and navigate to a pool that is configured for this Workload Balancing server.   Select “View Workload Reports …” from the Pool dropdown menu and bring up the Workload Reporting interface.  You will notice your report is available for viewing in the “Reports” pane.