New report from Excel and all using an SQL query

[jkeri]

I need to create a report that uses and Excel sheet as input... It contains 'city', and 'sales' fields. For each 'City' filed in the  Excel , I want to do a query against an oracle table.

 

What i dont know is how to even begin the design... Should i do a subreport ?    How can i use the 'city' filed in the Excel as a key/paramater to pull out data from my SQL query?

 

Select city, sales from AnnualSales where city =  city from the excel

[hozawa]

Since you're using JasperReports Library, just use something like Apache POI to read your Excel sheet and extract city and sales field. Create parameters city and sales and use them in your jrxml query statement.

[reportdev]

1. create a jrxml with excel datasource.

create a grouping based on the excel datasource city. 

Inside the group header/footer. create a table element which would use the sql query and fetch the records from database.

pass the city group value into the table element which will filter as per the group value.