Update Existing Report Code

Updated Report Function Code

Public Function CreateValidValuesDataset(ByVal bQueryScope As Boolean, _

ByVal bSelectSpecific As Boolean, _

ByVal strPattern As String, _

ByVal strStartDateTime As DateTime, _

ByVal strEndDateTime As DateTime, _

ByVal strField As String, _

ByVal strView As String, _

ByVal strTimezoneId As String, _

ByVal strTimezone As String ) As String

strPattern = ConvertPattern(strPattern)

Dim strQueryLimit As String

Dim strReportDateColumn As String

strQueryLimit = ""

strReportDateColumn = GetReportDateColumn(strTimezoneId, "")

Dim res As String

res = ""

If (bQueryScope = False) Then

If (bSelectSpecific = False And strPattern = "") Then

res = String.Format("SELECT N'ALL' as {0}, 0 as Id ", strField)

Else

res = "SELECT "

If (strQueryLimit <> "") Then

res = res & String.Format("Top {0} ", strQueryLimit)

End If

res = res & String.Format(" (CASE WHEN(Len({0})>0) THEN {1} ELSE N'<Blank>' END) AS {2}, 1 as Id From {3} ", _

strField, _

strField, _

strField, _

strView)

If (strPattern <> "") Then

res = res & String.Format("WHERE {0} {1} AND {2} IS NOT NULL AND {3} BETWEEN '{4}'AND '{5}' ", _

strField, _

strPattern, _

strField, _

strReportDateColumn, _

Format(UtcToTimeZone(strTimezone, strStartDateTime), "s"), _

Format(UtcToTimeZone(strTimezone, strEndDateTime), "s"))

Else

res = res & String.Format(" WHERE {0} IS NOT NULL AND {1} BETWEEN '{2}' AND '{3}' ", strField , strReportDateColumn, _

Format(UtcToTimeZone(strTimezone, strStartDateTime), "s"), Format(UtcToTimeZone(strTimezone, strEndDateTime), "s"))

End If

res = res & String.Format(" UNION Select N'ALL' AS {0}, 0 as Id order by Id, {1}", strField, strField)

End If

Else

res = String.Format("SELECT N'ALL' as {0}, 0 as Id ", strField)

End If

CreateValidValuesDataset = res

End Function