Pass parameter to subdataset query (for crosstab)

[chaddn]

I am using iReport 3.0.0

I have a crosstab report that I need to pass a parameter to (customer id).  Basically I want the user to be able to type in a customer ID and that value will be used in the query for the subdataset that drives the crosstab. 

I looked at a few threads I found on here, but they don't fully explain how to do this.  From what I've read, I need to setup a parameter in my main report {feid_main} and use that to prompt the user for the customer ID value.  Then I setup a parameter in the subdataset {feid} and set the default value expression to the main parameter $P{feid_main}.

This doesn't work.  I get an "Error filling print...Error preparing statement for executing the report query".  It returns the query and where the value from my parameter entry should be, there is instead a "?".  Unfortunately it seems the subdataset cannot access the parameters in the main report, so no value is passed on.  Am I missing a step here, or is there a better way to do this?  My jrxml is attached..

[kchaudhry]

Try the following.

1. Create a parameter in the main report feid_main and set it to prompt.

2. Now go to your subdata set and create a parameter feid_sub and set it to prompt. You don't have to give it a default value here. Just leave that blank. Just make sure you are referencing your parameter in the query.

3. Now right click on your cross tab and go to cross tab properties, towards the bottom you'll see the "Dataset run". Click on the add button to add a parameter and enter feid_sub for the name and $P{feid_main} for the default value.

That's all you need to do. Hope this helps!

[chaddn]

OK, I tried this and it still doesn't seem to pass the prompted value to my subdataset query.  It shows a ? where my $P{feid_sub} is in the query.  There's a txt file attached with a paste from the output console, I'm just trying the preview from within iReport, using the built-in viewer.  If I enter a valid value in the query instead of the $P{feid_sub}, the query works as expected.  So I know the query is good.  Latest jrxml is also attached.

 

Any ideas why the param isn't getting passed?

 

Thanks for your help!

 

Chadd

[schultcd]

I needed something similar (wasn't working with a separate dataset) and I found out how to pass parameters to my crosstabs using the JRXML (use a element). Once I re-loaded the report in iReport, it was clear how to create a without hand-editing the XML:

 

1. Go to Main Report view

2. Right-click on the crosstab and choose "Properties" (/not/ "Crosstab properties")

3. Go to the "Crosstab" tab

 

Here you can see your crosstab parameters. Click "Add" to add a new one.

 

"Parameter Name" is the name of the parameter /within/ the crosstab. Type is obvious. "Value Expression" is evaluated in the main report context, so you can use the same name for the crosstab parameter as the main report parameter (like you can define my_custom_variable to have a value of $P{my_custom_variable}).

 

This might help you with your subdataset stuff.

[chaddn]

I tried the last suggestion of adding the crosstab parameter (under properties, not crosstab properties) and I still have the same issue.  It still passes no value to my subdataset query.

 

Is there a way to use the main report query for the dataset in the crosstab, instead of a subdataset?

[chaddn]

OK, I got it to work using the instructions from the first reply in this thread.  It seems that the Ingres JDBC driver was having a problem with using a parameter, and grouping in the same query by a partial field value -left(datumrg,4)-...Very obscure, strange issue.  So I got around it by not grouping and having the crosstab do the totaling for me.

 

Thanks for all your help guys!

 

Chadd