Calculations by group and by field content

0

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. 

Click for Larger Image

tinao's picture
2
Joined: Apr 1 2013 - 7:21am
Last seen: 5 years 6 months ago
Here is the file: <band height="94" splittype="Stretch"><statictext><reportelement uuid="390b6f2b-011e-4e5f-8766-d1bd1dc8f91f" style="SubTitle" x="336" y="65" width="449" height="29"></reportelement><textelement textalignment="Right"><font size="22" isbold="false"></font> </textelement><text></text></statictext><frame /><reportelement uuid="82617514-6aed-4f26-ac46-8cb0d75428eb" mode="Opaque" x="-20" y="0" width="822" height="65" forecolor="#006699" backcolor="#006699"></reportelement><statictext><reportelement uuid="dc1fbdb2-c9fd-4f01-8393-365a5a4295b1" style="Title" x="231" y="0" width="578" height="65"></reportelement><textelement textalignment="Right"><font size="54" isbold="false"></font> </textelement><text></text></statictext></band>
tinao - 6 years 6 months ago
hi, Can u post some of ur database table entries and expected ouput with respect of that.
atmaling - 6 years 6 months ago
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
tinao - 6 years 6 months ago
show 2 more...
<band height="94" splittype="Stretch"><statictext><reportelement uuid="390b6f2b-011e-4e5f-8766-d1bd1dc8f91f" style="SubTitle" x="336" y="65" width="449" height="29"></reportelement><textelement textalignment="Right"><font size="22" isbold="false"></font> </textelement><text></text></statictext><frame /><reportelement uuid="82617514-6aed-4f26-ac46-8cb0d75428eb" mode="Opaque" x="-20" y="0" width="822" height="65" forecolor="#006699" backcolor="#006699"></reportelement><statictext><reportelement uuid="dc1fbdb2-c9fd-4f01-8393-365a5a4295b1" style="Title" x="231" y="0" width="578" height="65"></reportelement><textelement textalignment="Right"><font size="54" isbold="false"></font> </textelement><text></text></statictext></band>
tinao - 6 years 6 months ago
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?
tinao - 6 years 6 months ago

1 Answer:

0

Hi tinao,

1. First thing you need to do is that in the SQL query you need to use ORDER BY clause upon Location,Yard.

2, Then you need to make two groups in the report named Location and yard and in each of the group expression would be same as the respective fields suppose $F{location} and $F{Yard}

3. Then you need to put the fields Location and Yard into the respective group header so that you get a demarcation of the same

4. then put all the fields required in the details section 

5. Remove the Group footers or you could place the subtotals based upon location and yard of bus in the group footers.

6. You would need to have a variable which would be counting the  total number of busses with the status In Service, if you need the count of the In service busses yard wise or you need it location wise or total busses in service then you need to set the reset type for the variable to the either of groups Yard/Location or you could set it to report as it suits you, hope you got the concept of reset type.

Thats it you are good to go

Hope that above explanation solves your query

Regards,

Ankur

Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 1 month 1 week ago

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.

tinao - 6 years 6 months ago

Hi tinao,
Can you please put the jrxml file so that I could see and let you know what is the real problem , as looking into the file I would be able to help you better , and yes you could put those little icons no problems it could be done by placing all the three red,green,yellow icons in the detail bank and then you could specify the Print when expression for each of the icons based on the check of status , so that would work and give you desired icons.
But if you could put up your jrxml file then could see into the primary problem of display of the report.

Regards,
Ankur

Ankur Gupta - 6 years 6 months ago
Feedback
randomness