Chart help Please

[sauder84]

 I am trying to create a bar chart that looks similar to the chart that is attached! Here is the SQL:

select Name, Month, sum(proj) as Projected, sum(act) as Actual

FROM monthlookup

inner join personproject

on monthlookup.MonthId = personproject.MonthId

inner join salesperson

on salesperson.SalesId = personproject.SalesId

inner join project

on personproject.ProjectId = project.ProjectId

where Quarter = 1

group by  monthlookup.MonthId, salesperson.SalesId;

I have 1 category Series: Series Expression $F{Month}, Category Expression $F{Name}, Value Expression $F{Projected}

The problem is when I create another Category Series and replace $F{Projected} with $F{Actual} all I get is the Actual data. I need Projected and Actual side by side in each month.

Any help would be greatly appreciated!!

[cmatthews]

 I'm a bit confused myself .. but you shouldn't be replacing anything, you should be adding another series with the same category field and a new value field I believe? I may be confused though as I said.

If you were to upload your template I might be able to mock it together for you, if you include the template and the create table statements for your involved tables it would be easier to show by example. Myself or someone may jump in then and be able to assist better.

What you are trying to do should be possible though.

[sauder84]

Thanks for you reply!  I am really new to iReports, so I am not sure what you mean by template? I have attached the SQL statements and a screenshot of the of the Report template.

[cmatthews]

 I mean the .JRXML file associated with your requirement, this way I can just load it up, make a few tweaks, and provide a copy back to you. If you aren't comfortable with that, it's okay ... in my opinion chart definition is easier explained by example.

[sauder84]

 Hope this is the right one

[cmatthews]

 Take a look at this, the problem is you want the series to be the same I think but the category to differ. I didn't make your tables and shove some sample data in to try it out but give this a try and see if it helps.

Would make it easier on me to help if you need more help to also give me the create table statements for your source tables, easy enough for me to make them but would take me longer. I will put sample data in if you give me the CREATE TABLE statements.

But try this first.

Clark

 

[sauder84]

 I thought I had attached the CREATE TABLE statements earlier. I also attached Excel file containing the data that I used to import into TOAD. Hope this helps! Your chart is almost perfect, but I would like SaleP1 Projected and SaleP1 Actual for January side by side (like the screenshot I had attached earlier). Is that even possible?

Thanks so much for your help!

[cmatthews]

 Unfortunately the jasperforge servers aren't liking to let me download that sql, mind putting the contents into the "code" block in a post so I can properly copy/paste it.

I'm getting this  error wghen I try to download your file...

Forbidden

You don't have permission to access /uploads/forum/101/99428/salesample.sql on this server.

---

Apache/2.2.3 (CentOS) Server at jasperforge.org Port 80

[sauder84]

 Create Database SaleSample;

Use SaleSample;

 

CREATE TABLE `salesperson` (

  `SalesId` int(11) NOT NULL AUTO_INCREMENT,

  `Name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`SalesId`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

 

CREATE TABLE `project` (

  `ProjectId` int(11) NOT NULL AUTO_INCREMENT,

  `Description` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ProjectId`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

 

CREATE TABLE `personproject` (

  `Id` int(11) NOT NULL AUTO_INCREMENT,

  `ProjectId` int(11) DEFAULT NULL,

  `SalesId` int(11) DEFAULT NULL,

  `MonthId` int(11) DEFAULT NULL,

  `Proj` decimal(15,2) DEFAULT NULL,

  `Act` decimal(15,2) DEFAULT NULL,

  `Chg` decimal(7,6) DEFAULT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=latin1;

 

CREATE TABLE `monthlookup` (

  `MonthId` int(11) NOT NULL,

  `Month` varchar(20) DEFAULT NULL,

  `Quarter` int(11) DEFAULT NULL,

  PRIMARY KEY (`MonthId`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

[cmatthews]

I think this is closer to what you are looking to accomplish.

I attached a PDF and the template... Hope this helps, I added a subdataset, seemed appropriate but if not just unlink it from the chart.

Good luck,

Clark

 

(may have gotten the grouping backwards...) let me know if you can't work it out.

Post Edited by cmatthews at 07/12/2012 03:31

[sauder84]

 Thanks so much! looks fantastic!

[sauder84]

 Thanks for your help! this is exactly what I was looking for!

[cmatthews]

 Your welcome, glad I coudl help.