dynamic subreport

[bogdan]

I have a question regarding subreports.

 

I have two tables: clients and products.

Products has info about every client, and what they bought.

 

Ex:

Clients:

Id: 1

Name: A

Id: 2

Name: B

 

Products:

Id: 1

ClientID: 1

Product: Mouse

Id: 2

ClientID: 1

Product: Keyboard

Id: 3

ClientID: 2

Product: Toaster

 

I want in the Report to display the ID and Name of every client.

In the Subreport I want to display what they bought.

 

ex:

1 A

Mouse

Keyboard

--------

2 B

Toaster

--------

 

Now, I don't want the query to be in the .jrxml file. I want to be able to use the query from the programm.

From the .jrxml file it works fine and with no problems. My problem is, like I've said, to be able to give the query from the Java programm I wrote. (for the report and for the subreport).

 

How can I do this ?

Is it possible?

[rsilverns.sympatico.ca]

So to summarize the DB structure...

 

Clients:

CID | Name

1 | Joe

2 | Frank

3 | Bob

 

Products:

PID | ClientID | ProductDescr

1 | 1 | Toaster

2 | 1 | Kettle

3 | 2 | David Hassellhoff CD (It could happen!)

 

There are 2 ways i see to address this problem...

 

1. Don't use a sub-report in this instance but combine both tables using a join SQl statement and use groups in 1 main report.

i.e. SELECT Clients.ID, Clients.Name, Products.ProductDescr FROM Products, Clients WHERE Clients.ID = Products.ClientID.

 

Note: this sql is off top of my head so be sure to double check it. :)

 

So now your result set would be....

 

ID, Name, ProductDescr

 

So group by ID and Name and then list each product descr in the detail band for what they bought...

 

example output:

 

Purchaser ID: 1

Purchaser Name: Frank

------------------------------- (detail band start)

* Toaster

* David Hassellhoff CD (oh the shame!)

 

 

2. The second approach is to pass the sub-report the main datasource as a parameter. But this means embedding the query into the jrxml file for the main and sub-report. In this case, the datasource would be a Connection object. Then you would parameterize the sub-report query by client ID... something like SELECT ProductDescr from Products where ClientID=$P!{CLIENT_ID_PARAM}

[bogdan]

Hello,

 

I do not want an embeded query. I wanted to know if it can be done without the query being embeded in the report.

 

A complex select is not the answer here.

Do you / anyone know if this is at all possible?

I mean, I don't know how to make in java the connection between the report and the subreport.

 

I have a select for the report but I don't know where to put the select query for the subreport.

 

I'm trying now to use scriptlets and see what happends.

 

Any ideas? Anyone ?

 

Thanks in advance

[rsilverns.sympatico.ca]

Did you try #1 that I listed in my original response? It seems to be the best suited solution to this problem. I don't think its possible to not embed the queryString into the sub-report since the contents of that subreport are 100% dependant on the current value of the grouped elements (ID and name).

 

 

I mean, I don't know how to make in java the connection between the report and the subreport.

To do this, pass the datasource to the sub-report as a parameter in the main reports parameter hashmap. This datasource would have to be a resultset datasource since you want to avoid embedding queryStrings in your reports.

 

To fill the report you would use...

 

Code:

	public boolean exportReport(JRResultSetDataSource ds, JRResultSetDataSource subReportDS, File outputFile) {
	    JasperReport jasperReport;
	    JasperPrint jasperPrint;
	    try
	    {
	    	
	      jasperReport = JasperCompileManager.compileReport("c:\main_report.jrxml"«»);

	      Map customParameters = new HashMap(); 
	      customParameters.put("SubReportDataSource", subReportDS);	   

			  jasperPrint = JasperFillManager.fillReport(
					  		jasperReport, customParameters, ds);
		   JasperExportManager.exportReportToPdfFile(
	          jasperPrint, outputFile.toString());
	    }
	    catch (JRException e)
	    {
		  recordMessage("There was an error filling the report.  Message given: " + e.getMessage(), TYPE_ERROR);
		  return false; 
	    }
	    return true;
	}

 

Both of the datasources would be resultset datasources that you created based upon the needs of your report. Now the problem is that your dataset for the subreport is static, it will not change with each individual ID-Name combo that is in the header for each sub-report. So the sub-report will not be accurate.

 

HTH,

Robin

[bogdan]

With the embeded select statement, in the report was no problem. I managed to do that before I posted my first post.

 

The question I wanted an answer to was the one related to how can I make a connection between the report and the subreport.

 

Guess it doesn't work so I'll leave the query in the report.

 

Thanks :)

[lucianc]

Can you explain again what do you mean by "making a connection between the report and the subreport"?

 

If you don't have a query in the master report, how do you pass the data source when you fill the report?

 

Regards,

Lucian