manshack_one Posted February 26, 2010 Share Posted February 26, 2010 Here is how the data comes out from my query: unitdesc auditdate dentalwro atc Bridgeport PPT 9/1/2009 100 98 Bridgeport PPT 10/1/2009 85 97 Bridgeport PPT 11/1/2009 67 96 Mineral Wells PPT 9/1/2009 99 89 Mineral Wells PPT 10/1/2009 89 88 Mineral Wells PPT 11/2/2009 78 87 I need to pivot the data though in the report so it's like this: dentalwro atc 9/1/2009 10/1/2009 11/1/2009 9/1/2009 10/1/2009 11/1/2009 Bridgeport PPT 100 85 67 98 97 96 Mineral Wells PPT 99 89 78 89 88 87 Will I have to script this or are there better options in iReport to pivot the data. It needs to fill horizontally but do so for all the values in the first column (Bridgeport dentalwro values) then the next column (Bridgeport atc) before moving on to Mineral Wells dentalwro then Mineral Wells atc values. Any suggestions would be greatly appreciated. Link to comment Share on other sites More sharing options...
manshack_one Posted March 1, 2010 Author Share Posted March 1, 2010 I'm really needing some help with this one. It's driving me nuts. Here's the properly formatted report that originally came from an Excel spreadsheet and the data as it's being collected now. I need to query my data to look like that report. Link to comment Share on other sites More sharing options...
manshack_one Posted March 2, 2010 Author Share Posted March 2, 2010 it was all in the query:select u.system, u.unitcode, u.unitdesc, s1.dentalwro as dental1, s1.dentalnwr as nwr1, s2.dentalwro as dental2, s2.dentalnwr as nwr2, s3.dentalwro as dental3, s3.dentalnwr as nwr3, s1.atc1 as atc11, s2.atc1 as atc12, s3.atc1 as atc13, s1.atc2 as atc21, s2.atc2 as atc22, s3.atc2 as atc23, s1.atc3 as atc31, s2.atc3 as atc32, s3.atc3 as atc33, s1.dentalcs as cs1, s2.dentalcs as cs2, s3.dentalcs as cs3, s1.dentalnsp as nsp1, s2.dentalnsp as nsp2, s3.dentalnsp as nsp3, s1.dentalnpr as npr1, s2.dentalnpr as npr2, s3.dentalnpr as npr3from UNITS uleft outer join SLC s1 on s1.unit = u.unitdesc and s1.auditdate between $P{begindate} and last_day($P{begindate})left outer join SLC s2 on s2.unit = u.unitdesc and s2.auditdate between DATE_ADD($P{begindate}, interval 1 month) and last_day(date_add($P{begindate}, interval 1 month))left outer join SLC s3 on s3.unit = u.unitdesc and s3.auditdate between DATE_ADD($P{begindate}, interval 2 month) and last_day(date_add($P{begindate}, interval 2 month))order by u.system, u.unitdesc; Link to comment Share on other sites More sharing options...
vnug Posted March 30, 2010 Share Posted March 30, 2010 Or, you could use crosstab to give you the output you are looking for. 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