I am using jasper reports to generate a report. I am trying to group all the records of an item under one name. But the item names are displayed multiple type in a report
My Code is
// TODO Auto-generated method stub\\
Logger.getLogger(getClass()).info("In Sales report servlet");
String from = request.getParameter("from");
String to = request.getParameter("to");
System.out.println("From "+from+" To "+to);
Connection connection = null;
String connectionURL = "jdbc:mysql://localhost:3306/medicaminventory";
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(connectionURL, "root", "");
StyleBuilder boldStyle = stl.style().bold();
StyleBuilder boldCenteredStyle = stl.style(boldStyle)
.setHorizontalAlignment(HorizontalAlignment.CENTER);
StyleBuilder columnTitleStyle = stl.style(boldCenteredStyle)
.setBorder(stl.pen1Point())
.setBackgroundColor(Color.LIGHT_GRAY);
StyleBuilder CenteredStyle = stl.style().setHorizontalAlignment(HorizontalAlignment.CENTER);
StyleBuilder titleStyle = stl.style(boldStyle).setFontSize(15);
JasperReportBuilder report = DynamicReports.report();//a new report
TextColumnBuilder<Integer> rowNumberColumn = col.reportRowNumberColumn("No.");
TextColumnBuilder<java.util.Date> columndate = col.column("Date", "Date_Of_Sale", type.dateType()).setStyle(CenteredStyle);
TextColumnBuilder<String> columnItem = col.column("Item Name", "Item_Name", type.stringType()).setStyle(boldStyle);
TextColumnBuilder<String> columnCustomer = col.column("Customer Name", "Customer_name", type.stringType()).setStyle(CenteredStyle);
TextColumnBuilder<Double> columnunit = col.column("Unit Price", "Item_Price", type.doubleType()).setStyle(CenteredStyle);
TextColumnBuilder<Integer> columnqty = col.column("Qty", "Item_qty", type.integerType()).setStyle(CenteredStyle);
TextColumnBuilder<BigDecimal> columnsub = columnqty.multiply(columnunit).setTitle("Subtotal").setStyle(CenteredStyle);
report
.columns(
rowNumberColumn,columnItem,columnCustomer,columndate,columnunit,columnqty,columnsub
)
.title(//title of the report
Components.text("Sale Report").setStyle(titleStyle))
.setColumnTitleStyle(columnTitleStyle)
.highlightDetailEvenRows()
.pageFooter(Components.pageXofY().setStyle(boldCenteredStyle))//show page number on the page footer
.setDataSource(createDataSource(from,to));
report.groupBy(columnItem).show();
}
catch(DRException e)
{
e.printStackTrace();
}
catch (SQLException e1) {
e1.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
private JRDataSource createDataSource(String from, String to) {
Connection connection = null;
String connectionURL = "jdbc:mysql://localhost:3306/medicaminventory";
DRDataSource dataSource = new DRDataSource("Item_Name","Customer_name", "Date_Of_Sale", "Item_Price", "Item_qty");
ResultSet rs1 = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
// Get a Connection to the database
connection = DriverManager.getConnection(connectionURL, "root", "");
//Add the data into the database
Statement stmt;
stmt=connection.createStatement();
rs1=stmt.executeQuery("SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"'");
rs1.beforeFirst();
while(rs1.next())
{
dataSource.add(rs1.getString("Item_Name"), rs1.getString("Customer_name"), rs1.getDate("Date_Of_Sale"), rs1.getDouble("Item_Price"), rs1.getInt("Item_qty"));
}
System.out.println("Done with while");
}
catch (SQLException e1) {
e1.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
finally {
// Always close the database connection.
try {
if (connection != null) connection.close();
}
catch (SQLException ignored){
}
}
return dataSource;
}
My Output
I want one item name and all the records belonging to that item under that item name.
****Answering my own question*********
If the records in the database are random(which depends on database design) you need to order them by using ORDER BY
Clause in SQL query
In this Case
Instead of
SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"'
The Query should be
SELECT * FROM tbl_sale WHERE Date_Of_Sale >= '"+from+"' AND Date_Of_Sale <= '"+to+"' ORDER BY item_name
where item_name
is the name of a column in database