Count distinct rows with conditional (IF) in static report

[renatomitsunami]

Hi, I'm trying to count distinct rows with a conditional (IF)...example table:

City	Model	Ticket
SP	HOS	1234
MT	RTO	1234
SP	HOS	1234
ES	RTO	5678
PE	HOS	9630
SP	HOS	1234

In this script...    IF $F{City}="SP" AND $F{Model}="HOS"...  count rows where $F{Ticket} is distinct.

I have 3 rows but only 1 row distinct (SP/HOS/1234)....Result of variable $V{COUNT_X} = 1.

Thanks

[hozawa]

I think you'll need to create a sql statement to get the distinct rows. The problem is that you don't have rows sorted by City, Model, Ticket.

[MiditecReportDev]

You can do it as follows:

 

Create a variable as you already have and set the "Calculation" to "Distinct Count" - then in the expression fill (in your case) "$F{City} + $F{Model} + $F{Ticket}" - this will result in the Variable printing out the number of unique combinations of these three fields.