Count in Ireport

Asked

Viewed 404 times

4

I have a report where I have the column Status, in this column I have the following status: Finalized and Pending.

I created a variable and added it to the band Sumary and configured the following properties:

Variable class = java.lang.Integer
Calculation = Sum
ResetType = Report
Increment type = None
Variable expression = $F{tbl_suspensao_status}.equals("Finalizado")  ? 0 : 1

However the result is always the total everything, if I have 2 Finished and 2 Pending the result is equal to 4.

How can I count correctly how many Status are Completed and how many are Pending?

Query:

SELECT
     tbl_suspensao.`codigoBeneficiario` AS tbl_suspensao_codigoBeneficiario,
     tbl_suspensao.`contrato` AS tbl_suspensao_contrato,
     tbl_suspensao.`data_fim` AS tbl_suspensao_data_fim,
     tbl_suspensao.`data_inicio` AS tbl_suspensao_data_inicio,
     tbl_suspensao.`status` AS tbl_suspensao_status,
     tbl_usuario.`nome` AS tbl_usuario_nome,
     tbl_suspensao.`nomeBeneficiario` AS tbl_suspensao_nomeBeneficiario
FROM
     `tbl_usuario` tbl_usuario INNER JOIN `tbl_suspensao` tbl_suspensao ON tbl_usuario.`codigo` = tbl_suspensao.`usuario_id`
WHERE
 AND tbl_suspensao.`data_inicio` BETWEEN  $P{Data_Inicio} AND $P{Data_Fim}

GROUP BY
      tbl_suspensao_status
mysql sql ireport

  • Have to do or groupBy by Status.

  • Where do I put this groupBy? In the query?

  • That’s right. Select Status, Count(Id) as Total From Tabela Group By Status

  • It still didn’t work, I put the group By at the end, in addition to not showing all the records in the report also doesn’t count the totals

  • How are you mounting the query ? Puts it in the topic.

  • Ready, I put

  • The GROUP BY it works like this... what you put in SELECT have to put in the GROUP BY. But if the data from SELECT are different, your count will not be exact. I suggest you make only one SELECT for the count. Very simple. SELECT Status, Count(tbl_usuarios.nome) as Total Group By Status. That is, you will have two queries in your iReport. One for the records and one for the total. But I don’t remember if iReport has the function that already takes the total of records. It doesn’t have ?

  • I think the function Count does that

  • I also don’t know how to put two querys in the same report

  • @Techies, try to do this Select Coluna,count(*) from Tabela group by Coluna

Show 5 more comments

1 answer

2


puts a column to do the type count like this:

    SELECT
     sum(1) as contador,
     tbl_suspensao.`codigoBeneficiario` AS tbl_suspensao_codigoBeneficiario,
     tbl_suspensao.`contrato` AS tbl_suspensao_contrato,
     tbl_suspensao.`data_fim` AS tbl_suspensao_data_fim,
     tbl_suspensao.`data_inicio` AS tbl_suspensao_data_inicio,
     tbl_suspensao.`status` AS tbl_suspensao_status,
     tbl_usuario.`nome` AS tbl_usuario_nome,
     tbl_suspensao.`nomeBeneficiario` AS tbl_suspensao_nomeBeneficiario
FROM
     `tbl_usuario` tbl_usuario INNER JOIN `tbl_suspensao` tbl_suspensao ON tbl_usuario.`codigo` = tbl_suspensao.`usuario_id`
WHERE
 AND tbl_suspensao.`data_inicio` BETWEEN  $P{Data_Inicio} AND $P{Data_Fim}

GROUP BY
      tbl_suspensao_status;

So the column accountant remains his Field on the iReport.

Browser other questions tagged mysql sql ireport

You are not signed in. Login or sign up in order to post.