DynamicReports chart: comparing sales for years

-1

In the chart shown in the image I show sales figures for some products.

What I wanted now was to create a chart that would show the same information but compare the sales of these products in different years. Similar to the one found at this link: link but in a single chart, but could not reproduce.

Thank you for any information, thank you.

TestclassI'musing:

importjava.awt.*;importjava.math.BigDecimal;importjava.sql.Connection;importjava.util.logging.Level;importjava.util.logging.Logger;importjavax.swing.*;importnet.sf.dynamicreports.jasper.builder.JasperReportBuilder;importnet.sf.dynamicreports.report.builder.DynamicReports;importstaticnet.sf.dynamicreports.report.builder.DynamicReports.cht;importstaticnet.sf.dynamicreports.report.builder.DynamicReports.col;importstaticnet.sf.dynamicreports.report.builder.DynamicReports.report;importstaticnet.sf.dynamicreports.report.builder.DynamicReports.type;importnet.sf.dynamicreports.report.builder.chart.BarChartBuilder;importnet.sf.dynamicreports.report.builder.column.TextColumnBuilder;importnet.sf.dynamicreports.report.builder.style.StyleBuilder;importnet.sf.dynamicreports.report.builder.style.Styles;importnet.sf.dynamicreports.report.constant.HorizontalAlignment;importnet.sf.dynamicreports.report.constant.PageOrientation;importnet.sf.dynamicreports.report.constant.PageType;importnet.sf.dynamicreports.report.exception.DRException;classButtonFrameextendsJFrame{JButtonbChange;Connectioncon=newSQLConnection().getConnection();ButtonFrame(Stringtitle){super(title);setLayout(newFlowLayout());JasperReportBuilderreport=DynamicReports.report();//anewreportStringquery="select SUM(value_sale) as sum,COUNT(id_sale) as count,YEAR(date_sale) as year, YEAR(date_sale) as y,id,pro.name "
            + "from sales s "
            + "inner join product pro on pro.id=s.prduct_id "
            + "group by name";

        TextColumnBuilder<String> product = col.column("Product", "name", type.stringType());
        TextColumnBuilder<BigDecimal> sum = col.column("Sum", "sum", type.bigDecimalType()).setPattern("R$ #,###,###.00");
        TextColumnBuilder<Integer> count = col.column("Count", "count", type.integerType());
        TextColumnBuilder<String> year = col.column("Year", "year", type.stringType());

        BarChartBuilder chart1 = cht.barChart()
                .seriesColors((new Color(49, 79, 79)), (new Color(0, 255, 255)), (new Color(178, 255, 102)), (new Color(0, 250, 154)), (new Color(0, 100, 0)), (new Color(233, 150, 22)))
                //.setUseSeriesAsCategory(true)
                .setCategory(year)
                .setShowValues(Boolean.TRUE)
                .series(cht.serie(count).setSeries(product));

        try {
            report();
            report.setPageFormat(PageType.A4, PageOrientation.PORTRAIT);
            StyleBuilder bold = Styles.style().bold();
            StyleBuilder centeredBold = Styles.style(bold)
                    .setHorizontalAlignment(HorizontalAlignment.CENTER);
            StyleBuilder columnStyle = Styles.style(centeredBold);
              report.setColumnTitleStyle(columnStyle);
               report.setColumnStyle(Styles.style().setHorizontalAlignment(HorizontalAlignment.CENTER));
            report.columns(year,count, sum)
                    .summary(chart1)
                    .setDataSource(query, con)
                    .show();
        } catch (DRException ex) {
            Logger.getLogger(NovoJFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

public class TestingChart {

    public static void main(String[] args) {
        ButtonFrame frm = new ButtonFrame("Testing Chart");
       // frm.setSize(150, 75);
      //  frm.setVisible(true);
    }
}

Bank Script:

CREATE DATABASE  IF NOT EXISTS 'products' /*!40100 DEFAULT CHARACTER SET utf8 */;
USE 'products';
-- MySQL dump 10.13  Distrib 5.5.49, for debian-linux-gnu (x86_64)
--
-- Host: 127.0.0.1    Database: products
-- ------------------------------------------------------
-- Server version   5.5.49-0+deb8u1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table 'product'
--

DROP TABLE IF EXISTS 'product';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'product' (
  'id' int(11) NOT NULL,
  'name' varchar(45) DEFAULT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table 'product'
--

LOCK TABLES 'product' WRITE;
/*!40000 ALTER TABLE 'product' DISABLE KEYS */;
INSERT INTO 'product' VALUES (1,'food'),(2,'iron'),(3,'computer'),(4,'bags');
/*!40000 ALTER TABLE 'product' ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table 'sales'
--

DROP TABLE IF EXISTS 'sales';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'sales' (
  'id_sale' int(11) NOT NULL,
  'date_sale' date DEFAULT NULL,
  'prduct_id' int(11) NOT NULL,
  'value_sale' varchar(45) DEFAULT NULL,
  PRIMARY KEY ('id_sale'),
  KEY 'fk_sales_prducts_idx' ('prduct_id'),
  CONSTRAINT 'fk_sales_prducts' FOREIGN KEY ('prduct_id') REFERENCES 'product' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table 'sales'
--

LOCK TABLES 'sales' WRITE;
/*!40000 ALTER TABLE 'sales' DISABLE KEYS */;
INSERT INTO 'sales' VALUES (1,'2014-12-12',1,'125'),(3,'2003-07-09',1,'45'),(4,'2004-12-12',1,'23.55'),(5,'2014-10-10',2,'99.99'),(6,'2014-01-01',4,'56'),(7,'2003-10-10',3,'25'),(8,'2014-02-02',2,'36.55');
/*!40000 ALTER TABLE 'sales' ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Pom:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.mycompany</groupId>
    <artifactId>mavenproject1</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <dependencies>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>4.2.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.dynamicreports</groupId>
            <artifactId>dynamicreports-core</artifactId>
            <version>3.1.3</version>
        </dependency>
        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>3.2.1</version>
        </dependency>
        <dependency>
            <groupId>net.sf.squirrel-sql.thirdparty-non-maven</groupId>
            <artifactId>napkinlaf</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>net.xp-forge.maven.plugins</groupId>
            <artifactId>xp-maven-plugin</artifactId>
            <version>3.2.5</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jasperreports</groupId>
            <artifactId>jasperreports-fonts</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.7</version>
        </dependency>
        <dependency>
            <groupId>org.pushingpixels</groupId>
            <artifactId>trident</artifactId>
            <version>1.3</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.swt.gtk.linux</groupId>
                    <artifactId>x86</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>jfree</groupId>
            <artifactId>jfreechart</artifactId>
            <version>1.0.13</version>
        </dependency>

        <dependency>
            <groupId>tomcat</groupId>
            <artifactId>jasper-compiler</artifactId>
            <version>5.5.23</version>
        </dependency>
        <dependency>
            <groupId>xml-apis</groupId>
            <artifactId>xml-apis</artifactId>
            <version>1.0.b2</version>
            <type>jar</type>
        </dependency>
        <dependency>
            <groupId>net.sf.jasperreports</groupId>
            <artifactId>jasperreports</artifactId>
            <version>5.5.0</version>
            <type>jar</type>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.2.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.jdesktop</groupId>
            <artifactId>beansbinding</artifactId>
            <version>1.2.1</version>
            <type>jar</type>
        </dependency>
        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
            <version>1.0.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.3.1.Final</version>
            <type>jar</type>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.25</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>4.3.1.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-jpamodelgen</artifactId>
            <version>4.3.1.Final</version>
        </dependency>
        <dependency>
            <groupId>com.github.jai-imageio</groupId>
            <artifactId>jai-imageio-core</artifactId>
            <version>1.2.1</version>
        </dependency>

        <dependency>
            <groupId>org.swinglabs</groupId>
            <artifactId>swingx</artifactId>
            <version>0.9</version>
        </dependency>
    </dependencies>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
    </properties>
</project>
    
asked by anonymous 07.05.2016 / 05:37

1 answer

0

Solved, the problem was in my query , this is the correct one:

String query = "select SUM(value_sale) as sum,COUNT(id_sale) as count,YEAR(date_sale) as year, YEAR(date_sale) as y,id,pro.name "
                + "from sales s "
                + "inner join product pro on pro.id=s.prduct_id "
                + "group by year,name";

More specifically this part: group by year,name . The "second part" of group by was missing.

    
07.05.2016 / 15:17