Calculations by group and by field content

[tinao]

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. 

[tinao]

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()

 

 

[atmaling]

hi,

Can u post some of ur database table entries and expected ouput with respect of that.

[tinao]

<?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()

[tinao]

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]

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?

[Ankur Gupta]

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

[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.

[Ankur Gupta]

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