nanako Posted August 7, 2007 Share Posted August 7, 2007 I'll be really greatful if anyone can give me a hint.My project deadline is very close, I tried various way but still cannt solve the prolbem. The question is as follows: I intend to generate a table and 4 charts in a table.I used 5 different query, one for the table and 4 for the charts. All the queries take parameters which corresponds to the $F{order_date} field as follows: And it seems to me as all the parmeters corresponds to $F{order_date}, that's something wrong, I guess. ====Chart===== <subDataset name="history_perf"> <parameter name="startdate" class="java.util.Date"/> <parameter name="enddate" class="java.util.Date"/> <queryString> <![CDATA[select perf_bps,order_date,exchange from daily_perf where algorithm_name='GSAT_VWAP' and order_date>=$P{startdate} and order_date<=$P{enddate} group by order_date ]]> </queryString> <field name="exchange" class="java.lang.String" /> <field name="perf_bps" class="java.lang.Double"/> <field name="order_date" class="java.util.Date"/> <group name="exchangeGroup"> <groupExpression> <![CDATA[$F{exchange}]]> </groupExpression> </group> </subDataset> <timeSeriesChart> <chart evaluationTime="Report"> <reportElement mode="Opaque" x="0" y="14" width="750" height="175" /> </chart> <timeSeriesDataset> <dataset> <datasetRun subDataset="history_perf"> <datasetParameter name="startdate"> <datasetParameterExpression> <![CDATA[$F{order_date}]]> </datasetParameterExpression> </datasetParameter> <datasetParameter name="enddate"> <datasetParameterExpression> <![CDATA[$F{order_date}]]> </datasetParameterExpression> </datasetParameter> </datasetRun> </dataset> <timeSeries> <seriesExpression>$F{exchange}</seriesExpression> <timePeriodExpression>$F{order_date}</timePeriodExpression> <valueExpression>$F{perf_bps}</valueExpression> <labelExpression></labelExpression> </timeSeries> </timeSeriesDataset> <timeSeriesPlot> <plot/></timeSeriesPlot> </timeSeriesChart> =======table query <parameter name="orderdate" class="java.util.Date"/> <queryString> <![CDATA[select algorithm_name, exchange, order_date, order_count, quantity, value, perf_bps, perf_bps_stddev, duration from daily_perf where order_date=$P{orderdate} order by exchange]]> </queryString> Link to comment Share on other sites More sharing options...
takashi Posted August 7, 2007 Share Posted August 7, 2007 I have not validated, but I can see this is the problem. <dataset><datasetRun subDataset="history_perf"><datasetParameter name="startdate"><datasetParameterExpression><![CDATA[$F{order_date}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="enddate"><datasetParameterExpression><![CDATA[$F{order_date}]]></datasetParameterExpression> </datasetParameter> </datasetRun></dataset> Change to something else. Hope it works. Link to comment Share on other sites More sharing options...
nanako Posted August 7, 2007 Author Share Posted August 7, 2007 I think that's the problem too. But I need to desginate the startdate and enddate of data to display to the chart, which corresponds to the field $F{order_date} And the query is <subDataset name="history_perf"> <parameter name="startdate" class="java.util.Date"/> <parameter name="enddate" class="java.util.Date"/> <queryString> <![CDATA[select perf_bps,order_date,exchange from daily_perf where algorithm_name='GSAT_VWAP' and order_date>=$P{startdate} and order_date<=$P{enddate} group by order_date ]]> </queryString> <field name="exchange" class="java.lang.String" /> <field name="perf_bps" class="java.lang.Double"/> <field name="order_date" class="java.util.Date"/> <group name="exchangeGroup"> <groupExpression> <![CDATA[$F{exchange}]]> </groupExpression> </group> </subDataset> ====How can I change, thanks~~ Link to comment Share on other sites More sharing options...
takashi Posted August 7, 2007 Share Posted August 7, 2007 I am not a big helper, but I would like to know when you execute a sql query via JasperReports, how is your SQL server receiving. select perf_bps,order_date,exchange from daily_perfwhere algorithm_name='GSAT_VWAP' and order_date>=$P{startdate}and order_date<=$P{enddate}group by order_date Link to comment Share on other sites More sharing options...
nanako Posted August 7, 2007 Author Share Posted August 7, 2007 Thanks Takashi. For the below query, currently there is no data displayed in the chart, i.e. i get an empty chart======================select perf_bps,order_date,exchange from daily_perfwhere algorithm_name='GSAT_VWAP' and order_date>=$P{startdate}and order_date<=$P{enddate}group by order_date <timeSeriesDataset> <dataset><datasetRun subDataset="history_perf"><datasetParameter name="startdate"><datasetParameterExpression><![CDATA[$F{order_date}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="enddate"><datasetParameterExpression><![CDATA[$F{order_date}]]></datasetParameterExpression> </datasetParameter> </datasetRun></dataset> ===Bur if I change the sql toselect perf_bps,order_date,exchange from daily_perfwhere algorithm_name='GSAT_VWAP' and order_date>=$P{startdate}group by order_date ===or select perf_bps,order_date,exchange from daily_perfwhere algorithm_name='GSAT_VWAP' and order_date<=$P{enddate}group by order_date The chart is not empty anymore, but the problem is both $P{startdate} $P{enddate} takes the value of the parameter $P{orderdate} which should be only used in the table query as follows: select algorithm_name, exchange, order_date, order_count, quantity, value, perf_bps, perf_bps_stddev, duration from daily_perf where order_date=$P{orderdate} order by exchange Link to comment Share on other sites More sharing options...
takashi Posted August 7, 2007 Share Posted August 7, 2007 I am not sure the problem is. It looks fine to me. Is there possible that you can turn debug on or log your sql server? If you use mysql, modify your my.cnf:[mysqld]log=/tmp/sql.logthen restart. Link to comment Share on other sites More sharing options...
nanako Posted August 7, 2007 Author Share Posted August 7, 2007 sorry, foget to mention. I'm using the sybase database. Any idea as how to do with sybase. Thanks Nanako Link to comment Share on other sites More sharing options...
nanako Posted August 7, 2007 Author Share Posted August 7, 2007 up~up~~ Really grateful anyone can help me out. It's too weird. I cann't find anything wrong. Thanks Ivy Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now