CA Service Management

Hi ,

I am trying to get the output of the following query into a field of jasper report(Jasper Studio)-

select COUNT(*) from call_req where (status='OP')

But Unable to do so.Please share me the procedure for the same.

Regards,

Saikat Das

Hello,

Even though this seems like a straightforward question, I'm not 100% sure I understand what you're trying to do, so I hope this is going in the right direction for you.  If not, please reply and I'm sure the community will help.

To get count(*) into a report I did this:

• in Studio, create this query:

• I had to add the field 'status_count' manually to the field list, but had no other difficulty.  Placing those fields on a report is no different from any other report:

• 'Preview' shows some figures:

... but perhaps you have something else in mind?

Regards,

James

 Thank you very much for your kind support.

Regards,

Saikat

Yes I want something like that .Actually I want only count for open and closed status ticket . So if I use groupby status it will display all status,but i want both open and closed tickets . Sir Please help me for the same.

Regards,

Saikat

Hi Saikat,

Jaspersoft experts - please jump in if I'm missing an obvious or easier solution! 

One way to do that might be to add two subreports - one for the Open count and one for the Closed count - that just present the 'select count' for the relevant status.

Another way is to use Variables to count the records as they are processed.  I've added four variables to my report, counting Open status, Closed status, Active and Inactive. 

First, Active and Inactive.  The attribute 'active' is integer 1 or 0, so to count Active records I sum the value of the field 'cr.active' into an Integer variable which I've called 'isActive'.  Here are the properties of my variable 'isActive':

This will sum the content of the field 'cr.active' - which I've set to be of type Integer in the field's properties - so it will add 1 to the count for each Active ticket.  The 'Reset type' of 'Report' means it will continue cumulating for all records.

On my first try I hadn't set the field 'cr.active' to type Integer - I had left it with the default type of String - so I got a runtime error when I previewed the report.

To get the number of Inactive tickets, I sum 1 minus the value of the 'active' field, so adding one to the count for each Inactive ticket.  Here is my definition for the variable 'isInactive':

To count tickets with Open status I build a variable with an expression that returns 1 if the status is Open and 0 for any other status, and sum the returned values:

My variable 'isStatusClosed' is similar, except obviously its expression tests for status 'CL'.

To get these counts into the report, I place the variables into the 'Summary' band in the Design view.  For testing, I also put them into the 'Detail 1' band so I could see they were being evaluated properly, but now I'm confident that they're correct I've removed them from the Detail band:

The field width is too narrow to show the full labels, but the '$V' references in the 'Summary' band are for the variables 'isStatusOpen', 'isStatusClosed', 'isActive' and 'isInactive' respectively. 

The last page of the report preview follows:

You'll observe that the Open and Closed counts add up to less than the total of the Active and Inactive counts - which you'd expect, because there are at least three status values that I haven't counted, but which still add to the Active or Inactive count.

Hope that helps!

Regards,

James