Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » "Can't load the report query" at report beforeRender event??
"Can't load the report query" at report beforeRender event?? [message #671852] Thu, 19 May 2011 07:33
tong123123 tong123123 is currently offline tong123123 tong123123
Messages: 74
Registered: April 2011
Member
in my knowledge, dataset query is already executed before dataset afterOpen event. but now I found the following error message which seems that the report can't load the report query after dataset "afterOpen" and "onFetch" event!!
the debug message is added by me.
Quote:

unix dataset afterOpen
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
unix dataset onFetch
report afterFactory event
report beforeRender event
2011年5月19日 下午03:24:10 org.eclipse.birt.report.engine.data.dte.DataPresentationEngine doExecuteQuery
嚴重的: Can't load the report query
2011年5月19日 下午03:24:10 org.eclipse.birt.report.engine.data.dte.DataPresentationEngine doExecuteQuery
嚴重的: Can't load the report query


the rptdesign file is as follow: (the related dataset is "SingleServerUptime_Unix")
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.15" id="1">
    <property name="author">IBM Tivoli Common Reporting</property>
    <property name="createdBy">Eclipse BIRT Designer Version 2.2.1.r221_v20070924 Build &lt;2.2.0.v20070924-1550></property>
    <property name="units">in</property>
    <property name="includeResource">ITM/properties/itm</property>
    <text-property name="title">ITM: Resource Availability Single System</text-property>
    <property name="comments">Licensed Materials - Property of IBM
5724-Q87 
IBM Tivoli Reporting

(C) Copyright IBM Corp. 2008 All Rights Reserved.

US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with
IBM Corp.</property>
    <html-property name="description">This template contains a simple table layout following the Tivoli Common Reporting Guidelines.</html-property>
    <method name="initialize"><![CDATA[//
// Set up logging.  Uncomment next line to enable in Designer tool.
reportContext.setPersistentGlobalVariable("logfileName", "ITM_Resource_Availability_Heat_Chart.log");
setupLogging();
logInitialize();
// Add closeLogger(); to afterRender/afterFactory to close logger object.]]></method>
    <method name="beforeFactory"><![CDATA[params["Critical"].value = params["Bad"].value;]]></method>
    <method name="afterFactory"><![CDATA[Packages.java.lang.System.out.println("report afterFactory event");
closeLogger();]]></method>
    <method name="beforeRender"><![CDATA[Packages.java.lang.System.out.println("report beforeRender event");]]></method>
    <property name="theme">TivoliCommonReporting_v1.TCRTheme_v1</property>
    <text-property name="displayName">ITM: Resource Availability Single System</text-property>
    <list-property name="libraries">
        <structure>
            <property name="fileName">ITM/lib/itm.rptlibrary</property>
            <property name="namespace">itm</property>
        </structure>
        <structure>
            <property name="fileName">tcr_common/lib/TivoliCommonReporting_v1.0.rptlibrary</property>
            <property name="namespace">TivoliCommonReporting_v1</property>
        </structure>
    </list-property>
    <list-property name="includeScripts">
        <property>tcr_common/scripts/ReportUtils.js</property>
        <property>tcr_common/scripts/DateTime.js</property>
        <property>tcr_common/scripts/ModifyQuery.js</property>
        <property>tcr_common/scripts/Logger.js</property>
        <property>ITM/scripts/ITM_reportscripts.js</property>
    </list-property>
    <parameters>
        <cascading-parameter-group name="Resource Selection" id="10012310" extends="itm.Resource Selection">
            <property name="dataSetMode">multiple</property>
            <overridden-values>
                <ref-entry baseId="10004567" name="OSType" id="10004567"/>
                <ref-entry baseId="10004568" name="Server Name" id="10004568">
                    <property name="dataType">string</property>
                    <property name="valueType">dynamic</property>
                    <property name="concealValue">false</property>
                    <property name="isRequired">false</property>
                    <structure name="format">
                        <property name="category">Unformatted</property>
                    </structure>
                    <property name="dataSetName">ServerList</property>
                    <expression name="valueExpr">row["Servers_Name"]</expression>
                    <expression name="labelExpr">row["Servers_Name"]</expression>
                </ref-entry>
            </overridden-values>
        </cascading-parameter-group>
        <parameter-group name="Date Range" id="2891" extends="TivoliCommonReporting_v1.Date Range"/>
        <parameter-group name="Display Options" id="10004997">
            <text-property name="displayName" key="display_options_group_header"></text-property>
            <parameters>
                <scalar-parameter name="Attribute" id="2579">
                    <property name="hidden">false</property>
                    <property name="valueType">static</property>
                    <property name="dataType">string</property>
                    <property name="paramType">simple</property>
                    <text-property name="promptText" key="param_attribute"></text-property>
                    <property name="controlType">list-box</property>
                    <property name="mustMatch">true</property>
                    <property name="fixedOrder">true</property>
                    <property name="defaultValue">uptime</property>
                    <property name="distinct">true</property>
                    <structure name="format">
                        <property name="category">Unformatted</property>
                    </structure>
                    <list-property name="selectionList">
                        <structure>
                            <property name="value">uptime</property>
                            <property name="label">System Uptime</property>
                            <property name="labelID">attribute_uptime</property>
                        </structure>
                        <structure>
                            <property name="value">downtime</property>
                            <property name="label">System Downtime</property>
                            <property name="labelID">attribute_downtime</property>
                        </structure>
                    </list-property>
                </scalar-parameter>
            </parameters>
        </parameter-group>
        <parameter-group name="Thresholds" id="2515" extends="itm.Thresholds"/>
        <parameter-group name="Shift and Vacation Period Selection" id="10004845" extends="itm.Shift and Vacation Period Selection"/>
        <scalar-parameter name="Group_Name" id="10012390">
            <property name="hidden">true</property>
            <property name="valueType">static</property>
            <property name="dataType">string</property>
            <property name="paramType">simple</property>
            <property name="controlType">text-box</property>
            <property name="defaultValue">LBS</property>
            <property name="distinct">true</property>
            <structure name="format">
                <property name="category">Unformatted</property>
            </structure>
        </scalar-parameter>
    </parameters>
    <data-sources>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="ITM" id="10004698"
 extends="itm.ITM"/>
    </data-sources>
    <data-sets>
        <oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="SingleServerUptime_Windows" id="2884">
            <list-property name="computedColumns">
                <structure>
                    <property name="name">serverDate</property>
                    <expression name="expression">if(row["DATE"] != null)
{
yymmdd = "20" + row["DATE"].substr(0,2) + "/" + row["DATE"].substr(2,2) +  "/" + row["DATE"].substr(4,2);
s = yymmdd + " 00:00:00";
new Date(s);
}</expression>
                    <property name="dataType">date-time</property>
                </structure>
            </list-property>
            <list-property name="columnHints">
                <structure>
                    <property name="columnName">DATE</property>
                    <property name="displayName">DATE</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR1</property>
                    <property name="displayName">HOUR1</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR2</property>
                    <property name="displayName">HOUR2</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR3</property>
                    <property name="displayName">HOUR3</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR4</property>
                    <property name="displayName">HOUR4</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR5</property>
                    <property name="displayName">HOUR5</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR6</property>
                    <property name="displayName">HOUR6</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR7</property>
                    <property name="displayName">HOUR7</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR8</property>
                    <property name="displayName">HOUR8</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR9</property>
                    <property name="displayName">HOUR9</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR10</property>
                    <property name="displayName">HOUR10</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR11</property>
                    <property name="displayName">HOUR11</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR12</property>
                    <property name="displayName">HOUR12</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR13</property>
                    <property name="displayName">HOUR13</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR14</property>
                    <property name="displayName">HOUR14</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR15</property>
                    <property name="displayName">HOUR15</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR16</property>
                    <property name="displayName">HOUR16</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR17</property>
                    <property name="displayName">HOUR17</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR18</property>
                    <property name="displayName">HOUR18</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR19</property>
                    <property name="displayName">HOUR19</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR20</property>
                    <property name="displayName">HOUR20</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR21</property>
                    <property name="displayName">HOUR21</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR22</property>
                    <property name="displayName">HOUR22</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR23</property>
                    <property name="displayName">HOUR23</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR24</property>
                    <property name="displayName">HOUR24</property>
                </structure>
            </list-property>
            <list-property name="filter">
                <structure>
                    <property name="operator">ne</property>
                    <expression name="expr">row["DATE"]</expression>
                    <simple-property-list name="value1">
                        <value>null</value>
                    </simple-property-list>
                </structure>
            </list-property>
            <structure name="cachedMetaData">
                <list-property name="resultSet">
                    <structure>
                        <property name="position">1</property>
                        <property name="name">DATE</property>
                        <property name="dataType">string</property>
                    </structure>
                    <structure>
                        <property name="position">2</property>
                        <property name="name">HOUR1</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">3</property>
                        <property name="name">HOUR2</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">4</property>
                        <property name="name">HOUR3</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">5</property>
                        <property name="name">HOUR4</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">6</property>
                        <property name="name">HOUR5</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">7</property>
                        <property name="name">HOUR6</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">8</property>
                        <property name="name">HOUR7</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">9</property>
                        <property name="name">HOUR8</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">10</property>
                        <property name="name">HOUR9</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">11</property>
                        <property name="name">HOUR10</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">12</property>
                        <property name="name">HOUR11</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">13</property>
                        <property name="name">HOUR12</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">14</property>
                        <property name="name">HOUR13</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">15</property>
                        <property name="name">HOUR14</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">16</property>
                        <property name="name">HOUR15</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">17</property>
                        <property name="name">HOUR16</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">18</property>
                        <property name="name">HOUR17</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">19</property>
                        <property name="name">HOUR18</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">20</property>
                        <property name="name">HOUR19</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">21</property>
                        <property name="name">HOUR20</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">22</property>
                        <property name="name">HOUR21</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">23</property>
                        <property name="name">HOUR22</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">24</property>
                        <property name="name">HOUR23</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">25</property>
                        <property name="name">HOUR24</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">26</property>
                        <property name="name">serverDate</property>
                        <property name="dataType">date-time</property>
                    </structure>
                </list-property>
            </structure>
            <property name="dataSource">ITM</property>
            <method name="beforeOpen"><![CDATA[dsDebugLogger = ((""+typeof(debugLogger)) != "undefined" ) ? debugLogger : function(s) {}; 

try {
	var query = this.queryText;
	if(params["Attribute"].value == "downtime") {
	    this.queryText = query.replace("1.0*avg","100-1.0*avg");
		//var pattern = "(avg(\"TOT_";
		//pos = query.indexOf(pattern);
		//query = query.substr(0, pos) + "100-(avg(\"TOT_" + query.substr(pos + pattern.length);
		//this.queryText = query;
	}
	
	ModifyQueryCandleDateRangeFromGroupValues(this,params["Report Period"].value, params["Begin Date"].value, params["End Date"].value);
	this.queryText = modifyQueryBasedOnDBType(this.queryText,this.getDataSource().getExtensionProperty("odaDriverClass"));
	
	dsDebugLogger("Resource availability heat chart (Windows) " + this.queryText);
} catch(ex) {
	//if an exception occurs and reportContext.getReportRunnable fails, we are in preview mode. Set defaults accordingly
	try {
		var reportName = reportContext.getReportRunnable();
	} catch (ex1) {

	}
	
	dsDebugLogger("beforeOpen " + this.getDataSource().getName() + " : "  +ex);
}]]></method>
            <list-property name="parameters">
                <structure>
                    <property name="name">param_1</property>
                    <property name="paramName">Server Name</property>
                    <property name="dataType">any</property>
                    <property name="position">1</property>
                    <property name="isInput">true</property>
                    <property name="isOutput">false</property>
                </structure>
            </list-property>
            <list-property name="resultSet">
                <structure>
                    <property name="position">1</property>
                    <property name="name">DATE</property>
                    <property name="nativeName">DATE</property>
                    <property name="dataType">string</property>
                    <property name="nativeDataType">12</property>
                </structure>
                <structure>
                    <property name="position">2</property>
                    <property name="name">HOUR1</property>
                    <property name="nativeName">HOUR1</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">3</property>
                    <property name="name">HOUR2</property>
                    <property name="nativeName">HOUR2</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">4</property>
                    <property name="name">HOUR3</property>
                    <property name="nativeName">HOUR3</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">5</property>
                    <property name="name">HOUR4</property>
                    <property name="nativeName">HOUR4</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">6</property>
                    <property name="name">HOUR5</property>
                    <property name="nativeName">HOUR5</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">7</property>
                    <property name="name">HOUR6</property>
                    <property name="nativeName">HOUR6</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">8</property>
                    <property name="name">HOUR7</property>
                    <property name="nativeName">HOUR7</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">9</property>
                    <property name="name">HOUR8</property>
                    <property name="nativeName">HOUR8</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">10</property>
                    <property name="name">HOUR9</property>
                    <property name="nativeName">HOUR9</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">11</property>
                    <property name="name">HOUR10</property>
                    <property name="nativeName">HOUR10</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">12</property>
                    <property name="name">HOUR11</property>
                    <property name="nativeName">HOUR11</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">13</property>
                    <property name="name">HOUR12</property>
                    <property name="nativeName">HOUR12</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">14</property>
                    <property name="name">HOUR13</property>
                    <property name="nativeName">HOUR13</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">15</property>
                    <property name="name">HOUR14</property>
                    <property name="nativeName">HOUR14</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">16</property>
                    <property name="name">HOUR15</property>
                    <property name="nativeName">HOUR15</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">17</property>
                    <property name="name">HOUR16</property>
                    <property name="nativeName">HOUR16</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">18</property>
                    <property name="name">HOUR17</property>
                    <property name="nativeName">HOUR17</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">19</property>
                    <property name="name">HOUR18</property>
                    <property name="nativeName">HOUR18</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">20</property>
                    <property name="name">HOUR19</property>
                    <property name="nativeName">HOUR19</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">21</property>
                    <property name="name">HOUR20</property>
                    <property name="nativeName">HOUR20</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">22</property>
                    <property name="name">HOUR21</property>
                    <property name="nativeName">HOUR21</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">23</property>
                    <property name="name">HOUR22</property>
                    <property name="nativeName">HOUR22</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">24</property>
                    <property name="name">HOUR23</property>
                    <property name="nativeName">HOUR23</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
                <structure>
                    <property name="position">25</property>
                    <property name="name">HOUR24</property>
                    <property name="nativeName">HOUR24</property>
                    <property name="dataType">decimal</property>
                    <property name="nativeDataType">3</property>
                </structure>
            </list-property>
            <property name="queryText">with 
tempTable as
(select  substr("WRITETIME", 2, 6) as dt, 
         cast(substr("WRITETIME", 8, 2) as int) as hr,
         case when (1.0*avg("TOT_System_Up_Time")*100/3600) > 100 then 100
         	else (1.0*avg("TOT_System_Up_Time")*100/3600) 
         end as avgAttr
  from  "NT_System_HV" 
  where "Server_Name" = ?
   and   "WRITETIME" between '0000000000000000'
         and  
         '9999999999999999' 
         group by substr("WRITETIME", 8, 2), substr("WRITETIME", 2, 6) 
),

H1 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 0
),

H2 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 1
),

H3 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 2
),
H4 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 3
),
H5 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 4
),
H6 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 5
),
H7 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 6
),
H8 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 7
),
H9 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 8
),
H10 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 9
),
H11 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 10
),
H12 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 11
),
H13 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 12
),
H14 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 13
),
H15 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 14
),
H16 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 15
),
H17 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 16
),
H18 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 17
),
H19 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 18
),
H20 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 19
),
H21 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 20
),
H22 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 21
),
H23 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 22
),
H24 as 
(select dt,avgAttr from   tempTable
              where  tempTable.hr = 23
)

select H1.dt as "DATE",
        H1.avgAttr as "HOUR1",
        H2.avgAttr as "HOUR2",
        H3.avgAttr as "HOUR3",
        H4.avgAttr as "HOUR4",
H5.avgAttr as "HOUR5",
H6.avgAttr as "HOUR6",
H7.avgAttr as "HOUR7",
H8.avgAttr as "HOUR8",
H9.avgAttr as "HOUR9",
H10.avgAttr as "HOUR10",
H11.avgAttr as "HOUR11",
H12.avgAttr as "HOUR12",
H13.avgAttr as "HOUR13",
H14.avgAttr as "HOUR14",
H15.avgAttr as "HOUR15",
H16.avgAttr as "HOUR16",
H17.avgAttr as "HOUR17",
H18.avgAttr as "HOUR18",
H19.avgAttr as "HOUR19",
H20.avgAttr as "HOUR20",
H21.avgAttr as "HOUR21",
H22.avgAttr as "HOUR22",
H23.avgAttr as "HOUR23",
H24.avgAttr as "HOUR24"
from 
        H1 full outer join H2 on H1.dt = H2.dt 
           full outer join H3 on H1.dt = H3.dt
           full outer join H4 on H1.dt = H4.dt
		   full outer join H5 on H1.dt = H5.dt
full outer join H6
	on H1.dt = H6.dt
 full outer join H7
	on H1.dt = H7.dt
 full outer join H8
	on H1.dt = H8.dt
 full outer join H9
	on H1.dt = H9.dt
 full outer join H10
	on H1.dt = H10.dt
 full outer join H11
	on H1.dt = H11.dt
 full outer join H12
	on H1.dt = H12.dt
 full outer join H13
	on H1.dt = H13.dt
 full outer join H14
	on H1.dt = H14.dt
 full outer join H15
	on H1.dt = H15.dt
 full outer join H16
	on H1.dt = H16.dt
 full outer join H17
	on H1.dt = H17.dt
 full outer join H18
	on H1.dt = H18.dt
 full outer join H19
	on H1.dt = H19.dt
 full outer join H20
	on H1.dt = H20.dt
 full outer join H21
	on H1.dt = H21.dt
 full outer join H22
	on H1.dt = H22.dt
 full outer join H23
	on H1.dt = H23.dt
 full outer join H24
	on H1.dt = H24.dt           
</property>
        </oda-data-set>
        <oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="SingleServerUptime_Linux" id="10004695">
            <list-property name="computedColumns">
                <structure>
                    <property name="name">serverDate</property>
                    <expression name="expression">if(row["DATE"] != null)
{
yymmdd = "20" + row["DATE"].substr(0,2) + "/" + row["DATE"].substr(2,2) +  "/" + row["DATE"].substr(4,2);
s = yymmdd + " 00:00:00";
new Date(s);
}</expression>
                    <property name="dataType">date-time</property>
                </structure>
            </list-property>
            <list-property name="columnHints">
                <structure>
                    <property name="columnName">DATE</property>
                    <property name="displayName">DATE</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR1</property>
                    <property name="displayName">HOUR1</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR2</property>
                    <property name="displayName">HOUR2</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR3</property>
                    <property name="displayName">HOUR3</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR4</property>
                    <property name="displayName">HOUR4</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR5</property>
                    <property name="displayName">HOUR5</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR6</property>
                    <property name="displayName">HOUR6</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR7</property>
                    <property name="displayName">HOUR7</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR8</property>
                    <property name="displayName">HOUR8</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR9</property>
                    <property name="displayName">HOUR9</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR10</property>
                    <property name="displayName">HOUR10</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR11</property>
                    <property name="displayName">HOUR11</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR12</property>
                    <property name="displayName">HOUR12</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR13</property>
                    <property name="displayName">HOUR13</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR14</property>
                    <property name="displayName">HOUR14</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR15</property>
                    <property name="displayName">HOUR15</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR16</property>
                    <property name="displayName">HOUR16</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR17</property>
                    <property name="displayName">HOUR17</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR18</property>
                    <property name="displayName">HOUR18</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR19</property>
                    <property name="displayName">HOUR19</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR20</property>
                    <property name="displayName">HOUR20</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR21</property>
                    <property name="displayName">HOUR21</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR22</property>
                    <property name="displayName">HOUR22</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR23</property>
                    <property name="displayName">HOUR23</property>
                </structure>
                <structure>
                    <property name="columnName">HOUR24</property>
                    <property name="displayName">HOUR24</property>
                </structure>
            </list-property>
            <list-property name="filter">
                <structure>
                    <property name="operator">ne</property>
                    <expression name="expr">row["DATE"]</expression>
                    <simple-property-list name="value1">
                        <value>null</value>
                    </simple-property-list>
                </structure>
            </list-property>
            <structure name="cachedMetaData">
                <list-property name="resultSet">
                    <structure>
                        <property name="position">1</property>
                        <property name="name">DATE</property>
                        <property name="dataType">string</property>
                    </structure>
                    <structure>
                        <property name="position">2</property>
                        <property name="name">HOUR1</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">3</property>
                        <property name="name">HOUR2</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">4</property>
                        <property name="name">HOUR3</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">5</property>
                        <property name="name">HOUR4</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">6</property>
                        <property name="name">HOUR5</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">7</property>
                        <property name="name">HOUR6</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">8</property>
                        <property name="name">HOUR7</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">9</property>
                        <property name="name">HOUR8</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">10</property>
                        <property name="name">HOUR9</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">11</property>
                        <property name="name">HOUR10</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">12</property>
                        <property name="name">HOUR11</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">13</property>
                        <property name="name">HOUR12</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">14</property>
                        <property name="name">HOUR13</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">15</property>
                        <property name="name">HOUR14</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">16</property>
                        <property name="name">HOUR15</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">17</property>
                        <property name="name">HOUR16</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">18</property>
                        <property name="name">HOUR17</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">19</property>
                        <property name="name">HOUR18</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">20</property>
                        <property name="name">HOUR19</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">21</property>
                        <property name="name">HOUR20</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">22</property>
                        <property name="name">HOUR21</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">23</property>
                        <property name="name">HOUR22</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">24</property>
                        <property name="name">HOUR23</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">25</property>
                        <property name="name">HOUR24</property>
                        <property name="dataType">decimal</property>
                    </structure>
                    <structure>
                        <property name="position">26</property>
                        <property name="name">serverDate</property>
                        <property name="dataType">date-time</property>
                    </structure>
                </list-property>
            </structure>
            <property name="dataSource">ITM</property>
            <method name="beforeOpen"><![CDATA[dsDebugLogger = ((""+typeof(debugLogger)) != "undefined" ) ? debugLogger : function(s) {}; 

try {
	var query = this.queryText;
	if(params["Attribute"].value == "downtime") {
	    this.queryText = query.replace("1.0*avg","100-1.0*avg");
		//var pattern = "(avg(\"TOT_";
		//pos = query.indexOf(pattern);
		//query = query.substr(0, pos) + "100-(avg(\"TOT_" + query.substr(pos + pattern.length);
		//this.queryText = query;
	}
	
	ModifyQueryCandleDateRangeFromGroupValues(this,params["Report Period"].value, params["Begin Date"].value, params["End Date"].value);
	this.queryText = modifyQueryBasedOnDBType(this.queryText,this.getDataSource().getExtensionProperty("odaDriverClass"));
	dsDebugLogger("Resource availability heat chart (Windows) " + this.queryText);
} catch(ex) {
	//if an exception occurs and reportContext.getReportRunnable fails, we are in preview mode. Set defaults accordingly
	try {
		var reportName = reportContext.getReportRunnable();
	} catch (ex1) {

	}
	
	dsDebugLogger("beforeOpen " + this.getDataSource().getName() + " : "  +ex);
}]]></method>
            <list-property name="parameters">
                <structure>
                    <property name="name">param_1</property>
                    <property name="paramName">Server Name</property>
                    <property name="nativeName"></property>
                    <property name="dataType">string</property>
                    <property name="nativeDataType">12</property>
                    <property name="position">1</property>
                    <property name="isOptional">true</property>
                    <property name="allowNull">true</property>
                    <property name="isInput">true</property>
         



Previous Topic:Inserting a chart in between report elements
Next Topic:Help with BIRT reports (Maximo)
Goto Forum:
  


Current Time: Tue Oct 21 05:41:53 GMT 2014

Powered by FUDForum. Page generated in 0.22603 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software