SSRS - 按日期表达式分组

发布于 2024-11-06 04:22:39 字数 329 浏览 1 评论 0原文

使用 SS 报告服务 2008，我正在考虑设置按日期分组的月份销售数据的默认报告。

从理论上讲，它应该看起来有点像：

Date         Total Sales    Qty 
----------------------------------
01/04/2011   $15.00         3
02/04/2011   $20.00         4
03/04/2011   $00.00         0
Etc

我遇到的问题是一个月中没有记录的日子被跳过。（如上面的 03/04/2011 所示）。有没有办法显示没有记录的日期？

原文

Using SS reporting service 2008, I'm looking at setting up a default report on sale figures through the month grouping by date.

In theory it should look a little like:

Date         Total Sales    Qty 
----------------------------------
01/04/2011   $15.00         3
02/04/2011   $20.00         4
03/04/2011   $00.00         0
Etc

Problem I'm having is the days of the month that have no records are being skipped. (Like 03/04/2011 above). Is there a way to show the dates that have no records?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

病毒体 2024-11-13 04:22:39

在 SSRS 中没有办法专门执行此操作，但可以在 SQL 查询中完成。

您需要生成一个临时表（如果您要经常执行此操作，则数据库中的永久表也可能很好），其中包含顺序日期列表，然后使用外部将其连接到现有数据查询中加入。这样，没有数据的日期在连接后将在表中显示为空条目。

例如，如果您有以下表

DateList

Date
----
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
etc...

SalesData

Date        Qty
----------------
2011-01-01  5
2011-01-02  4
2011-01-04  7

，那么您可以使用以下查询来获取销售数据：
没有销售记录的日期为空记录。

select 
    dl.Date, 
    sd.Qty
from SalesData sd 
    left outer join DateList dl on dl.Date = sd.Date


Date    Qty
--------------
2011-01-01  5
2011-01-02  4
2011-01-03  (null)
2011-01-04  7
2011-01-05  (null)
etc...

请参阅此问题了解有关生成要加入的日期列表的信息。

There is no way to specifically do this in SSRS, but it can be accomplished in the SQL query.

You need to generate a temporary table (a permanent table in the database could also be good, if you are going to be doing this alot) with a list of sequential dates in it, and then join this onto the existing data query with an outer join. This way dates with no data will appear as null entries in the table after the join.

For example, if you had the following tables

DateList

Date
----
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
etc...

SalesData

Date        Qty
----------------
2011-01-01  5
2011-01-02  4
2011-01-04  7

then you could use the following query to get the sales data with the
null records for days where there are no sales recorded.

select 
    dl.Date, 
    sd.Qty
from SalesData sd 
    left outer join DateList dl on dl.Date = sd.Date


Date    Qty
--------------
2011-01-01  5
2011-01-02  4
2011-01-03  (null)
2011-01-04  7
2011-01-05  (null)
etc...

See this question for information on generating a list of dates to join on.

回复收藏 0 原文