subdataset parameters

[nanako]

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>

[takashi]

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.

[nanako]

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~~

[takashi]

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_perf where algorithm_name='GSAT_VWAP' and order_date>=$P{startdate} and order_date<=$P{enddate} group by order_date

[nanako]

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_perf

where 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 to

select perf_bps,order_date,exchange from daily_perf

where algorithm_name='GSAT_VWAP'

and order_date>=$P{startdate}

group by order_date

 

===or

select perf_bps,order_date,exchange from daily_perf

where 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

[takashi]

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.log

then restart.

[nanako]

sorry, foget to mention.

 

I'm using the sybase database.

 

Any idea as how to do with sybase.

 

Thanks Nanako

[nanako]

up~up~~

 

 

Really grateful anyone can help me out.

 

It's too weird. I cann't find anything wrong.

 

 

Thanks Ivy