MSChart - 强制生成一个折线图 [英] MSChart - Forcing a line chart to draw from the Origin

问题描述

我有一个从以下MSSQL查询运行的数据库图表 -

 SELECT dateovoiced AS Date，（SELECT SUM ）FROM jobs WHERE dateinvoiced> ='+ 
 new DateTime（年，月，1）.ToString（yyyy-MM-dd）+'AND dateivoiced< = 
 j.date ）/ 100 AS收入FROM作业j WHERE dateinvoiced> ='+ new 
 DateTime（年，月，1）.ToString（yyyy-MM-dd）+'AND dateovoiced< =' + new 
 DateTime（year，month，daysInMonth）.ToString（yyyy-MM-dd）+'GROUP BY dateinvoiced
  

（请原谅我想象的是一个奇怪的处理日期的方法，漫长的故事，但我会尽快改变他们。）

查询是在日历月份之前累计为用户业务完成的工作量。该图表本身与本月的目标值进行比较，因此用户可以看到其每月目标的进展。事情的一面都是正常的，查询也是。问题出现在本月初。

从上面的查询中提取的行显然是从第一个数据点开始。如果一个用户在一个月之前就没有拿到任何钱，然后再花费一个大的价值，那么在图表上不会画线，因为只有一点存在。它将一直保持下去，直到用户在稍后的一天中获得资金，此时他们可以看到一条线。

我想要的是确保即使用户只有一天的数据，总是有一行从原点上看图表。

我可以让这个工作的唯一方法是通过检查在数据库之前的第1天是否有数据，以及是否没有简单地将零价值销售记录插入到表中。我相信你可以看到为什么这是不能接受的。有没有办法手动添加点到数据绑定表？我试过，似乎不能这样做。没有，有没有办法改变SQL查询给我每个日期的值，即使数据不存在于他们所有？或任何其他方法？

谢谢。

解决方案

在评论中建议我认为，如果您可以将系列设置为列类型而不是行，那么这将更容易天不需要有与之相关的记录。

如果您想让所有这些日期可用，那么输出所有日子的查询将是好的然后您可以将其加入到所需的集合中以获取这些值。将零替换为您没有的值。

  WITH AllDays 
 AS 
（
 SELECT CAST（'20120101'as datetime）AS DAYS 
 UNION ALL 
 SELECT DATEADD（dd，1，DAYS）
 FROM AllDays 
 WHERE DATEADD（dd，1，DAYS）< ; cast（'20120201'as datetime）
）
 SELECT DAYS 
 FROM AllDays  - 考虑在此需要
  

I have a databound chart running from the following MSSQL query -

"SELECT dateinvoiced AS Date, (SELECT SUM(value) FROM jobs WHERE dateinvoiced >= '" + 
new DateTime(year, month, 1).ToString("yyyy-MM-dd") + "' AND dateinvoiced <= 
j.dateinvoiced)/100 AS Revenue FROM jobs j WHERE dateinvoiced >= '" + new 
DateTime(year, month, 1).ToString("yyyy-MM-dd") + "' AND dateinvoiced <= '" + new 
DateTime(year, month, daysInMonth).ToString("yyyy-MM-dd") + "' GROUP BY dateinvoiced"

(Please forgive what I imagine is a strange way of handling dates, long story but I'll be changing them soon.)

The query is getting a cumulative total of money taken for work that the user's business has done, by day, over the calendar month. The chart itself compares this against a target value for the month, so the user can see progress towards their monthly goal. That side of things is all working fine, and the query is too. The problem comes at the start of the month.

The line drawn from the query above obviously starts from the first datapoint. If a user has not taken any money until, say, half way through a month, and then takes a large value, no line will be drawn on the chart because only one point is present. It will stay like this until the user takes money on a later day, at which point they can see a line.

What I'd like is a way of ensuring that there is always a line to see on the chart from the origin, even if the user only has data from one day.

The only way I can get this working myself is by checking to see if there is data on day 1 prior to the databind, and if there isn't simply inserting a record for a zero-value sale into the table. I'm sure you can see why this is not acceptable. Is there a way to manually add a point onto a databound table? I've tried and can't seem to do this. Failing that, is there some way to alter the SQL query to give me a value for every date, even though data isn't present for them all? Or any other methods at all?

Thanks.

解决方案

As suggested in the comment i believe it would be easier if you could set the series as Column type rather than Line since all days won't need to have a record associated with it.

In case you want to have all those dates made available then a query which would output all days would be good and then you could join it on your required set to get those values. substituting zero for values you don't have.

WITH AllDays
AS
(
    SELECT CAST('20120101' as datetime) AS DAYS
    UNION ALL
    SELECT DATEADD(dd, 1, DAYS)
    FROM AllDays
    WHERE DATEADD(dd, 1, DAYS) < cast('20120201' as datetime)
)
SELECT DAYS
FROM AllDays -- consider making the join here as you need

这篇关于MSChart - 强制生成一个折线图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！