AnsweredAssumed Answered

Using Max and groupby function

Question asked by cbscoreteam on Dec 4, 2015
Latest reply on Dec 5, 2015 by cbscoreteam

Hi,

 

I want to use below sql query in BOXI but boxi is not allowing me to use 'Max' and 'Group By'.

------------------------------------------------------------------------------------------

SELECT

     chgalg.change_chg_ref_num,

     chgalg.description,

  Max(chgalg.time_stamp),

     chgalg.type_symbol,

     chgalg.action_desc,

     chgalg.change_open_date

  

FROM

  chgalg

 

WHERE

  (

chgalg.action_desc  LIKE  '%Status changed from ''CISO Approved'' to ''Implemented''%'

  )

group by

chgalg.change_chg_ref_num

------------------------------------------------------------------------------------------

 

Here I am using change activity table. There are multiple change ref numbers in one column. I want to fetch only those change ref numbers in my report whose timestamp is latest.

 

Please guide me how to achieve this?

 

1) I tried with below mentioned formula in BOXI Report also.

Max(chgalg.time_stamp) ForEach (chgalg.change_chg_ref_num)

 

 

2) I tried with creating a measure also in designer.

I created an object having type as measure and aggregate function as 'Max'.

 

max(@Select(Change Activity\Time Stamp))

 

Parse error: The expression type is not compatible with the object type.

 

I selected the type as 'date'.

 

 

 

 

Kindly assist me on this.

Outcomes