SQL Table Editor DBNull with dates

Question

Hi,is it possible to display nothing in this column like Excel instead of this useless value? In the column I have DBNull.I tried setting the default value to “” (without quotes) or DBNull in the Column setting in the SQL Table Editor component but with no luck.

MarcusH · Accepted Answer

Create a data adapter and then populate the return datatable using a Business Rule (Type: Dashboard Data Set). That Business Rule will then process the returned rows and pick off the datetime column and format it appropriately. Here is an example of a Dashboard Data Set from the Guided Reporting solution. You would probably update the procedure WriteReportRow to include your columns:

Namespace OneStream.BusinessRule.DashboardDataSet.GRT_HelperQueries
	Public Class MainClass
		'------------------------------------------------------------------------------------------------------------
		'Reference Code: 		GRT_HelperQueries (Reporting Workspace) 
		'
		'Description:			Filters the list of reports displayed in the left listbox of the Guided Reporting workspace (GRT)
		'						based on security.  Each report is assigned a security group and this method will execute the
		'						SQL query to return all reports, then filter the list down to reports that the executing user
		'						can access.
		'
		'Usage:					Used as custom method query in a Dashboard Data Adapter with the following parameter structure.
		'Parameter ProtoType:	{GRT_HelperQueries}{ReportList}{EntityName="Reporting Entity Name"}
		'Parameter Example:		{GRT_HelperQueries}{ReportList}{EntityName="Houston"}
		'
		'Created By:			Tom Shea
		'Date Created:			8-26-2013
		'------------------------------------------------------------------------------------------------------------
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
			Try
				Select Case args.FunctionType

					Case Is = DashboardDataSetFunctionType.GetDataSetNames
						Dim reports = New List(Of String)()
						reports.Add("ReportList")
						Return reports

					Case Is = DashboardDataSetFunctionType.GetDataSet
						If args.DataSetName.Equals("ReportList", StringComparison.InvariantCultureIgnoreCase) Then
							'Get the EntityName parameter (GRT_Entity)
							Dim entityName As String = SharedConstants.Unknown.ToString
							args.NameValuePairs.TryGetValue("EntityName", entityName)

							'Evaluate all years and find data for the requested entity
							Using dbConnFW As DBConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)									
								Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)	
									'Create the return Table	
									Dim dt As DataTable = Me.CreateDataTable(si, "ReportList")

									'Get All Reports
									Dim sqlStatement As String = Me.CreateAllReportsSQL(si, entityName)
									Using dtAllReports As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sqlStatement, True)
										For Each row As DataRow In dtAllReports.Rows									
											'Check to see if the user is in the report's access group, if so, include it
											Dim groupName As String = row("SecurityGroupName").ToString
											If EngineSecurity.IsUserInGroup(dbConnFW, groupName) Then
												Me.WriteReportRow(dbConnFW, dbConnApp, dt, row)
											End If
										Next
									End Using

									Return dt									
								End Using		
							End Using
						End If
					End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function

		Private Function CreateDataTable(ByVal si As SessionInfo, ByVal dataTableName As String) As DataTable
			Try
				'Create the data table to return
				Dim dt As New DataTable(dataTableName)

				Dim objCol = New DataColumn
	            objCol.ColumnName = "DisplayName"
	            objCol.DataType = GetType(String)
	            objCol.DefaultValue = ""
	            objCol.AllowDBNull = False
	            dt.Columns.Add(objCol)

				objCol = New DataColumn
				objCol.ColumnName = "DshbrdName"
	            objCol.DataType = GetType(String)
	            objCol.DefaultValue = ""
	            objCol.AllowDBNull = False
	            dt.Columns.Add(objCol)

				Return dt

			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function

		Private Function CreateAllReportsSQL(ByVal si As SessionInfo, ByVal entityName As String) As String
			Try
				'Create the data table to return
				Dim sql As New Text.StringBuilder

				sql.Append("SELECT XFT_GRT_ReportSetItems.DisplayName, XFT_GRT_ReportSetItems.DshBrdName, XFT_GRT_ReportSetItems.DisplayOrder, XFT_GRT_ReportSetItems.SecurityGroupName ")
				sql.Append("FROM   XFT_GRT_ReportSetItems ")
				sql.Append("WHERE  (XFT_GRT_ReportSetItems.ReportSetName = N'(Default)') AND (XFT_GRT_ReportSetItems.Enabled = 1) ")

				sql.Append("Union SELECT XFT_GRT_ReportSetItems.DisplayName, XFT_GRT_ReportSetItems.DshBrdName, XFT_GRT_ReportSetItems.DisplayOrder, XFT_GRT_ReportSetItems.SecurityGroupName ")
				sql.Append("FROM XFT_GRT_ReportSetItems ") 
				sql.Append("INNER JOIN XFT_GRT_ReportSets ON XFT_GRT_ReportSetItems.ReportSetName = XFT_GRT_ReportSets.ReportSetName ") 
				sql.Append("INNER JOIN XFT_GRT_EntityReports ON XFT_GRT_ReportSets.ReportSetName = XFT_GRT_EntityReports.ReportSet1Name ")
				sql.Append("WHERE (XFT_GRT_EntityReports.EntityName = N'" & entityName & "') AND (XFT_GRT_ReportSetItems.Enabled = 1) ")

				sql.Append("UNION SELECT XFT_GRT_ReportSetItems.DisplayName, XFT_GRT_ReportSetItems.DshBrdName, XFT_GRT_ReportSetItems.DisplayOrder, XFT_GRT_ReportSetItems.SecurityGroupName ")
				sql.Append("FROM XFT_GRT_ReportSetItems ")
				sql.Append("INNER JOIN XFT_GRT_ReportSets ON XFT_GRT_ReportSetItems.ReportSetName = XFT_GRT_ReportSets.ReportSetName ")
				sql.Append("INNER JOIN XFT_GRT_EntityReports ON XFT_GRT_ReportSets.ReportSetName = XFT_GRT_EntityReports.ReportSet2Name ")
				sql.Append("WHERE (XFT_GRT_EntityReports.EntityName = N'" & entityName & "') AND (XFT_GRT_ReportSetItems.Enabled = 1) ")

				sql.Append("UNION SELECT XFT_GRT_ReportSetItems.DisplayName, XFT_GRT_ReportSetItems.DshBrdName, XFT_GRT_ReportSetItems.DisplayOrder, XFT_GRT_ReportSetItems.SecurityGroupName ")
				sql.Append("FROM XFT_GRT_ReportSetItems ")
				sql.Append("INNER JOIN XFT_GRT_ReportSets ON XFT_GRT_ReportSetItems.ReportSetName = XFT_GRT_ReportSets.ReportSetName ")
				sql.Append("INNER JOIN XFT_GRT_EntityReports ON XFT_GRT_ReportSets.ReportSetName = XFT_GRT_EntityReports.ReportSet3Name ")
				sql.Append("WHERE (XFT_GRT_EntityReports.EntityName = N'" & entityName & "') AND (XFT_GRT_ReportSetItems.Enabled = 1) ")

				sql.Append("UNION SELECT XFT_GRT_ReportSetItems.DisplayName, XFT_GRT_ReportSetItems.DshBrdName, XFT_GRT_ReportSetItems.DisplayOrder, XFT_GRT_ReportSetItems.SecurityGroupName ")
				sql.Append("FROM XFT_GRT_ReportSetItems ")
				sql.Append("INNER JOIN XFT_GRT_ReportSets ON XFT_GRT_ReportSetItems.ReportSetName = XFT_GRT_ReportSets.ReportSetName ")
				sql.Append("INNER JOIN XFT_GRT_EntityReports ON XFT_GRT_ReportSets.ReportSetName = XFT_GRT_EntityReports.ReportSet4Name ")
				sql.Append("WHERE (XFT_GRT_EntityReports.EntityName = N'" & entityName & "') AND (XFT_GRT_ReportSetItems.Enabled = 1) ")

				sql.Append("UNION SELECT XFT_GRT_ReportSetItems.DisplayName, XFT_GRT_ReportSetItems.DshBrdName, XFT_GRT_ReportSetItems.DisplayOrder, XFT_GRT_ReportSetItems.SecurityGroupName ")
				sql.Append("FROM XFT_GRT_ReportSetItems ")
				sql.Append("INNER JOIN XFT_GRT_ReportSets ON XFT_GRT_ReportSetItems.ReportSetName = XFT_GRT_ReportSets.ReportSetName ")
				sql.Append("INNER JOIN XFT_GRT_EntityReports ON XFT_GRT_ReportSets.ReportSetName = XFT_GRT_EntityReports.ReportSet5Name ")
				sql.Append("WHERE (XFT_GRT_EntityReports.EntityName = N'" & entityName & "') AND (XFT_GRT_ReportSetItems.Enabled = 1) ")

				sql.Append("ORDER BY XFT_GRT_ReportSetItems.DisplayOrder")

				Return sql.ToString

			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function

		Private Sub WriteReportRow(ByVal dbConnFW As DBConnInfo, ByVal dbConnApp As DBConnInfo, ByVal dt As DataTable, ByVal reportRow As DataRow)
			Try
	            'Create a new row and append it to the table
				Dim row As DataRow = dt.NewRow()

				row("DisplayName") = reportRow("DisplayName")
				row("DshbrdName") = reportRow("DshbrdName")

                dt.Rows.Add(row)

			Catch ex As Exception
				Throw ErrorHandler.LogWrite(dbConnApp.SI, New XFException(dbConnApp.SI, ex))
			End Try
		End Sub
	End Class	
End Namespace

MarcusH · Answer

There is always a default value for a datetime column so you will need to cast this column as another data type if you do not want to see anything there. Here is an example that I think will work:

Dim activeDate As DateTime = dr("ActiveDate")
Dim dtValue As DateTime? = Nothing
If Not dr.IsNull("ActiveDate") AndAlso activeDate.ToString("dd/MM/yyyy") <> "01/01/1900" Then
	BRApi.ErrorLog.LogMessage(si, $"Active Date Not NULL")
	dtValue = CDate(dr("ActiveDate"))
Else
	BRApi.ErrorLog.LogMessage(si, $"Active Date NULL")
End If
BRApi.ErrorLog.LogMessage(si, $"Active Date [{dtValue.ToString}]")

Note that the variable type is DateTime? which indicates that the variable value is nullable.

Sign up

Login with SSO

Login to the OneStream Community

Login with SSO