Shirly Radco has uploaded a new change for review. Change subject: reports: br18a report - added comments to queries ......................................................................
reports: br18a report - added comments to queries Changed the code structure so it will be more readable and added commets. Change-Id: I5bbb94ff6caa6658d2d4ab3ca666be83de1a307f Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A_files/active_entities_by_os_BR18A_jrxml.data 1 file changed, 346 insertions(+), 229 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/10/27810/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A_files/active_entities_by_os_BR18A_jrxml.data b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A_files/active_entities_by_os_BR18A_jrxml.data index 676ce4f..679100f 100644 --- a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A_files/active_entities_by_os_BR18A_jrxml.data +++ b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A_files/active_entities_by_os_BR18A_jrxml.data @@ -1,233 +1,350 @@ <?xml version="1.0" encoding="UTF-8"?> +<!-- Created with Jaspersoft Studio version 5.5.0--> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"; name="BR18A" language="groovy" pageWidth="540" pageHeight="585" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="540" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error" uuid="d7d6c16e-a751-4e69-aef9-1f2272082c97"> - <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A"/> - <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/services/repository"/> - <property name="ireport.zoom" value="1.0"/> - <property name="ireport.x" value="0"/> - <property name="ireport.y" value="0"/> - <style name="style1"> - <conditionalStyle> - <conditionExpression><![CDATA[($V{Date_group} % 2) == 0]]></conditionExpression> - <style mode="Opaque" backcolor="#F2F3F9"/> - </conditionalStyle> - </style> - <parameter name="is_deleted" class="java.lang.String"> - <defaultValueExpression><![CDATA["AND delete_date IS NULL"]]></defaultValueExpression> - </parameter> - <parameter name="P_Period" class="java.lang.Short"> - <parameterDescription><![CDATA[Period]]></parameterDescription> - <defaultValueExpression><![CDATA[0]]></defaultValueExpression> - </parameter> - <parameter name="P_Start_Date" class="java.util.Date"> - <defaultValueExpression><![CDATA["2000-01-01"]]></defaultValueExpression> - </parameter> - <parameter name="P_DataCenter_ID" class="java.lang.String"> - <parameterDescription><![CDATA[Select a DataCenter]]></parameterDescription> - <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> - </parameter> - <parameter name="P_Cluster_ID" class="java.lang.String"> - <parameterDescription><![CDATA[Select a Cluster]]></parameterDescription> - <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> - </parameter> - <parameter name="REPORT_NAME" class="java.lang.String" isForPrompting="false"/> - <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"> - <defaultValueExpression><![CDATA["C:\\Program Files\\jasperserver-pro-3.7\\"]]></defaultValueExpression> - </parameter> - <parameter name="table_name" class="java.lang.String"> - <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "hourly" : "daily"]]></defaultValueExpression> - </parameter> - <parameter name="calendar_column" class="java.lang.String"> - <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "the_datetime" : "the_date"]]></defaultValueExpression> - </parameter> - <parameter name="P_Locale" class="java.lang.String"> - <defaultValueExpression><![CDATA[$P{REPORT_LOCALE}.toString()]]></defaultValueExpression> - </parameter> - <queryString> - <![CDATA[select distinct $P!{calendar_column} as the_date, coalesce(os_name,max(os_name) over (partition by 1),'') as os_name, vm_count -FROM (SELECT (CASE - WHEN UPPER(coalesce(enum_os_type.value_localized,enum_os_type.value,'Unknown OS')) like 'UNKN%' THEN 'Unknown OS' - ELSE coalesce(enum_os_type.value_localized,enum_os_type.value,'Unknown OS') - END) as os_name, - history_datetime, - COUNT(DISTINCT v3_5_configuration_history_vms.vm_id) AS vm_count - FROM v3_5_statistics_vms_resources_usage_$P!{table_name} - INNER JOIN v3_5_configuration_history_vms ON (v3_5_configuration_history_vms.history_id = v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_configuration_version) - INNER JOIN (SELECT enum_translator_default.enum_type, enum_translator_default.enum_key, enum_translator_default.value, enum_translator_localized.value_localized - FROM enum_translator as enum_translator_default - LEFT OUTER JOIN (SELECT enum_type, enum_key, value as value_localized - FROM enum_translator - WHERE language_code = $P{P_Locale}) as enum_translator_localized ON (enum_translator_localized.enum_type = enum_translator_default.enum_type and enum_translator_localized.enum_key = enum_translator_default.enum_key) - WHERE language_code = 'en_US') enum_os_type ON (enum_os_type.enum_key = v3_5_configuration_history_vms.operating_system AND enum_os_type.enum_type = 'OS_TYPE') - INNER JOIN v3_5_configuration_history_vms a ON (a.vm_id = v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id) - WHERE v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_status = 1 - AND v3_5_configuration_history_vms.cluster_id in (SELECT cluster_id - FROM v3_5_configuration_history_clusters - WHERE datacenter_id = cast($P{P_DataCenter_ID} as uuid)) - AND v3_5_configuration_history_vms.cluster_id = CASE $P{P_Cluster_ID} - WHEN NULL THEN v3_5_configuration_history_vms.cluster_id - WHEN '11111111-1111-1111-1111-111111111111' THEN v3_5_configuration_history_vms.cluster_id - ELSE cast($P{P_Cluster_ID} as uuid) - END - AND a.history_id in (SELECT max(b.history_id) - FROM v3_5_configuration_history_vms b - GROUP BY b.vm_id) - AND history_datetime >= cast($P{P_Start_Date} as timestamp) - AND history_datetime <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND CASE - WHEN $P{is_deleted} like 'AND%' THEN a.delete_date IS NULL - ELSE a.delete_date IS NULL or a.delete_date IS NOT NULL - END - GROUP BY (CASE - WHEN UPPER(coalesce(enum_os_type.value_localized,enum_os_type.value,'Unknown OS')) like 'UNKN%' THEN 'Unknown OS' - ELSE coalesce(enum_os_type.value_localized,enum_os_type.value,'Unknown OS') - END), - history_datetime) a right outer join calendar b on (history_datetime = b.$P!{calendar_column}) -WHERE $P!{calendar_column} >= cast($P{P_Start_Date} as date) - AND $P!{calendar_column} <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END + <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A"/> + <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/> + <property name="ireport.zoom" value="1.0"/> + <property name="ireport.x" value="0"/> + <property name="ireport.y" value="0"/> + <property name="ireport.jasperserver.report.resource" value="/reports_resources/embedded_reports/Inventory_Dashboard/active_entities_by_os_BR18A_files/active_entities_by_os_BR18A_jrxml"/> + <style name="style1"> + <conditionalStyle> + <conditionExpression><![CDATA[($V{Date_group} % 2) == 0]]></conditionExpression> + <style mode="Opaque" backcolor="#F2F3F9"/> + </conditionalStyle> + </style> + <parameter name="is_deleted" class="java.lang.String"> + <defaultValueExpression><![CDATA["AND delete_date IS NULL"]]></defaultValueExpression> + </parameter> + <parameter name="P_Period" class="java.lang.Short"> + <parameterDescription><![CDATA[Period]]></parameterDescription> + <defaultValueExpression><![CDATA[0]]></defaultValueExpression> + </parameter> + <parameter name="P_Start_Date" class="java.util.Date"> + <defaultValueExpression><![CDATA["2000-01-01"]]></defaultValueExpression> + </parameter> + <parameter name="P_DataCenter_ID" class="java.lang.String"> + <parameterDescription><![CDATA[Select a DataCenter]]></parameterDescription> + <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> + </parameter> + <parameter name="P_Cluster_ID" class="java.lang.String"> + <parameterDescription><![CDATA[Select a Cluster]]></parameterDescription> + <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> + </parameter> + <parameter name="REPORT_NAME" class="java.lang.String" isForPrompting="false"/> + <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"> + <defaultValueExpression><![CDATA["C:\\Program Files\\jasperserver-pro-3.7\\"]]></defaultValueExpression> + </parameter> + <parameter name="table_name" class="java.lang.String"> + <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "hourly" : "daily"]]></defaultValueExpression> + </parameter> + <parameter name="calendar_column" class="java.lang.String"> + <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "the_datetime" : "the_date"]]></defaultValueExpression> + </parameter> + <parameter name="P_Locale" class="java.lang.String"> + <defaultValueExpression><![CDATA[$P{REPORT_LOCALE}.toString()]]></defaultValueExpression> + </parameter> + <queryString language="SQL"> + <![CDATA[-- BR18A - This query will return the total number of VMs, +-- Count by "Known" OS type and "Unknown" OS type. + +SELECT DISTINCT + --If "Period" is "Daily" then + -- the "calendar_column" parameter is equal to "the_datetime" else "the_date" + $P!{calendar_column} AS the_date, + COALESCE ( + os_name, + MAX ( os_name ) over ( partition by 1 ), + '' + ) AS os_name, + vm_count +FROM ( + SELECT + ( + CASE + WHEN + COALESCE ( + enum_os_type.enum_key, + 0 + ) IN ( 0, 1001 ) + THEN 'Unknown OS' + ELSE + COALESCE ( + enum_os_type.value_localized, + enum_os_type.value, + 'Unknown OS' + ) + END + ) AS os_name, + history_datetime, + COUNT ( + DISTINCT v3_5_configuration_history_vms.vm_id + ) AS vm_count + -- If "Period" equals to "Daily" then "table_name" + -- parameter equals to "hourly" else "daily" + FROM v3_5_statistics_vms_resources_usage_$P!{table_name} + INNER JOIN v3_5_configuration_history_vms + ON ( + v3_5_configuration_history_vms.history_id = + v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_configuration_version + ) + INNER JOIN ( + SELECT + enum_translator_default.enum_type, + enum_translator_default.enum_key, + enum_translator_default.value, + enum_translator_localized.value_localized + FROM enum_translator AS enum_translator_default + LEFT OUTER JOIN ( + SELECT + enum_type, + enum_key, + value AS value_localized + FROM enum_translator + WHERE language_code = $P{P_Locale} + ) AS enum_translator_localized + ON ( + enum_translator_localized.enum_type = + enum_translator_default.enum_type + AND enum_translator_localized.enum_key = + enum_translator_default.enum_key + ) + WHERE language_code = 'en_US' + ) enum_os_type + ON ( + enum_os_type.enum_key = + v3_5_configuration_history_vms.operating_system + AND enum_os_type.enum_type = 'OS_TYPE' + ) + INNER JOIN v3_5_configuration_history_vms AS a + ON ( + a.vm_id = + v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id + ) + WHERE + -- Here we get only "active" vms + v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_status = 1 + -- Filter vms list according to the datacenter that was chosen by the user + AND v3_5_configuration_history_vms.cluster_id IN ( + SELECT cluster_id + FROM v3_5_configuration_history_clusters + WHERE + datacenter_id = + CAST ( $P{P_DataCenter_ID} AS uuid ) + ) + -- Filter vms list according to the cluster that was chosen by the user + AND v3_5_configuration_history_vms.cluster_id = + CASE $P{P_Cluster_ID} + WHEN NULL + THEN v3_5_configuration_history_vms.cluster_id + WHEN '11111111-1111-1111-1111-111111111111' + THEN v3_5_configuration_history_vms.cluster_id + ELSE CAST ( $P{P_Cluster_ID} AS uuid ) + END + -- Here we get the latest vms configuration + AND a.history_id IN ( + SELECT MAX ( b.history_id ) + FROM v3_5_configuration_history_vms AS b + GROUP BY b.vm_id + ) + AND history_datetime >= CAST ( $P{P_Start_Date} AS TIMESTAMP ) + AND history_datetime < + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 year' + END + AND + -- This will determine where deleted vms will be included in the report, + -- according to the user selection for "is_deleted" parameter + CASE + WHEN $P{is_deleted} LIKE 'AND%' + THEN a.delete_date IS NULL + ELSE + a.delete_date IS NULL + OR + a.delete_date IS NOT NULL + END + GROUP BY ( + CASE + WHEN + COALESCE ( + enum_os_type.enum_key, + 0 + ) IN ( 0, 1001 ) + THEN 'Unknown OS' + ELSE + COALESCE ( + enum_os_type.value_localized, + enum_os_type.value, + 'Unknown OS' + ) + END + ), + history_datetime +) AS a + RIGHT OUTER JOIN calendar b + ON ( + history_datetime = b.$P!{calendar_column} + ) +WHERE + $P!{calendar_column} >= CAST ( $P{P_Start_Date} AS date ) + AND $P!{calendar_column} < + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 year' + END ORDER BY $P!{calendar_column}]]> - </queryString> - <field name="the_date" class="java.sql.Timestamp"/> - <field name="os_name" class="java.lang.String"/> - <field name="vm_count" class="java.lang.Long"/> - <variable name="Date_group" class="java.lang.Integer" incrementType="Group" incrementGroup="Date" calculation="DistinctCount"> - <variableExpression><![CDATA[$F{the_date}]]></variableExpression> - </variable> - <group name="Date" keepTogether="true"> - <groupExpression><![CDATA[$F{the_date}]]></groupExpression> - </group> - <group name="count_change"> - <groupExpression><![CDATA[$F{vm_count}]]></groupExpression> - </group> - <title> - <band height="240" splitType="Stretch"> - <timeSeriesChart> - <chart evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.TimelineChartCustomizer" theme="ReportsLineBarChartTheme"> - <reportElement uuid="1a72533e-bfe4-4574-98f6-18305df215ba" mode="Opaque" x="0" y="0" width="540" height="235"/> - <box> - <pen lineWidth="1.0" lineColor="#3C617F"/> - <topPen lineWidth="1.0" lineColor="#3C617F"/> - <leftPen lineWidth="1.0" lineColor="#3C617F"/> - <bottomPen lineWidth="1.0" lineColor="#3C617F"/> - <rightPen lineWidth="1.0" lineColor="#3C617F"/> - </box> - <chartTitle color="#3A5E7E"> - <font fontName="SansSerif"/> - <titleExpression><![CDATA[$R{br18a.chart.active.vms.os}]]></titleExpression> - </chartTitle> - <chartSubtitle/> - <chartLegend position="Bottom"/> - <hyperlinkParameter name="_report"> - <hyperlinkParameterExpression><![CDATA["/Reports/Executive/active_vms_by_os_br18"]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="is_deleted"> - <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_DataCenter_ID"> - <hyperlinkParameterExpression><![CDATA[$P{P_DataCenter_ID}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Cluster_ID"> - <hyperlinkParameterExpression><![CDATA[$P{P_Cluster_ID}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Period"> - <hyperlinkParameterExpression><![CDATA[$P{P_Period}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Start_Date"> - <hyperlinkParameterExpression><![CDATA[$P{P_Start_Date}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - </chart> - <timeSeriesDataset timePeriod="Hour"> - <timeSeries> - <seriesExpression><![CDATA[$F{os_name}]]></seriesExpression> - <timePeriodExpression><![CDATA[$F{the_date}]]></timePeriodExpression> - <valueExpression><![CDATA[$F{vm_count}]]></valueExpression> - <itemHyperlink> - <hyperlinkTooltipExpression><![CDATA[$F{os_name} + ", " + $F{vm_count}.toString()]]></hyperlinkTooltipExpression> - </itemHyperlink> - </timeSeries> - </timeSeriesDataset> - <timeSeriesPlot isShowLines="true" isShowShapes="false"> - <plot/> - <timeAxisLabelExpression><![CDATA[$R{axis.date}]]></timeAxisLabelExpression> - <valueAxisLabelExpression><![CDATA[$R{axis.number.of.vms}]]></valueAxisLabelExpression> - </timeSeriesPlot> - </timeSeriesChart> - </band> - </title> - <columnHeader> - <band height="20"> - <frame> - <reportElement uuid="33f0f28d-1994-4817-98d1-a675a80d272c" mode="Opaque" x="0" y="0" width="540" height="20" backcolor="#D8E2FF"/> - <textField> - <reportElement uuid="b4d775f7-528b-461c-9132-2be93f90b0bc" x="3" y="0" width="100" height="20"/> - <textElement verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression><![CDATA[$R{br18a.table.date}]]></textFieldExpression> - </textField> - <textField> - <reportElement uuid="6ab6992f-3b94-4634-82c4-6da465176960" x="109" y="0" width="299" height="20"/> - <textElement verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression><![CDATA[$R{br18a.table.os.version}]]></textFieldExpression> - </textField> - <textField> - <reportElement uuid="8f6498a9-0975-43f9-a857-2ec56bf5c0eb" x="463" y="0" width="69" height="20"/> - <textElement verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression><![CDATA[$R{br18a.table.number.of.vms}]]></textFieldExpression> - </textField> - </frame> - </band> - </columnHeader> - <detail> - <band height="19"> - <frame> - <reportElement uuid="8b9ee783-3b44-449a-85e0-bbd1ff108bee" style="style1" x="0" y="0" width="540" height="19" isRemoveLineWhenBlank="true"> - <printWhenExpression><![CDATA[$F{vm_count} != null]]></printWhenExpression> - </reportElement> - <line> - <reportElement uuid="1762a5bc-6229-411d-ac53-6dadfa145154" x="0" y="0" width="540" height="1" forecolor="#D0DAE6"> - <printWhenExpression><![CDATA[$V{Date_COUNT} == 1]]></printWhenExpression> - </reportElement> - </line> - <textField> - <reportElement uuid="e25cddbb-2e14-4022-be59-79e98c30ba1b" x="105" y="1" width="289" height="18"> - <printWhenExpression><![CDATA[$F{vm_count} != null]]></printWhenExpression> - </reportElement> - <textElement verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression><![CDATA[$F{os_name}]]></textFieldExpression> - </textField> - <textField> - <reportElement uuid="14ff16e3-917c-4d8d-9910-e2747834735f" isPrintRepeatedValues="false" x="5" y="1" width="100" height="18"> - <printWhenExpression><![CDATA[$F{vm_count} != null]]></printWhenExpression> - </reportElement> - <textElement verticalAlignment="Middle"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression><![CDATA[DateFormat.getDateInstance(DateFormat.SHORT, $P{REPORT_LOCALE}).format($F{the_date})]]></textFieldExpression> - </textField> - <textField isBlankWhenNull="true"> - <reportElement uuid="365dc174-34fc-48d3-abd7-7dc62f1b9350" x="409" y="1" width="82" height="18" isRemoveLineWhenBlank="true"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression><![CDATA[$F{vm_count}]]></textFieldExpression> - </textField> - </frame> - </band> - </detail> + </queryString> + <field name="the_date" class="java.sql.Timestamp"/> + <field name="os_name" class="java.lang.String"/> + <field name="vm_count" class="java.lang.Long"/> + <variable name="Date_group" class="java.lang.Integer" incrementType="Group" incrementGroup="Date" calculation="DistinctCount"> + <variableExpression><![CDATA[$F{the_date}]]></variableExpression> + </variable> + <group name="Date" keepTogether="true"> + <groupExpression><![CDATA[$F{the_date}]]></groupExpression> + </group> + <group name="count_change"> + <groupExpression><![CDATA[$F{vm_count}]]></groupExpression> + </group> + <title> + <band height="240" splitType="Stretch"> + <timeSeriesChart> + <chart evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.TimelineChartCustomizer" theme="ReportsLineBarChartTheme"> + <reportElement mode="Opaque" x="0" y="0" width="540" height="235" uuid="1a72533e-bfe4-4574-98f6-18305df215ba"/> + <box> + <pen lineWidth="1.0" lineColor="#3C617F"/> + <topPen lineWidth="1.0" lineColor="#3C617F"/> + <leftPen lineWidth="1.0" lineColor="#3C617F"/> + <bottomPen lineWidth="1.0" lineColor="#3C617F"/> + <rightPen lineWidth="1.0" lineColor="#3C617F"/> + </box> + <chartTitle color="#3A5E7E"> + <font fontName="SansSerif"/> + <titleExpression><![CDATA[$R{br18a.chart.active.vms.os}]]></titleExpression> + </chartTitle> + <chartSubtitle/> + <chartLegend position="Bottom"/> + <hyperlinkParameter name="_report"> + <hyperlinkParameterExpression><![CDATA["/Reports/Executive/active_vms_by_os_br18"]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="is_deleted"> + <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_DataCenter_ID"> + <hyperlinkParameterExpression><![CDATA[$P{P_DataCenter_ID}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Cluster_ID"> + <hyperlinkParameterExpression><![CDATA[$P{P_Cluster_ID}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Period"> + <hyperlinkParameterExpression><![CDATA[$P{P_Period}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Start_Date"> + <hyperlinkParameterExpression><![CDATA[$P{P_Start_Date}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + </chart> + <timeSeriesDataset timePeriod="Hour"> + <timeSeries> + <seriesExpression><![CDATA[$F{os_name}]]></seriesExpression> + <timePeriodExpression><![CDATA[$F{the_date}]]></timePeriodExpression> + <valueExpression><![CDATA[$F{vm_count}]]></valueExpression> + <itemHyperlink> + <hyperlinkTooltipExpression><![CDATA[$F{os_name} + ", " + $F{vm_count}.toString()]]></hyperlinkTooltipExpression> + </itemHyperlink> + </timeSeries> + </timeSeriesDataset> + <timeSeriesPlot isShowLines="true" isShowShapes="false"> + <plot/> + <timeAxisLabelExpression><![CDATA[$R{axis.date}]]></timeAxisLabelExpression> + <timeAxisFormat> + <axisFormat/> + </timeAxisFormat> + <valueAxisLabelExpression><![CDATA[$R{axis.number.of.vms}]]></valueAxisLabelExpression> + <valueAxisFormat> + <axisFormat/> + </valueAxisFormat> + </timeSeriesPlot> + </timeSeriesChart> + </band> + </title> + <columnHeader> + <band height="20"> + <frame> + <reportElement mode="Opaque" x="0" y="0" width="540" height="20" backcolor="#D8E2FF" uuid="33f0f28d-1994-4817-98d1-a675a80d272c"/> + <textField> + <reportElement x="3" y="0" width="100" height="20" uuid="b4d775f7-528b-461c-9132-2be93f90b0bc"/> + <textElement verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br18a.table.date}]]></textFieldExpression> + </textField> + <textField> + <reportElement x="109" y="0" width="299" height="20" uuid="6ab6992f-3b94-4634-82c4-6da465176960"/> + <textElement verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br18a.table.os.version}]]></textFieldExpression> + </textField> + <textField> + <reportElement x="463" y="0" width="69" height="20" uuid="8f6498a9-0975-43f9-a857-2ec56bf5c0eb"/> + <textElement verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br18a.table.number.of.vms}]]></textFieldExpression> + </textField> + </frame> + </band> + </columnHeader> + <detail> + <band height="19"> + <frame> + <reportElement style="style1" x="0" y="0" width="540" height="19" isRemoveLineWhenBlank="true" uuid="8b9ee783-3b44-449a-85e0-bbd1ff108bee"> + <printWhenExpression><![CDATA[$F{vm_count} != null]]></printWhenExpression> + </reportElement> + <line> + <reportElement x="0" y="0" width="540" height="1" forecolor="#D0DAE6" uuid="1762a5bc-6229-411d-ac53-6dadfa145154"> + <printWhenExpression><![CDATA[$V{Date_COUNT} == 1]]></printWhenExpression> + </reportElement> + </line> + <textField> + <reportElement x="105" y="1" width="289" height="18" uuid="e25cddbb-2e14-4022-be59-79e98c30ba1b"> + <printWhenExpression><![CDATA[$F{vm_count} != null]]></printWhenExpression> + </reportElement> + <textElement verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[$F{os_name}]]></textFieldExpression> + </textField> + <textField> + <reportElement isPrintRepeatedValues="false" x="5" y="1" width="100" height="18" uuid="14ff16e3-917c-4d8d-9910-e2747834735f"> + <printWhenExpression><![CDATA[$F{vm_count} != null]]></printWhenExpression> + </reportElement> + <textElement verticalAlignment="Middle"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[DateFormat.getDateInstance(DateFormat.SHORT, $P{REPORT_LOCALE}).format($F{the_date})]]></textFieldExpression> + </textField> + <textField isBlankWhenNull="true"> + <reportElement x="409" y="1" width="82" height="18" isRemoveLineWhenBlank="true" uuid="365dc174-34fc-48d3-abd7-7dc62f1b9350"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[$F{vm_count}]]></textFieldExpression> + </textField> + </frame> + </band> + </detail> </jasperReport> -- To view, visit http://gerrit.ovirt.org/27810 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I5bbb94ff6caa6658d2d4ab3ca666be83de1a307f Gerrit-PatchSet: 1 Gerrit-Project: ovirt-reports Gerrit-Branch: master Gerrit-Owner: Shirly Radco <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
