Unknown parameter xxxx  in sub dataset

[jaumeg]

HI All !

 

I'm having some problems when I try to create a SubDataset in order to fill a ChartPie.

 

I'm Using iReport 1.2.5

 

I have been declared a SubDataset1 with a query like this:

 

$P!{sql_query2}

 

Same as Main (sql_query1) because my program change some SQL parameters at runtime before it runs..

 

As in Main I Create the:

Parameter --> sql_query2

Field : quantity

Field : descripcion

 

I create then the ChartPie3D and in the Dataset RUn I use the SubDataset created in my last Step and I ADD the fields (quantity and descripcion).

 

This Two fields are ADDED to the CharData Subdatset Parameters

 

In parameters Map Ex and COnnection are in Blank because I nuse a Java program that pass the COnnection as a Parameter (Like in Main).

 

Then in Details I put the parameters as is:

 

KeyExpression :

$F{descripcion}

 

Value Expression:

$F{quantity}

 

Label Expression:

$F{descripcion}

 

---------------------------------

The Problem:

 

When I compile the jrxml I get all the times the message :

net.sf.jasperreports.engine.JRException: Unknown parameter quantity in sub dataset SubDataset1

 

What I'm doing Wrong ?

My Main Report run always fine but when I use SubDatas with to fill a simple chart :S

 

Thank you in advance for your Help

[lucianc]

Can you post your JRXML so that we can see what causes the error?

 

Regards,

Lucian

[jaumeg]

Thank you Lucianc for your help,

 

I have been updating Jasper to 1.2.7 and This problem is solved.

 

I have another one that perhaps you can help:

 

I'm Putting a subdataset for making a simple PieChart.

 

I declare the SQL in SUbdataset as this:

 

 

(book.diardate BETWEEN '2006/10/18' AND '2006/10/18') AND

 

cstaapus.apuscode = '$P{apuscode}' AND

 

agen.agenname = '$P{agenda}' AND

 

 

 

But when I run my program I get an exception like this:<br>

 

Error preparing statement for executing the report query :

 

error preparing statement for executing the query

 

 

 

 

('P','D') AND

 

(book.diardate BETWEEN '2006/10/18' AND '2006/10/18') AND

 

cstaapus.apuscode = '?' AND

 

agen.agenname = '?' AND

 

Look at my program

 

parameters.put("sql_query", query); parameters.put("sql_query2", query2); parameters.put("agenda",agenda); parameters.put("apuscode",apuscode); //Preparacion del reporte (en esta etapa se inserta el valor del query en el reporte). JasperPrint jasperPrint=JasperFillManager.fillReport(fileName, parameters, con3);

 

 

AnyWay, If I try to put $P!{sql_query2} in the SubQuery I get an Exception as well (null) and I have the sql_query parameter full.

 

 

 

 

 

 

 

Can I use the $P!{sql_query2} in SubDatasets as in MAIN ?

 

 

 

 

 

[file name=agendaUsuarioChart.jrxml size=21600]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/agendaUsuarioChart.jrxml[/file]

 

 

 

 

This is the JRXML when using $P!{sql_query2}

 

 

 

And this is the Exception:

 

Error preparing statement for executing the report query :

 

null

Post edited by: jaumeg, at: 2006/10/18 13:58

[lucianc]

Yes, you can use $P!{param} in subdataset queries, just make sure you send a value for the subdataset parameter.

The syntax for "regular" query parameters is cstaapus.apuscode = $P{apuscode} and not cstaapus.apuscode = '$P{apuscode}'

[/ul]

 

HTH,

Lucian

[jaumeg]

Thank for your reponse:

 

Look at this when I use $P!{sql_query2}

 

 

Error preparing statement for executing the report query :

 

null

 

 

 

Thanks ! [file name=agendaUsuarioChart-1020df0efa27da9bf2f4c31e8a2745a0.jrxml size=20132]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/agendaUsuarioChart-1020df0efa27da9bf2f4c31e8a2745a0.jrxml[/file]

[jaumeg]

Thank for your reponse:

 

Look at this when I use $P!{sql_query2}

 

 

Error preparing statement for executing the report query :

 

null

 

 

 

Thanks ! [file name=agendaUsuarioChart-24ae333077f3d3ef6c440e3b7ec17546.jrxml size=21765]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/agendaUsuarioChart-24ae333077f3d3ef6c440e3b7ec17546.jrxml[/file]

[lucianc]

As I was saying

.. just make sure you send a value for the subdataset parameter

 

You don't set any value for the "sql_query2" subdataset parameter. You need something like

Code:

<datasetRun subDataset="SubDataset1">
  <datasetParameter name="sql_query2">
    <datasetParameterExpression>.. sql string for subdataset ..</datasetParameterExpression>
  </datasetParameter>
</datasetRun>

 

HTH,

Lucian

[jaumeg]

HI Lucianc !

 

Sorry but I can't not execute a subDataset using the query:

 

$P!{sql_queryb}

 

 

in my program I'm putting this Parameter in Hashtable same as the sql_query that uses the MAIN.

 

Please if I need to make sure you send a value for the subdataset parameter Can you say me what I'm missing in Ireports 1.2.7 or in My Java program ?

 

I'm sure I put the SQL Statement fine when I execute the Java program:

 

parameters.put("sql_query", query); parameters.put("sql_queryb", query2); parameters.put("agenda",agenda); parameters.put("apuscode",apuscode); parameters.put("fecha_desde",Util.convierteFechaToSql(fechadesde)); parameters.put("fecha_hasta",Util.convierteFechaToSql(fechahasta)); JasperPrint jasperPrint=JasperFillManager.fillReport(fileName, parameters, con3);

 

This is my JRXML generated by 1.2.7

 

 

 

 

What I'm doing wrong please ?

 

Thanks.

Jaume

 

 

[file name=agendaUsuarioChart-98b3e7cccdc91f6000594e24d5009cc5.jrxml size=20298]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/agendaUsuarioChart-98b3e7cccdc91f6000594e24d5009cc5.jrxml[/file]

 

Anyway what I have to put here inside the datasetParameterExpression if I'm using a

$P!{sql_queryb} ?

 

 

 

 

 

 

<datasetRun subDataset="SubDataset1">

<datasetParameter name="sql_query2">

<datasetParameterExpression>

 

?????????

 

</datasetParameterExpression>

</datasetParameter>

</datasetRun>

 

 

Sorry I'm lost..

Post edited by: jaumeg, at: 2006/10/18 14:51

[jaumeg]

Well I think there is a BUG in 1.2.7 because if I use any parameter The system gets an error.

 

 

Here is My java Code and I can confirm that all parameters are not Empty:

 

Code:

        parameters.put("sql_query", query);
        parameters.put("sql_queryb", query2);
        parameters.put("agenda",agenda);
        parameters.put("apuscode",apuscode);
        parameters.put("fecha_desde",Util.convierteFechaToSql(fechadesde));
        parameters.put("fecha_hasta",Util.convierteFechaToSql(fechahasta));
        

 

 

 

 

and I send you the JRXM in this case I use the SQL inside the Query with parameters and I get the Java Exception :

 

 

Null 'key' argument.

 

 

Thanks

[file name=agendaUsuarioChart-21c692dd34566d4f623f095aec77e81d.jrxml size=22779]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/agendaUsuarioChart-21c692dd34566d4f623f095aec77e81d.jrxml[/file]

[lucianc]

The parameters map can be used to set values for (main) report parameters only. The parameters of a subdataset form a different realm (similar to a subreport), and you can only set values for the subdataset parameters in the <datasetRun> definition.

 

Therefore you need to do the following:

Define a report in the report:

Code:

<parameter name="subdatasetQuery" isForPrompting="false"/>

Set its value when filling the report

Code:

[code] params.put("subdatasetQuery", subdatasetSQLString);

Create a parameter in the subdataset

Code:

[code] <subDataset name="SubDataset1"> .. <parameter name="query"/> </subDataset>

Send the value for the subdataset parameter when you instantiate the subdataset

Code:

[code] <datasetRun subDataset="SubDataset1"> <datasetParameter name="query"> <datasetParameterExpression>$P{subdatasetQuery}</datasetParameterExpression> <datasetParameter> </datasetRun>

[/ol]

 

Regarding the bug, can you post the full exception stack trace and the lines from your source code where the exception occurred?

 

HTH,

Lucian