sauder84 Posted July 10, 2012 Share Posted July 10, 2012 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 ActualFROM monthlookupinner join personprojecton monthlookup.MonthId = personproject.MonthIdinner join salespersonon salesperson.SalesId = personproject.SalesIdinner join projecton personproject.ProjectId = project.ProjectIdwhere Quarter = 1group 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!! Link to comment Share on other sites More sharing options...
cmatthews Posted July 10, 2012 Share Posted July 10, 2012 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. Link to comment Share on other sites More sharing options...
sauder84 Posted July 11, 2012 Author Share Posted July 11, 2012 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. Link to comment Share on other sites More sharing options...
cmatthews Posted July 11, 2012 Share Posted July 11, 2012 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. Link to comment Share on other sites More sharing options...
sauder84 Posted July 11, 2012 Author Share Posted July 11, 2012 Hope this is the right one Link to comment Share on other sites More sharing options...
cmatthews Posted July 11, 2012 Share Posted July 11, 2012 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 Link to comment Share on other sites More sharing options...
sauder84 Posted July 11, 2012 Author Share Posted July 11, 2012 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! Link to comment Share on other sites More sharing options...
cmatthews Posted July 11, 2012 Share Posted July 11, 2012 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...ForbiddenYou 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 Link to comment Share on other sites More sharing options...
sauder84 Posted July 11, 2012 Author Share Posted July 11, 2012 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; Link to comment Share on other sites More sharing options...
cmatthews Posted July 12, 2012 Share Posted July 12, 2012 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 Link to comment Share on other sites More sharing options...
sauder84 Posted July 12, 2012 Author Share Posted July 12, 2012 Thanks so much! looks fantastic! Link to comment Share on other sites More sharing options...
sauder84 Posted July 12, 2012 Author Share Posted July 12, 2012 Thanks for your help! this is exactly what I was looking for! Link to comment Share on other sites More sharing options...
cmatthews Posted July 12, 2012 Share Posted July 12, 2012 Your welcome, glad I coudl help. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now