Get Top 1 value In the report

• bpradeep

  SSC Veteran

  Points: 249

  More actions

  August 17, 2010 at 6:28 pm

  #222605

  I have 4 fields in the report. For example

  field1,field2,field3,field4

  field1 data is like this

  A,B,C,D

  Note:

  1. This is sorted like this. but if some time there is no A record then B is the first record based on the date range.

  field1 --> taken from dataset(database)

  row group field1

  field2 --> CountRows()

  field3 --> CountRows()-Previous(CountRows())

  field4 --> Each Group CountRows/Top1 CountRows() (i.e Countrows()/Top1 CountRows)

  example 1

  actual data

  field1

  A

  A

  A

  B

  B

  C

  D

  OUTPUT shoule be

  Field1,Field2,Field3,Field4

  A, 3 , 0, 3/3

  B , 2, -1 , 2/3

  C , 1 , -1, 1/3

  D , 1 , 0, 0/3

  I hope there is some workaround for the same.

• Klaus.bilger

  SSC Journeyman

  Points: 82

  More actions

  August 24, 2010 at 2:51 am

  #1210849

  Hi,

  I'm not sure but I think you can use a select to do this

  like

  select a, b

  case c when c is NULL then 0

  the result is whenever the count is NULL you see the RS

  hope this help you

  Klaus