Pass main dataset parameter to subdataset query

4

Hi,

I need to use a parameter from the report main dataset in a subdataset query. Can it be done?

if so how?

 

Situation:

report1:

dataset

- parameters: MyMainParam="hello"

sub dataset: MySubDataset

- query: select * from myTable where field1=$P{MyMainParam}

 

This does not work.

Error:  net.sf.jasperreports.engine.design.JRValidationException: Report design not valid :       1. Query parameter not found : MyMainParam

 

Francois

digifrancois's picture
Joined: Aug 4 2011 - 7:01am
Last seen: 3 months 1 week ago

9 Answers:

0

Hi digifrancois,

If you want to use parameters in your queries the notaton is the following: $P!{parameterName}

So your query should be this one:

select * from myTable where field1=$P!{MyMainParam}

Depending on the data type of 'field1' you may need to use quotes:

select * from myTable where field1='$P!{MyMainParam}'

Hope this helps.

Regards.

augarte's picture
511
Joined: Jan 27 2010 - 7:20am
Last seen: 1 year 1 month ago
0

Hi.

Thanks for the quick response!

However even with the $P!{MyMainParam} or with '$P!{MyMainParam}' in the subdataset query, it makes no difference. I still get the same error.

I have attached the jrxml file and error file.

As you can see in the jrxml, the same query does work in the main dataset.

Product Version: Jaspersoft iReport Designer 4.5.0
Java: 1.6.0_27; Java HotSpot(TM) Client VM 20.2-b06
System: Windows XP version 5.1 running on x86; Cp1252; nl_BE (ireport)

Any help is appreciated.



Post Edited by digifrancois at 02/27/2012 12:44
digifrancois's picture
Joined: Aug 4 2011 - 7:01am
Last seen: 3 months 1 week ago
0

Hi,

After checking the jrxml file I see that the problem is that the parameter is not defined in your SubDataSet. If you define a new MyMainParameter in your subdataset you won't get this error any more. The problem you may have is that maybe it won't get the value it has in the Main dataset. I'm sure you need to define the parameter in the subdataset but I actually don't know how can you do it to take the same value it has in the main subdataset.

Regards.

augarte's picture
511
Joined: Jan 27 2010 - 7:20am
Last seen: 1 year 1 month ago
0

Hello,

OK, I added an parameter to the subdataset with the same name and type. I added a table just to verify the result.
The report runs ok, now the error is gone, but the subdataset is empty. Probably because it used the parameter from the subdatset and not the one from the main report.

If I open the query editor and push "read fields" an error appears in the fields pane: "Error: Please set a value for the parameter 'MyMainParam'.
If I push "Refresh Preview Data" the result set is empty.

BTW: the result set from the main query is not empty. And i f I remove the where clause from the sudataset query the table is filled.

Best regards
 



Post Edited by digifrancois at 02/27/2012 14:20
digifrancois's picture
Joined: Aug 4 2011 - 7:01am
Last seen: 3 months 1 week ago
6

I think that the two questions I see on this board the most are how to pass a value from a report to a subreport and how to pass a value back.  In your case, you want the subreport to use the same parameter $P{MyMainParam} that the main report has access to.  Since you've already defined the parameter in the subreport, the next step is to pass the value:  go into your main report in iReport, click on the subreport element and scroll to the bottom of the Properties pane.  Where you see the "Parameters" property, click on the "..." button and it pulls up a Parameters dialog box.  Click the "Add" button to add a new parameter value to pass to the subreport.  For Subreport parameter name, I don't believe you need to use the $P{} syntax, since this dialog presumes that the first text box is the name of a parameter.  For the "Value expression" text box, you can enter any combination of field, parameter, variable, or function from the main report.  In this case, it sounds like you have a parameter with exactly the same name in the main report, so you would use $P{MyMainParam} for the Value expression (since the value expression is not assumed to just be another parameter (even though it is in our example), you would need to use the $P{} syntax for this second text box.

Hope that helps!

Carl

cbarlow3's picture
1387
Joined: Mar 4 2010 - 8:59am
Last seen: 6 days 2 hours ago
1

Thanks Carl,

Well, actually I am not creating a subreport (yet) only a subdataset ("add dataset" from the main report context menu).

I only created the subdataset parameter with the same name because it was suggest that could be a solution.

When I create/modify a subdataset I do not see any of the parameters mapping options I have when modifying a subreport (or chart,crosstab data).

I am just trying to create a query based on data from the main report, in this case for simplicity from a parameter. Even before attaching any report object to the subdataset.

Any suggestions?

Francois

digifrancois's picture
Joined: Aug 4 2011 - 7:01am
Last seen: 3 months 1 week ago
6

Absolutely...actually, I use subdatasets all the time and almost never use subreports, all though many of the concepts are similar.  In this case, your subdataset is being used by a Table element.  By working with the subdataset, you can define the subdataset query and define parameters, fields, and variables for the subdataset, but that's not where you define the interaction between the main dataset and the subdata set.  THAT is done in the Table (or List) element itself.  In the tree, right click on "Table" and choose "Edit table datasource".  On the first tab of that dialog box, it looks like you've already correctly linked the table to MySubDataset and said to "Use connection expression" $P{REPORT_CONNECTION}.  If you click on the Parameters tab in that same dialog box, you will see that you do not yet have any definition set up for how to pass values from the main report to the subdataset parameters.

Click Add.  Since you already have the parameter defined in your subdataset, you can just choose that parameter from the dropdown list for "Dataset parameter name".  Remember, this is the name of the parameter in your subdataset.  Then click the expression editor icon for the "Value expression" text box.  Like most expressions, you can enter anything you want here, but if you feel like just clicking instead of typing in this case, you are looking at parameters, fields, and variables that are currently defined in your main dataset.  Select Parameters, and you should see in the middle column that your main dataset also (in this example) has a parameter called MyMainParam.  Select that and click the various OK buttons.

Carl



Post Edited by cbarlow3 at 02/27/2012 17:12
cbarlow3's picture
1387
Joined: Mar 4 2010 - 8:59am
Last seen: 6 days 2 hours ago
1

Hi Carl,

Thanks for that clarification. I've got it working with the table element. (I've changed the name of the subdataset parameter to make it clear which one is used)

But I have some questions:

Am I right in thinking, that: a subdataset is evaluated/executed each time anew when different report elements make use of it?
Each report element that uses that subdataset can have the sub parameter associated with a different one from the main report ?
And as such, that is why I can not evaluate the subdataset in the query editor if I need to use parameters from the main report?

I've attached the working jrxml.

And Thanks again!

Francois
 

 

digifrancois's picture
Joined: Aug 4 2011 - 7:01am
Last seen: 3 months 1 week ago
0

Hi,

I'm using iReport 5.6.0 version , and I  try to use Crosstab based on dataset, with parameter. but it doesn't work.

so , i need to pass a parametrer from main report to dataset;

- I create the same parameter with the same name  in  the main report parameters, and dataset pameters (P_NO_PROJET)

-  In the Crosstab Data (when i right clic on the crosstab on the report ) , i put  :

   Connection/Datasource exp (Tab): $P{REPORT_CONNECTION}

   Parameters (Tab):    Parameters: P_NO_PROJET   (wich is the dataset parameter) 

   Expression : $P{P_NO_PROJET}                            (wich is the main prameter)

TheQuery of dataset:

select null,
description,
annee
from bddpi_ligne_temps
WHERE NO_PROJECT = $P{P_NO_PROJET}
 AND ANNEE IS NOT NULL
order by annee  ASC NULLS LAST

 

 

 

fouzia_tighersine's picture
Joined: Oct 4 2014 - 4:27pm
Last seen: 4 years 1 month ago
Feedback
randomness