Shirly Radco has uploaded a new change for review. Change subject: reports: br7a report - added comments to queries ......................................................................
reports: br7a report - added comments to queries Changed the code structure so it will be more readable and added commets. Change-Id: Ia7ec9c1ceb9b2a728173dae6ac4a66f80568b356 Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/embedded_reports/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a_files/cluster_uptime_by_datacenter_br7a_jrxml.data 1 file changed, 409 insertions(+), 334 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/52/27852/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a_files/cluster_uptime_by_datacenter_br7a_jrxml.data b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a_files/cluster_uptime_by_datacenter_br7a_jrxml.data index 9f99b16..8861af7 100644 --- a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a_files/cluster_uptime_by_datacenter_br7a_jrxml.data +++ b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a_files/cluster_uptime_by_datacenter_br7a_jrxml.data @@ -1,340 +1,415 @@ <?xml version="1.0" encoding="UTF-8"?> -<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="BR7A" language="groovy" pageWidth="510" pageHeight="832" whenNoDataType="AllSectionsNoDetail" columnWidth="510" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error"> - <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/cluster_uptime_by_datacenter_br7a"/> - <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver/services/repository"/> - <property name="ireport.zoom" value="1.0"/> - <property name="ireport.x" value="0"/> - <property name="ireport.y" value="0"/> - <style name="Line Style"> - <conditionalStyle> - <conditionExpression><![CDATA[($V{REPORT_COUNT} % 2) == 0]]></conditionExpression> - <style mode="Opaque" backcolor="#EFF3FF"/> - </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["01/01/2000"]]></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="REPORT_NAME" class="java.lang.String" isForPrompting="false"/> - <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> - <queryString> - <![CDATA[SELECT cluster_id, +<!-- 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="BR7A" language="groovy" pageWidth="510" pageHeight="832" whenNoDataType="AllSectionsNoDetail" columnWidth="510" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error" uuid="83f9c6a4-854b-4cfc-b4bb-a451b5894b69"> + <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a"/> + <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/Uptime_Dashboard/cluster_uptime_by_datacenter_br7a_files/cluster_uptime_by_datacenter_br7a_jrxml"/> + <style name="Line Style"> + <conditionalStyle> + <conditionExpression><![CDATA[($V{REPORT_COUNT} % 2) == 0]]></conditionExpression> + <style mode="Opaque" backcolor="#EFF3FF"/> + </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["01/01/2000"]]></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="REPORT_NAME" class="java.lang.String" isForPrompting="false"/> + <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> + <queryString language="SQL"> + <![CDATA[-- BR7A - This query returns the +-- average planned and unplanned downtime, +-- average uptime and total time per cluster. + +SELECT + cluster_id, name, delete_date, - avg(planned_downtime_mins) as planned_downtime_mins, - avg(unplanned_downtime_mins) as unplanned_downtime_mins, - avg(uptime_mins) as uptime_mins, - avg(total) as total -FROM (SELECT v3_5_configuration_history_hosts.cluster_id, - v3_5_configuration_history_clusters.cluster_name as name, - v3_5_configuration_history_clusters.delete_date, - v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id, - SUM(CASE WHEN v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_status = 2 THEN coalesce(v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status,0) ELSE 0 END) AS planned_downtime_mins, - SUM(CASE WHEN v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_status = 3 THEN coalesce(v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status,0) ELSE 0 END) AS unplanned_downtime_mins, - SUM(CASE WHEN v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_status = 1 THEN coalesce(v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status,0) ELSE 0 END) AS uptime_mins, - Sum(v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status) as total - FROM v3_5_statistics_hosts_resources_usage_$P!{table_name} - INNER JOIN v3_5_configuration_history_hosts - ON (v3_5_configuration_history_hosts.host_id = v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id) - INNER JOIN v3_5_configuration_history_clusters - ON (v3_5_configuration_history_clusters.cluster_id = v3_5_configuration_history_hosts.cluster_id) - WHERE - v3_5_configuration_history_hosts.cluster_id in (SELECT cluster_id - FROM v3_5_configuration_history_clusters - Where datacenter_id = cast($P{P_DataCenter_ID} as uuid)) - 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 v3_5_configuration_history_clusters.history_id in (SELECT max(a.history_id) - FROM v3_5_configuration_history_clusters a - GROUP BY a.cluster_id) - AND v3_5_configuration_history_hosts.history_id in (SELECT max(b.history_id) - FROM v3_5_configuration_history_hosts b - GROUP BY b.host_id) - AND CASE - WHEN $P{is_deleted} like 'AND%' THEN v3_5_configuration_history_hosts.delete_date IS NULL - ELSE v3_5_configuration_history_hosts.delete_date IS NULL or v3_5_configuration_history_hosts.delete_date IS NOT NULL + AVG ( planned_downtime_mins ) AS planned_downtime_mins, + AVG ( unplanned_downtime_mins ) AS unplanned_downtime_mins, + AVG ( uptime_mins ) AS uptime_mins, + AVG ( total ) AS total +FROM ( + SELECT + v3_5_configuration_history_hosts.cluster_id, + v3_5_configuration_history_clusters.cluster_name AS name, + v3_5_configuration_history_clusters.delete_date, + v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id, + SUM ( + CASE + WHEN v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_status = 2 + THEN + COALESCE ( + v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ELSE 0 END - GROUP BY - v3_5_configuration_history_hosts.cluster_id - ,v3_5_configuration_history_clusters.cluster_name - ,v3_5_configuration_history_clusters.delete_date - ,v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id) a + ) AS planned_downtime_mins, + SUM ( + CASE + WHEN v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_status = 3 + THEN + COALESCE ( + v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ELSE 0 + END + ) AS unplanned_downtime_mins, + SUM ( + CASE + WHEN v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_status = 1 + THEN + COALESCE ( + v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ELSE 0 + END + ) AS uptime_mins, + SUM ( + v3_5_statistics_hosts_resources_usage_$P!{table_name}.minutes_in_status + ) AS total + -- If "Period" equals to "Daily" then "table_name" parameter equals to "hourly" else "daily" + FROM v3_5_statistics_hosts_resources_usage_$P!{table_name} + INNER JOIN v3_5_configuration_history_hosts + ON ( + v3_5_configuration_history_hosts.host_id = + v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id + ) + INNER JOIN v3_5_configuration_history_clusters + ON ( + v3_5_configuration_history_clusters.cluster_id = + v3_5_configuration_history_hosts.cluster_id + ) + WHERE + -- Here we filter by the datacenter chosen by the user + v3_5_configuration_history_hosts.cluster_id IN ( + SELECT cluster_id + FROM v3_5_configuration_history_clusters + WHERE datacenter_id = CAST ( $P{P_DataCenter_ID} AS uuid ) + ) + 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 + -- Here we get the latest clusters configuration + AND v3_5_configuration_history_clusters.history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_5_configuration_history_clusters AS a + GROUP BY a.cluster_id + ) + -- Here we get the latest hosts configuration + AND v3_5_configuration_history_hosts.history_id IN ( + SELECT MAX ( b.history_id ) + FROM v3_5_configuration_history_hosts AS b + GROUP BY b.host_id + ) + -- This will determine if deleted hosts will be included in the report, + -- according to the user selection for "is_deleted" parameter + AND + CASE + WHEN $P{is_deleted} LIKE 'AND%' + THEN v3_5_configuration_history_hosts.delete_date IS NULL + ELSE + v3_5_configuration_history_hosts.delete_date IS NULL + OR + v3_5_configuration_history_hosts.delete_date IS NOT NULL + END + GROUP BY + v3_5_configuration_history_hosts.cluster_id, + v3_5_configuration_history_clusters.cluster_name, + v3_5_configuration_history_clusters.delete_date, + v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id +) AS a GROUP BY - cluster_id - ,name - ,delete_date -ORDER BY (CASE - WHEN delete_date IS NULL THEN 0 - ELSE 1 - END) ASC, - cast(avg(uptime_mins) as float)/cast(avg(total) as float) DESC]]> - </queryString> - <field name="cluster_id" class="java.lang.Object"/> - <field name="name" class="java.lang.String"/> - <field name="delete_date" class="java.sql.Timestamp"/> - <field name="planned_downtime_mins" class="java.math.BigDecimal"/> - <field name="unplanned_downtime_mins" class="java.math.BigDecimal"/> - <field name="uptime_mins" class="java.math.BigDecimal"/> - <field name="total" class="java.math.BigDecimal"/> - <group name="Data Center"> - <groupExpression><![CDATA[$P{P_DataCenter_ID}]]></groupExpression> - <groupHeader> - <band height="46"> - <textField> - <reportElement mode="Transparent" x="0" y="0" width="510" height="20" forecolor="#3A5E75"/> - <textElement markup="none"> - <font fontName="SansSerif" size="13" isBold="true" isUnderline="false"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[$R{br7a.title}]]></textFieldExpression> - </textField> - <frame> - <reportElement mode="Opaque" x="0" y="20" width="510" height="26" backcolor="#D8E2FF"/> - <textField> - <reportElement mode="Opaque" x="10" y="0" width="140" height="26" backcolor="#D8E2FF"/> - <textElement verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[$R{br7a.table.cluster.name}]]></textFieldExpression> - </textField> - <textField> - <reportElement mode="Opaque" x="269" y="0" width="116" height="26" backcolor="#D8E2FF"/> - <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[$R{br7a.table.planned.downtime}]]></textFieldExpression> - </textField> - <textField> - <reportElement mode="Opaque" x="388" y="0" width="122" height="26" backcolor="#D8E2FF"/> - <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[$R{br7a.table.unplanned.downtime}]]></textFieldExpression> - </textField> - <textField> - <reportElement mode="Opaque" x="150" y="0" width="117" height="26" backcolor="#D8E2FF"/> - <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="11" isBold="true"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[$R{br7a.table.uptime}]]></textFieldExpression> - </textField> - </frame> - </band> - </groupHeader> - <groupFooter> - <band height="16"> - <subreport> - <reportElement x="182" y="0" width="295" height="16"/> - <subreportExpression class="java.lang.String"><![CDATA["repo:report5.jrxml"]]></subreportExpression> - </subreport> - </band> - </groupFooter> - </group> - <group name="Cluster"> - <groupExpression><![CDATA[$F{cluster_id}.toString()]]></groupExpression> - </group> - <detail> - <band height="36" splitType="Stretch"> - <frame> - <reportElement style="Line Style" x="0" y="0" width="510" height="36"/> - <stackedBarChart> - <chart isShowLegend="false" evaluationTime="Group" evaluationGroup="Cluster" customizerClass="com.ovirt.reports.jasper.HorizontalStackedBarChart"> - <reportElement x="150" y="18" width="360" height="18"/> - <chartTitle/> - <chartSubtitle/> - <chartLegend/> - <anchorNameExpression><![CDATA["http://192.168.10.120:8080/jasperserver-pro/flow.html?_flowId=viewReportFlow&ParentFolderUri=%2FRHEV%2FReports%2FService_Level&ndefined=&standAlone=true&reportUnit=%2FRHEV%2FReports%2FService_Level%2FTest_002_1_1_1_1";]]></anchorNameExpression> - </chart> - <categoryDataset> - <dataset resetType="Group" resetGroup="Cluster"/> - <categorySeries> - <seriesExpression><![CDATA["Uptime"]]></seriesExpression> - <categoryExpression><![CDATA[$F{cluster_id}.toString()]]></categoryExpression> - <valueExpression><![CDATA[$F{uptime_mins}]]></valueExpression> - </categorySeries> - <categorySeries> - <seriesExpression><![CDATA["Planned Downtime"]]></seriesExpression> - <categoryExpression><![CDATA[$F{cluster_id}.toString()]]></categoryExpression> - <valueExpression><![CDATA[$F{planned_downtime_mins}]]></valueExpression> - </categorySeries> - <categorySeries> - <seriesExpression><![CDATA["Unplanned Downtime"]]></seriesExpression> - <categoryExpression><![CDATA[$F{cluster_id}.toString()]]></categoryExpression> - <valueExpression><![CDATA[$F{unplanned_downtime_mins}]]></valueExpression> - </categorySeries> - </categoryDataset> - <barPlot isShowLabels="false" isShowTickLabels="false" isShowTickMarks="false"> - <plot orientation="Horizontal"> - <seriesColor seriesOrder="0" color="#6EC000"/> - <seriesColor seriesOrder="1" color="#FFFF33"/> - <seriesColor seriesOrder="2" color="#FF0000"/> - </plot> - <itemLabel color="#000000" backgroundColor="#FFFFFF"/> - <categoryAxisFormat> - <axisFormat> - <labelFont/> - <tickLabelFont/> - </axisFormat> - </categoryAxisFormat> - <valueAxisFormat> - <axisFormat> - <labelFont/> - <tickLabelFont/> - </axisFormat> - </valueAxisFormat> - <rangeAxisMaxValueExpression><![CDATA[$F{planned_downtime_mins}+$F{unplanned_downtime_mins}+$F{uptime_mins}+1]]></rangeAxisMaxValueExpression> - </barPlot> - </stackedBarChart> - <textField hyperlinkType="ReportExecution" hyperlinkTarget="Blank"> - <reportElement x="10" y="0" width="140" height="36"/> - <textElement verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[$F{name}]]></textFieldExpression> - <hyperlinkParameter name="_report"> - <hyperlinkParameterExpression><![CDATA["/Reports/Service_level/Hosts/cluster_uptime_br7"]]></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[$F{cluster_id}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Host_Type"> - <hyperlinkParameterExpression><![CDATA[-1]]></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> - </textField> - <image> - <reportElement x="0" y="11" width="10" height="11"> - <printWhenExpression><![CDATA[$F{delete_date} != null]]></printWhenExpression> - </reportElement> - <imageExpression class="java.lang.String"><![CDATA["repo:trash-icon"]]></imageExpression> - </image> - <textField> - <reportElement x="150" y="0" width="32" height="18"> - <printWhenExpression><![CDATA[((($F{uptime_mins}/60)/24).intValue()) != 0]]></printWhenExpression> - </reportElement> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[((($F{uptime_mins}/60)/24).intValue()).toString() + "d"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="182" y="0" width="21" height="18"> - <printWhenExpression><![CDATA[((($F{uptime_mins}/60)/24).intValue()) != 0 || (($F{uptime_mins}/60).intValue()-(((($F{uptime_mins}/60)/24).intValue())*24)) != 0]]></printWhenExpression> - </reportElement> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[(($F{uptime_mins}/60).intValue()-(((($F{uptime_mins}/60)/24).intValue())*24)).toString() + "h"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="203" y="0" width="24" height="18"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[($F{uptime_mins} - ((($F{uptime_mins}/60).intValue()-(((($F{uptime_mins}/60)/24).intValue())*24))*60)-(((($F{uptime_mins}/60)/24).intValue())*60*24)).intValue().toString() + "m"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="227" y="0" width="40" height="18"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA["(" + (100-(($F{planned_downtime_mins}/$F{total})*100).intValue()-(($F{unplanned_downtime_mins}/$F{total})*100).intValue()).toString() + "%)"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="267" y="0" width="36" height="18"> - <printWhenExpression><![CDATA[((($F{planned_downtime_mins}/60)/24).intValue()) != 0]]></printWhenExpression> - </reportElement> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[((($F{planned_downtime_mins}/60)/24).intValue()).toString() + "d"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="303" y="0" width="21" height="18"> - <printWhenExpression><![CDATA[((($F{planned_downtime_mins}/60)/24).intValue()) != 0 || (($F{planned_downtime_mins}/60).intValue()-(((($F{planned_downtime_mins}/60)/24).intValue())*24)) != 0]]></printWhenExpression> - </reportElement> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[(($F{planned_downtime_mins}/60).intValue()-(((($F{planned_downtime_mins}/60)/24).intValue())*24)).toString() + "h"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="324" y="0" width="24" height="18"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[($F{planned_downtime_mins} - ((($F{planned_downtime_mins}/60).intValue()-(((($F{planned_downtime_mins}/60)/24).intValue())*24))*60)-(((($F{planned_downtime_mins}/60)/24).intValue())*60*24)).intValue().toString() + "m"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="348" y="0" width="40" height="18"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA["(" + (($F{planned_downtime_mins}/$F{total})*100).intValue().toString() + "%)"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="388" y="0" width="37" height="18"> - <printWhenExpression><![CDATA[((($F{unplanned_downtime_mins}/60)/24).intValue()) != 0]]></printWhenExpression> - </reportElement> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[((($F{unplanned_downtime_mins}/60)/24).intValue()).toString() + "d"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="425" y="0" width="21" height="18"> - <printWhenExpression><![CDATA[((($F{unplanned_downtime_mins}/60)/24).intValue()) != 0 || (($F{unplanned_downtime_mins}/60).intValue()-(((($F{unplanned_downtime_mins}/60)/24).intValue())*24)) != 0]]></printWhenExpression> - </reportElement> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[(($F{unplanned_downtime_mins}/60).intValue()-(((($F{unplanned_downtime_mins}/60)/24).intValue())*24)).toString() + "h"]]></textFieldExpression> - </textField> - <textField> - <reportElement x="446" y="0" width="24" height="18"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA[($F{unplanned_downtime_mins} - ((($F{unplanned_downtime_mins}/60).intValue()-(((($F{unplanned_downtime_mins}/60)/24).intValue())*24))*60)-(((($F{unplanned_downtime_mins}/60)/24).intValue())*60*24)).intValue().toString() + "m"]]></textFieldExpression> - </textField> - <textField pattern=""> - <reportElement x="470" y="0" width="40" height="18"/> - <textElement textAlignment="Right" verticalAlignment="Middle"> - <font fontName="SansSerif" isBold="false"/> - </textElement> - <textFieldExpression class="java.lang.String"><![CDATA["(" + (($F{unplanned_downtime_mins}/$F{total})*100).intValue().toString() + "%)"]]></textFieldExpression> - </textField> - </frame> - </band> - </detail> + cluster_id, + name, + delete_date +ORDER BY + ( + CASE + WHEN delete_date IS NULL + THEN 0 + ELSE 1 + END + ) ASC, + CAST ( + AVG ( uptime_mins ) + AS float + ) / + CAST ( + AVG ( total ) + AS float + ) DESC]]> + </queryString> + <field name="cluster_id" class="java.lang.Object"/> + <field name="name" class="java.lang.String"/> + <field name="delete_date" class="java.sql.Timestamp"/> + <field name="planned_downtime_mins" class="java.math.BigDecimal"/> + <field name="unplanned_downtime_mins" class="java.math.BigDecimal"/> + <field name="uptime_mins" class="java.math.BigDecimal"/> + <field name="total" class="java.math.BigDecimal"/> + <group name="Data Center"> + <groupExpression><![CDATA[$P{P_DataCenter_ID}]]></groupExpression> + <groupHeader> + <band height="46"> + <textField> + <reportElement mode="Transparent" x="0" y="0" width="510" height="20" forecolor="#3A5E75" uuid="3086f339-0ad9-4b0e-aafb-c3c71ce4adb5"/> + <textElement markup="none"> + <font fontName="SansSerif" size="13" isBold="true" isUnderline="false"/> + </textElement> + <textFieldExpression><![CDATA[$R{br7a.title}]]></textFieldExpression> + </textField> + <frame> + <reportElement mode="Opaque" x="0" y="20" width="510" height="26" backcolor="#D8E2FF" uuid="08954dd2-783b-49d8-bf3c-dcfcf4d8b3d7"/> + <textField> + <reportElement mode="Opaque" x="10" y="0" width="140" height="26" backcolor="#D8E2FF" uuid="e76789cc-b944-428f-9bb7-95a8a1434440"/> + <textElement verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br7a.table.cluster.name}]]></textFieldExpression> + </textField> + <textField> + <reportElement mode="Opaque" x="269" y="0" width="116" height="26" backcolor="#D8E2FF" uuid="bbbf7696-77a1-422b-ac66-7478e62b93d4"/> + <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br7a.table.planned.downtime}]]></textFieldExpression> + </textField> + <textField> + <reportElement mode="Opaque" x="388" y="0" width="122" height="26" backcolor="#D8E2FF" uuid="a602d981-1f16-46f8-afe4-9d86366e2c15"/> + <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br7a.table.unplanned.downtime}]]></textFieldExpression> + </textField> + <textField> + <reportElement mode="Opaque" x="150" y="0" width="117" height="26" backcolor="#D8E2FF" uuid="11af66dc-65c5-4820-80a4-6834a7a135c5"/> + <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="11" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br7a.table.uptime}]]></textFieldExpression> + </textField> + </frame> + </band> + </groupHeader> + <groupFooter> + <band height="16"> + <subreport> + <reportElement x="182" y="0" width="295" height="16" uuid="1b9c7284-7f3b-45a4-9c77-25d654984c16"/> + <subreportExpression><![CDATA["repo:report5.jrxml"]]></subreportExpression> + </subreport> + </band> + </groupFooter> + </group> + <group name="Cluster"> + <groupExpression><![CDATA[$F{cluster_id}.toString()]]></groupExpression> + </group> + <detail> + <band height="36" splitType="Stretch"> + <frame> + <reportElement style="Line Style" x="0" y="0" width="510" height="36" uuid="d8dd57bd-75c2-4cc2-908e-b35a7582d3ff"/> + <stackedBarChart> + <chart isShowLegend="false" evaluationTime="Group" evaluationGroup="Cluster" customizerClass="com.ovirt.reports.jasper.HorizontalStackedBarChart"> + <reportElement x="150" y="18" width="360" height="18" uuid="99a706ab-9a1e-4e36-86ed-26fcc8c10133"/> + <chartTitle/> + <chartSubtitle/> + <chartLegend/> + <anchorNameExpression><![CDATA["http://192.168.10.120:8080/jasperserver-pro/flow.html?_flowId=viewReportFlow&ParentFolderUri=%2FRHEV%2FReports%2FService_Level&ndefined=&standAlone=true&reportUnit=%2FRHEV%2FReports%2FService_Level%2FTest_002_1_1_1_1";]]></anchorNameExpression> + </chart> + <categoryDataset> + <dataset resetType="Group" resetGroup="Cluster"/> + <categorySeries> + <seriesExpression><![CDATA["Uptime"]]></seriesExpression> + <categoryExpression><![CDATA[$F{cluster_id}.toString()]]></categoryExpression> + <valueExpression><![CDATA[$F{uptime_mins}]]></valueExpression> + </categorySeries> + <categorySeries> + <seriesExpression><![CDATA["Planned Downtime"]]></seriesExpression> + <categoryExpression><![CDATA[$F{cluster_id}.toString()]]></categoryExpression> + <valueExpression><![CDATA[$F{planned_downtime_mins}]]></valueExpression> + </categorySeries> + <categorySeries> + <seriesExpression><![CDATA["Unplanned Downtime"]]></seriesExpression> + <categoryExpression><![CDATA[$F{cluster_id}.toString()]]></categoryExpression> + <valueExpression><![CDATA[$F{unplanned_downtime_mins}]]></valueExpression> + </categorySeries> + </categoryDataset> + <barPlot isShowLabels="false" isShowTickLabels="false" isShowTickMarks="false"> + <plot orientation="Horizontal"> + <seriesColor seriesOrder="0" color="#6EC000"/> + <seriesColor seriesOrder="1" color="#FFFF33"/> + <seriesColor seriesOrder="2" color="#FF0000"/> + </plot> + <itemLabel color="#000000" backgroundColor="#FFFFFF"/> + <categoryAxisFormat> + <axisFormat/> + </categoryAxisFormat> + <valueAxisFormat> + <axisFormat/> + </valueAxisFormat> + <rangeAxisMaxValueExpression><![CDATA[$F{planned_downtime_mins}+$F{unplanned_downtime_mins}+$F{uptime_mins}+1]]></rangeAxisMaxValueExpression> + </barPlot> + </stackedBarChart> + <textField hyperlinkType="ReportExecution" hyperlinkTarget="Blank"> + <reportElement x="10" y="0" width="140" height="36" uuid="e4884335-ff18-4a8c-9062-8f4aef139656"/> + <textElement verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression> + <hyperlinkParameter name="_report"> + <hyperlinkParameterExpression><![CDATA["/Reports/Service_level/Hosts/cluster_uptime_br7"]]></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[$F{cluster_id}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Host_Type"> + <hyperlinkParameterExpression><![CDATA[-1]]></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> + </textField> + <image> + <reportElement x="0" y="11" width="10" height="11" uuid="b5667938-a54b-48b5-b560-7dd756cc58cf"> + <printWhenExpression><![CDATA[$F{delete_date} != null]]></printWhenExpression> + </reportElement> + <imageExpression><![CDATA["repo:trash-icon"]]></imageExpression> + </image> + <textField> + <reportElement x="150" y="0" width="32" height="18" uuid="0b7c6fa9-3094-414c-bec3-bba7fbaf98ac"> + <printWhenExpression><![CDATA[((($F{uptime_mins}/60)/24).intValue()) != 0]]></printWhenExpression> + </reportElement> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[((($F{uptime_mins}/60)/24).intValue()).toString() + "d"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="182" y="0" width="21" height="18" uuid="56cc0e00-8ade-4878-b210-c29d4ef6de09"> + <printWhenExpression><![CDATA[((($F{uptime_mins}/60)/24).intValue()) != 0 || (($F{uptime_mins}/60).intValue()-(((($F{uptime_mins}/60)/24).intValue())*24)) != 0]]></printWhenExpression> + </reportElement> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[(($F{uptime_mins}/60).intValue()-(((($F{uptime_mins}/60)/24).intValue())*24)).toString() + "h"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="203" y="0" width="24" height="18" uuid="6c3c96ef-71e1-4219-bd33-082d20b9b063"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[($F{uptime_mins} - ((($F{uptime_mins}/60).intValue()-(((($F{uptime_mins}/60)/24).intValue())*24))*60)-(((($F{uptime_mins}/60)/24).intValue())*60*24)).intValue().toString() + "m"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="227" y="0" width="40" height="18" uuid="d653193d-74ee-4e6d-a8bc-4a038f163d91"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA["(" + (100-(($F{planned_downtime_mins}/$F{total})*100).intValue()-(($F{unplanned_downtime_mins}/$F{total})*100).intValue()).toString() + "%)"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="267" y="0" width="36" height="18" uuid="8e7cd00d-2fa1-4be2-9c19-b94ba548be03"> + <printWhenExpression><![CDATA[((($F{planned_downtime_mins}/60)/24).intValue()) != 0]]></printWhenExpression> + </reportElement> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[((($F{planned_downtime_mins}/60)/24).intValue()).toString() + "d"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="303" y="0" width="21" height="18" uuid="8d5e62f5-0d86-4572-864f-e66e4b518daa"> + <printWhenExpression><![CDATA[((($F{planned_downtime_mins}/60)/24).intValue()) != 0 || (($F{planned_downtime_mins}/60).intValue()-(((($F{planned_downtime_mins}/60)/24).intValue())*24)) != 0]]></printWhenExpression> + </reportElement> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[(($F{planned_downtime_mins}/60).intValue()-(((($F{planned_downtime_mins}/60)/24).intValue())*24)).toString() + "h"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="324" y="0" width="24" height="18" uuid="ff9227ab-4e77-464b-ac2a-584266f2800c"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[($F{planned_downtime_mins} - ((($F{planned_downtime_mins}/60).intValue()-(((($F{planned_downtime_mins}/60)/24).intValue())*24))*60)-(((($F{planned_downtime_mins}/60)/24).intValue())*60*24)).intValue().toString() + "m"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="348" y="0" width="40" height="18" uuid="9909bcd8-6902-45f8-beea-44460c139791"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA["(" + (($F{planned_downtime_mins}/$F{total})*100).intValue().toString() + "%)"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="388" y="0" width="37" height="18" uuid="176862cf-1318-4145-b8a6-9631556f0134"> + <printWhenExpression><![CDATA[((($F{unplanned_downtime_mins}/60)/24).intValue()) != 0]]></printWhenExpression> + </reportElement> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[((($F{unplanned_downtime_mins}/60)/24).intValue()).toString() + "d"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="425" y="0" width="21" height="18" uuid="e98775c8-7136-431a-acf2-76cedaf5dc6b"> + <printWhenExpression><![CDATA[((($F{unplanned_downtime_mins}/60)/24).intValue()) != 0 || (($F{unplanned_downtime_mins}/60).intValue()-(((($F{unplanned_downtime_mins}/60)/24).intValue())*24)) != 0]]></printWhenExpression> + </reportElement> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[(($F{unplanned_downtime_mins}/60).intValue()-(((($F{unplanned_downtime_mins}/60)/24).intValue())*24)).toString() + "h"]]></textFieldExpression> + </textField> + <textField> + <reportElement x="446" y="0" width="24" height="18" uuid="033ca771-eeb8-42f9-9292-0e24b0f50dc1"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif"/> + </textElement> + <textFieldExpression><![CDATA[($F{unplanned_downtime_mins} - ((($F{unplanned_downtime_mins}/60).intValue()-(((($F{unplanned_downtime_mins}/60)/24).intValue())*24))*60)-(((($F{unplanned_downtime_mins}/60)/24).intValue())*60*24)).intValue().toString() + "m"]]></textFieldExpression> + </textField> + <textField pattern=""> + <reportElement x="470" y="0" width="40" height="18" uuid="ca864cd3-66e1-481d-b208-500f95f027d0"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="SansSerif" isBold="false"/> + </textElement> + <textFieldExpression><![CDATA["(" + (($F{unplanned_downtime_mins}/$F{total})*100).intValue().toString() + "%)"]]></textFieldExpression> + </textField> + </frame> + </band> + </detail> </jasperReport> -- To view, visit http://gerrit.ovirt.org/27852 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ia7ec9c1ceb9b2a728173dae6ac4a66f80568b356 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
