Using Report Builder 3.0 with a MySQL back end

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  July 28, 2014 at 10:10 am

  #291512

  I am trying to check for if there is not a null in a column called ReviewDate.

  I am using Report Builder 3.0 and I am trying to use a Filter.

  Can someone help me out with a expression? I have tried several already and no success.

  Here is the code:

  Select Step2.TicketName,

  Step2.CreateDate,

  Step2.AuthorizeDate,

  Step2.AssignDate,

  Step2.WIPDate,

  Step2.ProductionDate,

  Step2.ReviewDate,

  Step2.CloseDate,

  Step2.CurrentState,

  Step2.CurrentQueue,

  Step2.TicketType,

  Step2.TicketCategory,

  Step2.Project,

  Step2.ProjectDescription,

  Step2.ProjectDate,

  Step2.WorkingDaysDiff * 8 AS ReviewDateMinusCreateDateHoursDff,

  CONVERT(Left(Step2.WorkingTimeDiff,1),UNSIGNED) as Months,

  CONVERT(Mid(Step2.WorkingTimeDiff,2,2),UNSIGNED) as Days,

  CONVERT(Mid(Step2.WorkingTimeDiff,5,2),UNSIGNED) as Hours,

  CONVERT(Mid(Step2.WorkingTimeDiff,8,2),UNSIGNED) as Minutes

  FROM

  (Select Step1.TicketName,

  CAST(Step1.CreateDate AS DATETIME) AS CreateDate,

  CAST(Step1.AuthorizeDate AS DATETIME) AS AuthorizeDate,

  CAST(Step1.AssignDate AS DATETIME) AS AssignDate,

  CAST(Step1.WIPDate AS DATETIME) AS WIPDate,

  CAST(Step1.ProductionDate AS DATETIME) AS ProductionDate,

  CAST(Step1.ReviewDate AS DATETIME) AS ReviewDate,

  CAST(Step1.CloseDate AS DATETIME) AS CloseDate,

  Step1.CurrentState,

  Step1.CurrentQueue,

  Step1.TicketType,

  Step1.TicketCategory,

  Step1.Project,

  Step1.ProjectDescription,

  CAST(Step1.ProjectDate AS DATETIME) AS ProjectDate,

  DATEDIFF(ReviewDate,CreateDate) AS WorkingDaysDiff,

  TIMEDIFF(ReviewDate,CreateDate) AS WorkingTimeDiff

  FROM (Select t.id As TicketID,

  # t.tn As TicketNumber,

  Convert(t.tn, Unsigned) As TicketNumber,

  t.title As TicketName,

  (Select create_time

  From ticket_history

  Where ticket_id = t.id

  And history_type_id = 1)

  As CreateDate,

  (Select Min(create_time)

  From ticket_history

  Where ticket_id = t.id

  And state_id = 13)

  As AuthorizeDate,

  (Select Min(create_time)

  From ticket_history

  Where ticket_id = t.id

  And queue_id In (5,6,7,12,13,14,17,18,19,22,23,27,31,32,42,43) )

  As AssignDate,

  (Select Min(create_time)

  From ticket_history

  Where ticket_id = t.id

  # And state_id = 14 )

  And state_id In (14,19) )

  As WIPDate,

  (Select Max(create_time)

  From ticket_history

  Where ticket_id = t.id

  And state_id = 18 )

  # And state_id In (17,18,23,25,26) )

  # Or queue_id In (24,25,33,34,35,36,49) )

  As ProductionDate,

  (Select Min(create_time)

  From ticket_history

  Where ticket_id = t.id

  And state_id In (10,16,27)

  And create_time >= ProductionDate)

  As ReviewDate,

  (Select Min(create_time)

  From ticket_history

  Where ticket_id = t.id

  And state_id In (2,3,5,7,8,9,22) )

  # And create_time >= ProductionDate)

  As CloseDate,

  s.name As CurrentState,

  q.name As CurrentQueue,

  tt.name As TicketType,

  Case

  When t.type_id In (2,3,4,5,6,8,11,12,15,16,17) Then 'Networking'

  When t.type_id = 14

  Or (t.type_id In (7,10,13)

  And (Select Min(queue_id)

  From ticket_history

  Where ticket_id = t.id

  And create_time < ProductionDate

  And queue_id In (6,17,18,19,31))

  Is Not Null) Then 'Development'

  When t.type_id In (7,10,13) Then 'Networking'

  When t.type_id = 9

  Or (Select Min(queue_id)

  From ticket_history

  Where ticket_id = t.id

  And create_time < ProductionDate

  And queue_id In (7,22,23))

  Is Not Null Then 'Business Development'

  Else 'Other'

  End As TicketCategory,

  t.freekey16 as Project,

  t.freetext16 as ProjectDescription,

  t.freetime6 as ProjectDate

  From ticket t, ticket_type tt,

  ticket_state s, queue q

  #Where tt.id = t.type_id

  # And q.id = t.queue_id

  # And s.id = t.ticket_state_id

  # And Week(Date(t.create_time)) < Week(Curdate())

  #Where Week(Date(t.create_time)) < Week(Curdate())

  # And Year(Date(t.create_time)) <= Year(Curdate())

  Where Date(t.create_time) <

  Date_Add(Curdate(), Interval(1-DayofWeek(CurDate())) Day)

  # And Date(t.create_time) >

  # Date_Sub(Curdate(), Interval(79) Week)

  And tt.id = t.type_id

  And q.id = t.queue_id

  And s.id = t.ticket_state_id

  # And t.id In (17,743,770,771,959)

  # And t.id In (17,736,737,738,739,740,741,742,743,744,745,

  # 746,747,748,749,750,770,771,959)

  Order By t.id) As Step1 Order By 1)As Step2 Order By 1;

  This runs perfectly. If I try to add WHERE ReviewDate IS NOT NULL; as the last line I get this error message:

  TITLE: Microsoft SQL Server Report Builder

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

  An error occurred while executing the query.

  ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.41-3ubuntu12.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ReviewDate IS NOT NULL' at line 142

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

  ADDITIONAL INFORMATION:

  ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.41-3ubuntu12.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ReviewDate IS NOT NULL' at line 142 (myodbc3.dll)

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

  BUTTONS:

  OK

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

  Where should I place the IS NOT NULL for the ReviewDate in this code?:-D