.net - MSChart 仅显示插入的日期，没有空格
问题描述
几天来，我一直在寻找如何使用 MS Chart 解决此问题，不幸的是，到目前为止我发现的一切都无济于事。
我有一个水平条形图，我正在加载特定时间范围内的销售数据。该数据已经使用日期维度表“按摩”到我想要的方式中，以便我可以在不存在数据的地方推零。
这意味着无论是每日、每周、每月还是每年，我的数据始终具有所需的点数。你要求 7 天你得到 7 行，你要求 12 周你得到 12 行你要求 3 个月你得到 3 行。总是（每年可能是目前时间的开始，所以我们不会谈论那个。）
现在 Daily 工作正常，但是当我切换到其他模式时，图表完全忽略了我给它一个非常具体的点数的事实，而是将日历中的每个日期放在我的数据点之间。
正如我所说，我已经尝试了各种我能找到的解决方案，但它们要么使事情变得更糟，要么导致错误。
- 起初我是对图表进行数据绑定，但后来切换到手动插入点
- 我已将 IsXValueIndexed 设置为 true，这只是导致图表根本不显示任何值...
- 添加 AlignDataPointsByAxisLabel 会导致关于需要全部为非空且唯一的错误
- IntervalOffset 值似乎什么都不做
Private Sub LoadChart()
Dim oDT As New DataTable
Dim sqlCmd As New SqlCommand
Dim sSQLDateCTE As String = "WITH Date_CTE as ( " & _
"SELECT [Date] as SaleDate " & _
"FROM [DateDimension] "
Dim sWHEREDateCTE As String = "WHERE [Date] BETWEEN DATEADD({0},-{1},GETDATE()) AND GETDATE() "
Dim sSQLSUM As String = "), SUM_CTE as ( " & _
"SELECT F01 as UPCCode, F254 as SaleDate, SUM(F64) as UnitsSold " & _
"FROM {0} " & _
"WHERE F1034 = 3 AND F64 <> 0 AND F01 = @UPCCode " & _
"AND F254 IN (SELECT saledate FROM Date_CTE) " & _
"GROUP BY F01,F254) " & _
"Select DC.SaleDate ,ISNULL(SC.UnitsSold,0) as UnitsSold " & _
"FROM Date_CTE as DC " & _
"LEFT OUTER JOIN SUM_CTE as SC " & _
"ON SC.SaleDate = DC.SaleDate " & _
"ORDER BY DC.SaleDate ASC "
Dim sSQL As String = ""
Try
Dim PeriodRow As DataRow = oAppEnv.dtPeriods.Select(String.Format("DPPeriodID = {0}", cboPeriods.SelectedValue)).FirstOrDefault
Dim DurationRow As DataRow = oAppEnv.dtDurations.Select(String.Format("DDDurationID = '{0}'", cboDuration.SelectedValue)).FirstOrDefault
Select Case PeriodRow.Item("DPPeriodAbbrev")
Case "D"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "DD", DurationRow.Item("DDDurationLength"))
sSQLSUM = String.Format(sSQLSUM, "RPT_ITM_D")
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM
Case "W"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "WW", DurationRow.Item("DDDurationLength")) & " AND Weekday = 1 "
sSQLSUM = String.Format(sSQLSUM, "RPT_ITM_W")
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM
Case "M"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "MM", DurationRow.Item("DDDurationLength")) & " AND [DAY] = DATEPART(Day,getdate()) "
sSQLSUM = String.Format(sSQLSUM, "RPT_ITM_M")
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM
Case "Y"
'????
End Select
sqlCmd.CommandText = sSQL
sqlCmd.Parameters.AddWithValue("@UPCCode", sUPCCode)
oDT = oAppEnv.oLogiDM.GetSQLData(sqlCmd)
ctSalesData.ChartAreas.Clear()
ctSalesData.ChartAreas.Add("Sales")
ctSalesData.Series.Clear()
ctSalesData.Series.Add("Sales")
ctSalesData.Series("Sales").ChartType = Charting.SeriesChartType.Bar
'ctSalesData.Series(0).IsXValueIndexed = True
'ctSalesData.ChartAreas(0).AxisX.IntervalOffsetType = Charting.DateTimeIntervalType.Days
'ctSalesData.ChartAreas(0).AxisX.IntervalOffset = -1
If oDT IsNot Nothing Then
'//Get the Date range.
Dim oSortDV As DataView = oDT.DefaultView
oSortDV.Sort = "UnitsSold DESC"
'ctSalesData.DataSource = oDT
For Each row In oDT.Rows
ctSalesData.Series(0).Points.AddXY(row.item("SaleDate"), row.item("UnitsSold"))
Next
'ctSalesData.AlignDataPointsByAxisLabel()
ctSalesData.Series(0).CustomProperties = "PixelPointWidth = 15"
ctSalesData.Series(0).IsValueShownAsLabel = True
Dim MinDate As Date = oDT.Rows(0).Item("SaleDate")
Dim Maxdate As Date = oDT.Rows(oDT.Rows.Count - 1).Item("SaleDate")
ctSalesData.ChartAreas(0).AxisX.Minimum = MinDate.ToOADate
ctSalesData.ChartAreas(0).AxisX.Maximum = Maxdate.ToOADate
If oDT.Rows.Count > 14 Then
Dim ZoomDate As Date = oDT.Rows(14).Item("SaleDate")
ctSalesData.ChartAreas(0).AxisX.ScaleView.Zoom(MinDate.ToOADate, ZoomDate.ToOADate)
End If
ctSalesData.Series(0).XValueType = Charting.ChartValueType.Date
ctSalesData.ChartAreas(0).AxisX.Interval = 1
ctSalesData.ChartAreas(0).AxisY.Minimum = 0
ctSalesData.ChartAreas(0).AxisY.Maximum = Math.Ceiling(oSortDV(0).Item("UnitsSold") / 10) * 10
If ctSalesData.ChartAreas(0).AxisY.Maximum = oSortDV(0).Item("UnitsSold") Then
ctSalesData.ChartAreas(0).AxisY.Maximum += 10
End If
ctSalesData.ChartAreas(0).CursorX.AutoScroll = True
ctSalesData.ChartAreas(0).AxisX.ScaleView.SizeType = Charting.DateTimeIntervalType.Number
ctSalesData.ChartAreas(0).AxisX.ScrollBar.ButtonStyle = Charting.ScrollBarButtonStyles.SmallScroll
ctSalesData.ChartAreas(0).AxisX.ScaleView.SmallScrollSize = 20
'ctSalesData.Series(0).XValueMember = "SaleDate"
'ctSalesData.Series(0).YValueMembers = "UnitsSold"
ctSalesData.ChartAreas(0).AxisX.ScrollBar.IsPositionedInside = False
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
- 还尝试将点更改为通用计数器
Dim iCnt As Integer = 0
For Each row In oDT.Rows
ctSalesData.Series(0).Points.AddXY(iCnt, row.item("UnitsSold"))
ctSalesData.Series(0).Points(iCnt).AxisLabel = row.item("SaleDate")
iCnt += 1
Next
但我认为这只是破坏了我的缩放代码，因为它都是基于日期的。
所以; 我到底如何摆脱这些空白的空间浪费并只显示我插入的数据点？
解决方案
因此，对于任何曾经/曾经/将遇到同样问题的人，到目前为止，我能够解决这个烦恼的唯一方法是执行我之前提到的通用计数器，但重新编写我的缩放代码。
这是任何有兴趣的人的重新编写的代码。
Private Sub LoadChart()
Dim oDT As New DataTable
Dim sqlCmd As New SqlCommand
Dim sSQLDateCTE As String = "WITH Date_CTE as ( " & _
"SELECT [Date] as SaleDate " & _
"FROM [DateDimension] "
Dim sWHEREDateCTE As String = "WHERE [Date] BETWEEN DATEADD({0},-{1},GETDATE()) AND GETDATE() "
Dim sSQLSUM As String = "), SUM_CTE as ( " & _
"SELECT F01 as UPCCode, F254 as SaleDate, SUM(F64) as UnitsSold " & _
"FROM {0} " & _
"WHERE F1034 = 3 AND F64 <> 0 AND F01 = @UPCCode " & _
"AND F254 IN (SELECT saledate FROM Date_CTE) " & _
"GROUP BY F01,F254) " & _
"Select DC.SaleDate ,ISNULL(SC.UnitsSold,0) as UnitsSold " & _
"FROM Date_CTE as DC " & _
"LEFT OUTER JOIN SUM_CTE as SC " & _
"ON SC.SaleDate = DC.SaleDate " & _
"ORDER BY DC.SaleDate ASC "
Dim sSQL As String = ""
Try
Dim PeriodRow As DataRow = oAppEnv.dtPeriods.Select(String.Format("DPPeriodID = {0}", cboPeriods.SelectedValue)).FirstOrDefault
Dim DurationRow As DataRow = oAppEnv.dtDurations.Select(String.Format("DDDurationID = '{0}'", cboDuration.SelectedValue)).FirstOrDefault
Select Case PeriodRow.Item("DPPeriodAbbrev")
Case "D"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "DD", DurationRow.Item("DDDurationLength"))
sSQLSUM = String.Format(sSQLSUM, "RPT_ITM_D")
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM
Case "W"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "WW", DurationRow.Item("DDDurationLength")) & " AND Weekday = 1 "
sSQLSUM = String.Format(sSQLSUM, "RPT_ITM_W")
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM
Case "M"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "MM", DurationRow.Item("DDDurationLength")) & " AND [DAY] = DATEPART(Day,getdate()) "
sSQLSUM = String.Format(sSQLSUM, "RPT_ITM_M")
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM
Case "Y"
'????
End Select
sqlCmd.CommandText = sSQL
sqlCmd.Parameters.AddWithValue("@UPCCode", sUPCCode)
oDT = oAppEnv.oLogiDM.GetSQLData(sqlCmd)
ctSalesData.ChartAreas.Clear()
ctSalesData.ChartAreas.Add("Sales")
ctSalesData.Series.Clear()
ctSalesData.Series.Add("Sales")
ctSalesData.Series("Sales").ChartType = Charting.SeriesChartType.Bar
ctSalesData.Series(0).IsXValueIndexed = True
'ctSalesData.ChartAreas(0).AxisX.IntervalOffsetType = Charting.DateTimeIntervalType.Days
'ctSalesData.ChartAreas(0).AxisX.IntervalOffset = -1
If oDT IsNot Nothing Then
'//Get the highest sold item so we can set the Y axis to bigger than the biggest
Dim oSortDV As DataView = oDT.DefaultView
oSortDV.Sort = "UnitsSold DESC"
'ctSalesData.DataSource = oDT
Dim iCnt As Integer = 0
For Each row In oDT.Rows
ctSalesData.Series(0).Points.AddXY(iCnt, row.item("UnitsSold"))
ctSalesData.Series(0).Points(iCnt).AxisLabel = row.item("SaleDate")
iCnt += 1
Next
'ctSalesData.AlignDataPointsByAxisLabel()
ctSalesData.Series(0).CustomProperties = "PixelPointWidth = 15"
ctSalesData.Series(0).IsValueShownAsLabel = True
'Dim MinDate As Date = oDT.Rows(0).Item("SaleDate")
'Dim Maxdate As Date = oDT.Rows(oDT.Rows.Count - 1).Item("SaleDate")
ctSalesData.ChartAreas(0).AxisX.Minimum = 0
ctSalesData.ChartAreas(0).AxisX.Maximum = oDT.Rows.Count + 1
If oDT.Rows.Count > 21 Then
Dim ZoomDate As Date = oDT.Rows(14).Item("SaleDate")
ctSalesData.ChartAreas(0).AxisX.ScaleView.Zoom(0, 22)
End If
'ctSalesData.Series(0).XValueType = Charting.ChartValueType.Date
ctSalesData.ChartAreas(0).AxisX.Interval = 1
ctSalesData.ChartAreas(0).AxisY.Minimum = 0
ctSalesData.ChartAreas(0).AxisY.Maximum = Math.Ceiling(oSortDV(0).Item("UnitsSold") / 10) * 10
If ctSalesData.ChartAreas(0).AxisY.Maximum = oSortDV(0).Item("UnitsSold") Then
ctSalesData.ChartAreas(0).AxisY.Maximum += 10
End If
ctSalesData.ChartAreas(0).CursorX.AutoScroll = True
ctSalesData.ChartAreas(0).AxisX.ScaleView.SizeType = Charting.DateTimeIntervalType.Number
ctSalesData.ChartAreas(0).AxisX.ScrollBar.ButtonStyle = Charting.ScrollBarButtonStyles.SmallScroll
ctSalesData.ChartAreas(0).AxisX.ScaleView.SmallScrollSize = 21
''ctSalesData.Series(0).XValueMember = "SaleDate"
''ctSalesData.Series(0).YValueMembers = "UnitsSold"
ctSalesData.ChartAreas(0).AxisX.ScrollBar.IsPositionedInside = False
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
尽管这是一个烦人的解决方法，但让 Date 方法正常运行仍然会很好。
推荐阅读
- javascript - 选项卡内容 IFrame 未加载
- dockerfile - 无法在 Cloud Run 中将变量传递给 Dockerfile
- javascript - 基于自定义属性的条件 jQuery 验证
- php - 临时删除多个命名空间而不保存到 PHP 中的文件？
- reactjs - 如何在 reactjs 中使引导滑块响应？
- curl - curl 可以交互地询问用户名和密码吗？
- python - 图像副本在 OpenCV 上不显示相同的图像
- swift - Alamofire 上传功能打印 responseJSON
- intellij-idea - 如何使用 WSL2 正确设置 IntelliJ
- python - django.db.utils.DatabaseError 与 Django 和 Djongo