tinao

Ankur, I really apprecieate you taking time to answer my questions.. I have actually set up the SQL query that way already. I also set the reset type to group for the variable.. yet it did not total. Can you, please, tell me HOW? See my XML file above. Also when I changed the SQL to order by vs. group by, it only showed me one line of results, not all the yards.. not sure why but I tried it all the way through with the variables and it still ended up with that not working on the variable to calculate. I got it working in REPORTICO but it is so antiquated the report looks aweful.. I hope I don't have to use that for my output. I was wondering however if I could import that XML file straight into jaspersoft studio with the working report?? Is that possible? Also I need to know how to add little icons.. red, green and yellow based on the contents of the field STATUS. IE If the status is In Service I want a green circle, if Operational but needs work, I want a yellow circle, if Out of service I want a red icon to appear. Any help with that would be GREAT! Thank you once again so much for your help.

Is is possible to add a FOR EACH statement to the expression to make it FOR EACH $F{Location} ???? if so, how do I put this in using Jaspersoft Studio?

So this is solved by just changing your sql query sort order and it fixed it? This answer could help others.. Please confirm and close this question if so. Thank you.

Sure.. See the image above.. that is the expected output with the exception that the first column should be a 2 not a one.. since in the location AAOH in the Yard Advantage there are 3 busses but only 2 with In Service Status.. however since it is calculating per line it is only showing 1 bus in that location. Not sure how to include the location and the Status="In Service in the same variable calculation.. that is where I am stuck. Here is the table.. Location Trip Bus Number Yard BusNeeded Know Issues Status County Date out of service Bus Make/Model Bus Year Driver Assigned School Assigned Route Number AAOH Spare 96194 Advantage 2 Intermittant Stalling Rich to Look at on Friday In Service Hillsborough County,Hillsborough County 0 AAOH 2 98136 Advantage 2 Intermittant Stalling Rich to Look at on Friday In Service Hillsborough County,Hillsborough County 0 AAOH 1 28940 Advantage 2 Transmission Out of Service Hillsborough County,Hillsborough County 0 NAU 3 98161 Amer Star-All 7 Transmission Shifting Bad Out of Service 0 CPATC 4 98135 Amer Star-All 7 In Service 0 NAU 1 98151 Amer Star-All 7 In Service 0 CPATC 3 99144 Amer Star-All 7 In Service 0

<?xml version="1.0" encoding="UTF-8"?> $V{REPORT_COUNT}%2 == 0 sELECT Yard, Location, BusNumber, Status, KnowIssues, BusNeeded FROM bus_status ORDER BY Yard, Location ]]></fieldDescription></p><p> </field></p><p> <field name="Location" class="java.lang.String"></p><p> <fieldDescription><![CDATA[ ]]></fieldDescription></p><p> </field></p><p> <field name="KnowIssues" class="java.lang.String"></p><p> <fieldDescription><![CDATA[ ]]></fieldDescription></p><p> </field></p><p> <field name="BusNeeded" class="java.lang.Integer"></p><p> <fieldDescription><![CDATA[ new java.lang.Integer($F{Status}.equals("In Service") ? 1 : 0) (int)($V{REPORT_COUNT}/15) $F{Yard} bus Service Status - By Yard bus Health Report Location status KnowIssues busNumber #Routes/Yard inServCount $F{Location} $F{Status} $F{KnowIssues} $F{BusNeeded} $V{InServCount} " " + $V{PAGE_NUMBER} "Page "+$V{PAGE_NUMBER}+" of" new java.util.Date()

Here is the file: <?xml version="1.0" encoding="UTF-8"?> $V{REPORT_COUNT}%2 == 0 sELECT Yard, Location, BusNumber, Status, KnowIssues, BusNeeded FROM bus_status ORDER BY Yard, Location ]]></fieldDescription></p><p> </field></p><p> <field name="Location" class="java.lang.String"></p><p> <fieldDescription><![CDATA[ ]]></fieldDescription></p><p> </field></p><p> <field name="KnowIssues" class="java.lang.String"></p><p> <fieldDescription><![CDATA[ ]]></fieldDescription></p><p> </field></p><p> <field name="BusNeeded" class="java.lang.Integer"></p><p> <fieldDescription><![CDATA[ new java.lang.Integer($F{Status}.equals("In Service") ? 1 : 0) (int)($V{REPORT_COUNT}/15) $F{Yard} bus Service Status - By Yard bus Health Report Location status KnowIssues busNumber #Routes/Yard inServCount $F{Location} $F{Status} $F{KnowIssues} $F{BusNeeded} $V{InServCount} " " + $V{PAGE_NUMBER} "Page "+$V{PAGE_NUMBER}+" of" new java.util.Date()

I have a database with the fields BusNumber, Yard, Location, Status, KnownIssues.. I would like to show in a report the status of the bus (there are three options 1. In Service, 2. Operational - Needs Work and 3. Out of Service. I would just like a total of the busses that have the status In Service Grouped by Yard and Location (Yard is where bus is parked and Location is School it is assigned to). We manage school bus transportation services. In the same report I need to show all the other columns. I have created the report with all the data and the Status field I was able to create a "Variable" that would suposedly calculate the total number of busses with the status In Service, however it is only counting it per line, not per location or per yard. How do i group the calculations to say not only do I want it to COUNT(Status)="In Service" but also to Group the results by Yard, Location?? I have tried sub reports, several other query options, and nothing seems to work to get all the data as I want it on the same page. See below. The In Serv Count column is not totalling by Yard, or Location. HELP IS APPRECIATED.

I have a similiar issue.. I have busses, that have three status's In Service, Operational but needs work and out of service. They are grouped by location. I got it to count the in service status but it will only do it per line. I would like to do it per LOCATION.. now how do I add that in.. right now I have the Variable.. InServCount And the properties for that show: Value Class Name: java.lang.String, Calculation: Sum, Expression new java.lang.Integer($F{Status}.equals("In Service") ?1:0) Initial Value Expression: BLANK Increment type: Group1 (this group is my Location--only one group) Incrementer Factory Class Name is BLANK Reset Type GROUP1 Any help would be greatly apprecieated.. The Group1 field name is YARD