在 Report Builder 中创建动态图表
5.00/5 (1投票)
单个报表可用于显示不同数据集。
引言
Report Builder 3.0 是一个报表创作环境，可用于创建我们自己选择的报表，包含所需的列和计算。在设计报表时，您可以指定数据的来源、要获取的数据以及数据的显示方式。运行报表时，报表处理器会获取您指定的所有信息，检索数据，并将其与报表布局结合以生成报表。您可以在 Report Builder 中预览报表，也可以将报表发布到报表服务器或 SharePoint 集成模式下的报表服务器，供他人运行。
有关更多信息，请参阅 http://technet.microsoft.com/en-us/library/dd220460.aspx。
动态脚本生成
为了方便维护，我们决定使报表尽可能动态化。单个报表可用于显示不同数据集。
Using the Code
在深入本节内容之前，请确保您熟悉 Report Builder 和 SSRS 图表。请查看此链接：http://technet.microsoft.com/en-us/library/dd239351.aspx。
在本文中，我们将使用以下脚本生成并填充名为 tbl_Transaction 的 Transaction 表、名为 tbl_Product 的 Product 表以及名为 tbl_Company 的 Company 表。
-------------------------------------------------------------
CREATE TABLES
-------------------------------------------------------------
USE [CRM]
GO
/****** Object: Table [dbo].[tbl_Company]
Script Date: 07/24/2011 16:02:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](50) NOT NULL,
[State] [varchar](50) NOT NULL,
[Country] [varchar](50) NOT NULL,
CONSTRAINT [PK_tbl_Company] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CRM]
GO
/****** Object: Table [dbo].[tbl_Product]
Script Date: 07/24/2011 16:04:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[ProductDescription] [varchar](50) NOT NULL,
CONSTRAINT [PK_tbl_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CRM]
GO
/****** Object: Table [dbo].[tbl_Transaction]
Script Date: 07/24/2011 16:04:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Transaction](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[Quantity] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_tbl_Transaction] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Transaction] WITH CHECK ADD FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tbl_Company] ([CompanyID])
GO
ALTER TABLE [dbo].[tbl_Transaction] WITH CHECK ADD FOREIGN KEY([ProductID])
REFERENCES [dbo].[tbl_Product] ([ProductID])
GO
----------------------------------------------------------------
--INSERT VALUES INTO THE TABLES
----------------------------------------------------------------
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','Cinderella')
GO
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','Jungle Book')
GO
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','Tinker Bell and the great Fairy Rescue')
GO
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','Rain Man')
GO
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','Delhi Belly')
GO
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','harry potter and the deathly hallows part 2')
GO
INSERT INTO [dbo].[tbl_Product] ([ProductName] ,[ProductDescription])
VALUES ('DVD','harry potter and the deathly hallows part 1')
GO
INSERT INTO [dbo].[tbl_Company] ([CompanyName] ,[State] ,[Country])
VALUES ('Disney Store','PA','United States')
GO
INSERT INTO [dbo].[tbl_Company] ([CompanyName] ,[State] ,[Country])
VALUES ('Video Rental','PA','United State')
GO
INSERT INTO [dbo].[tbl_Company] ([CompanyName] ,[State] ,[Country])
VALUES ('Bombay Junction','PA','United States')
GO
DECLARE @CompanyID int
DECLARE @ProductID int
set @CompanyID = (select CompanyID from tbl_Company where CompanyName = 'Disney Store')
set @ProductID = (select ProductID from tbl_Product where
ProductName = 'DVD' and ProductDescription = 'Cinderella')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate(),2,40)
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate() + 1,2,40)
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate()+ 2,2,40)
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate()-1,2,40)
set @ProductID = (select ProductID from tbl_Product where
ProductName = 'DVD' and ProductDescription = 'Jungle Book')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate()-1,2,40)
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'Tinker Bell and the great Fairy Rescue')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate()-1,2,40)
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'harry potter and the deathly hallows part 1')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate()-1,2,40)
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'harry potter and the deathly hallows part 2')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate(),1,25)
set @CompanyID = (select CompanyID from tbl_Company
where CompanyName = 'Video Rental')
set @ProductID = (select ProductID from tbl_Product where
ProductName = 'DVD' and ProductDescription = 'Rain Man')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate(),1,25)
set @CompanyID = (select CompanyID from tbl_Company
where CompanyName = 'Bombay Junction')
set @ProductID = (select ProductID from tbl_Product where
ProductName = 'DVD' and ProductDescription = 'Delhi Belly')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,getdate(),1,25)
----------------------------------------------------------------
--The partial output after running a simple Select query
----------------------------------------------------------------
select TransactionID, TransactionDate, ProductName, ProductDescription,
Amount, Quantity from tbl_Transaction T
join tbl_Product P on P.ProductID = T.ProductID
14 2011-07-24 16:02:02.063 DVD Cinderella 40 2
15 2011-07-23 16:02:02.110 DVD Cinderella 40 2
16 2011-07-23 16:02:02.127 DVD Jungle Book 40 2
17 2011-07-23 16:02:02.127 DVD Tinker Bell and the great Fairy Rescue 40 2
18 2011-07-23 16:02:02.127 DVD harry potter and the deathly hallows part 1 40 2
19 2011-07-24 16:02:02.127 DVD harry potter and the deathly hallows part 2 25 1
20 2011-07-24 16:02:02.127 DVD Rain Man 25 1
21 2011-07-24 16:02:02.127 DVD Delhi Belly 25 1
22 2011-07-25 18:40:50.590 DVD Cinderella 40 2
select ProductDescription, Sum(Amount) from tbl_Transaction T
join tbl_Product P on P.ProductID = T.ProductID where
TransactionDate between getdate() - 5 and getdate() + 5
group by P.ProductDescription
Cinderella 160
Delhi Belly 25
harry potter and the deathly hallows part 1 40
harry potter and the deathly hallows part 2 25
Jungle Book 40
Rain Man 25
Tinker Bell and the great Fairy Rescue 40
select C.CompanyName, SUM(quantity) from tbl_Transaction T
Join tbl_Company C on C.CompanyID = T.CompanyID where
TransactionDate between getdate() - 5 and getdate() + 5
group by C.CompanyName
Bombay Junction 1
Disney Store 15
Video Rental 1
第一部分：创建动态图表
我们将首先介绍如何创建根据传入的输入参数显示不同信息的报表。
例如，同一个报表文件（rdl）可以用来生成显示产品与金额信息以及公司与数量信息的图表。
创建数据源。出于开发目的，请创建一个嵌入报表中的连接；报表测试完成后，将数据源修改为从参数读取。
创建报表标题、SQL 查询等参数。这些参数的值需要由调用这些报表的应用程序传递。
例如，SQL 查询可以在调用 RDL 报表的 ASPX 应用程序中构建。
出于测试目的，为 SQLQuery 参数提供一个默认值，稍后删除该值。
单击 fx 按钮后，将弹出以下窗口。复制粘贴 select 查询
select ProductDescription as xaxis, Sum(Amount) as yaxis from tbl_Transaction T
join tbl_Product P on P.ProductID = T.ProductID where
TransactionDate between getdate() - 5 and getdate() + 5
group by P.ProductDescription.
然后单击 OK 按钮。
基于 SQLQuery 参数创建数据集。选择“使用嵌入报表的数据集”选项。选择您已创建的数据源。单击 fx 按钮，然后从“类别”窗口中选择“参数”，双击 SQLQuery 参数，使其被添加。
在数据集属性的“字段”选项卡中，添加两个查询字段：xaxis 和 yaxis。
使用“图表向导”插入图表，选择 SQLDataset，选择图表类型，选择类别和 Sigma 值。
将 ReportTitle 参数拖放到报表的标题部分。
运行报表。
您可以通过添加 xaxisTitle 和 yaxisTitle 作为报表参数，并从调用报表的应用程序传递值，进一步参数化报表。
右键单击轴标题，选择“轴标题属性”。单击 fx 按钮，然后选择相应的轴标题参数。单击 OK 按钮。
对两个轴标题执行相同操作。
只要数据集返回两个值（即 xaxis 和 yaxis），上述报表就可以用于显示任何图表。
尝试通过将以下语句作为 SQLQuery 参数的默认值来使用它
select C.CompanyName as xaxis, SUM(quantity) as yaxis from tbl_Transaction T
Join tbl_Company C on C.CompanyID = T.CompanyID where TransactionDate between
getdate() - 5 and getdate() + 5 group by C.CompanyName
将图表保存并发布到报表服务器，然后可以使用 ReportViewer 控件从您的网页访问该图表。使用上述方法的优点是，单个 RDL 可以用于显示任意数量的报表。
第二部分：创建基于时间段的图表
在本节中，我们将重点介绍创建一个动态图表，该图表将在 X 轴上显示基于时间的信息。例如，每周数据、每月数据、每季度数据、每年数据等。
首先，我们将向事务表中插入更多数据，并包含不同的交易日期，使其跨越一周、一个月和一个季度。
DECLARE @CompanyID int
DECLARE @ProductID int
set @CompanyID = (select CompanyID from tbl_Company where CompanyName = 'Disney Store')
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'Tinker Bell and the great Fairy Rescue')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,'2011-08-2',2,40)
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'harry potter and the deathly hallows part 1')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,'2011-08-7',2,40)
set @ProductID = (select ProductID from tbl_Product where
ProductName = 'DVD' and
ProductDescription = 'harry potter and the deathly hallows part 1')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,'2011-06-5',2,40)
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'harry potter and the deathly hallows part 1')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,'2011-05-4',2,40)
set @ProductID = (select ProductID from tbl_Product where ProductName = 'DVD'
and ProductDescription = 'harry potter and the deathly hallows part 1')
INSERT INTO [dbo].[tbl_Transaction]
([CompanyID],[ProductID],[TransactionDate] ,[Quantity] ,[Amount])
VALUES (@CompanyID, @ProductID,'2011-04-4',2,40)
显示一周内每日信息的每周图表
对于每周图表，即使事务表中某一周的每一天都没有交易（或数据），我们也必须填充每一天的数据。
在实际场景中，查询应在调用图表的应用程序中动态构建，并根据用户选择的时间段（周、月、季度等）作为参数传递。
这是生成给定开始日期和结束日期之间所有日期的查询
declare @fromDate datetime = '2011-07-20 16:02:02.110'
declare @toDate datetime = '2011-07-26 18:40:50.590'
declare @period int = datediff(dd, @fromDate , @toDate)
Declare @table_days table(dayID int, monthID int, yearID int, xaxis datetime)
Declare @date datetime select @date = @fromDate
Declare @index int Select @index= day(@fromDate)
while @index <= (day(@fromDate) + @period)
begin
Insert into @table_days values (DAY(@date), MONTH(@date), YEAR(@date), @date );
SELECT @date=DATEADD(day, 1, @date);
Select @index=@index+1
end
select * from @table_days
DayID MonthID YearID XAxis
20 7 2011 2011-07-20 16:02:02.110
21 7 2011 2011-07-21 16:02:02.110
22 7 2011 2011-07-22 16:02:02.110
23 7 2011 2011-07-23 16:02:02.110
24 7 2011 2011-07-24 16:02:02.110
25 7 2011 2011-07-25 16:02:02.110
26 7 2011 2011-07-26 16:02:02.110
上述表必须与检索 Transaction 表中每周期数量/信息的 SQL 查询连接。
将以下查询复制粘贴到我们之前创建的 SQL 参数的默认值部分。
declare @fromDate datetime = '2011-07-20 16:02:02.110'
declare @toDate datetime = '2011-07-26 18:40:50.590'
declare @period int = datediff(dd, @fromDate , @toDate)
-- Filler Table
Declare @table_days table(dayID int, monthID int, yearID int, xaxis datetime)
Declare @date datetime select @date = @fromDate
Declare @index int Select @index= day(@fromDate)
while @index <= (day(@fromDate) + @period)
begin
Insert into @table_days values (DAY(@date), MONTH(@date), YEAR(@date), @date );
SELECT @date=DATEADD(day, 1, @date);
Select @index=@index+1
end
-- Query to select Quantity/Week from Transaction table
Select Day(TransactionDate) as DayID, MONTH(TransactionDate)
as MonthID, YEAR(TransactionDate) as YearID ,
convert(varchar(10),transactionDate,101) as xaxis, SUM(quantity) as yaxis into #TEMP
From tbl_Transaction T where TransactionDate between @fromDate and @toDate group by
Day(TransactionDate), MONTH(TransactionDate), YEAR(TransactionDate),
convert(varchar(10),transactionDate,101)
-- Join the Filler sql query with Actual query
select Case isNUll(T.dayID,0) when 0 then #TEMP.DayID else T.dayID end as dayID,
Case isNUll(T.monthID,0) when 0 then #TEMP.MonthID else T.monthID end
as monthID, Case isNUll(T.yearID,0) when 0 then #TEMP.YearID else T.yearID end
as yearID, Case isNUll(T.xaxis,0) when 0 then left(DATENAME(DW,#temp.xaxis) ,3) +
' ' + CONVERT(varchar(5),#temp.xaxis,103) else left(DATENAME(DW,T.xaxis) ,3) +
' ' + CONVERT(varchar(5),T.xaxis,103) end as xaxis,
isNULL(#TEMP.yaxis,0) as yaxis from #TEMP
full outer join @table_days T on T.dayID = #TEMP.DayID and T.monthID = #TEMP.MonthID
and T.yearID = #TEMP.YearID order by yearid, monthid, dayid
在图表的“水平轴”属性中，单击“轴选项”选项卡，将“轴类型”选择为“标量”，在“主要刻度线”部分，将“间隔”值设置为“自动”，并将“间隔类型”设置为“默认”。
执行后，我们将得到以下图表
显示一个月内周数的月度图表
declare @fromDate datetime = '2011-07-1 16:02:02.110'
declare @toDate datetime = '2011-07-30 18:40:50.590'
Declare @counter int = 1 Declare @table_days table(weekID int,
endofweek varchar(25),weekNumberDisplay int)
DECLARE @dayofweek int = DATEPART(dw,@fromDate);
declare @enddateofweek datetime = DATEADD(DD,7-@dayofweek,@fromDate)
--Take the first day of the week and find the week number of that date
DECLARE @Index int =
DATEPART(WEEK, DATEADD(DD, 1 - DATEPART(DW, @fromDate), @fromDate))
While DATEDIFF(dd,@enddateofweek,@toDate) >=0 begin insert into @table_days
Values (@Index, 'Week' + cast(@counter as varchar) + ' ' +
CONVERT(varchar(5),@enddateofweek,103) ,@counter)
if DATEDIFF(DD,@enddateofweek,@toDate)> 0 and
DATEDIFF(DD,@enddateofweek,@toDate)<7
begin set @enddateofweek = DATEADD(dd,7,@enddateofweek)
set @index = DATEPART(WEEK, DATEADD(DD,
1 - DATEPART(DW, @enddateofweek), @enddateofweek));
set @counter = @counter + 1 insert into @table_days Values (@Index, 'Week' +
cast(@counter as varchar) + ' ' +
CONVERT(varchar(5),@enddateofweek,103) ,@counter) end
set @enddateofweek = DATEADD(dd,7,@enddateofweek) set @index =
DATEPART(WEEK, DATEADD(DD, 1 - DATEPART(DW, @enddateofweek),
@enddateofweek)); set @counter = @counter + 1 end
select * from @table_days
WeekID EndOfWeek WeekNumberDisplay
select * from
@table_days
WeekID EndOfWeek WeekNumberDisplay
27 Week1 02/07 1
28 Week2 09/07 2
29 Week3 16/07 3
30 Week4 23/07 4
31 Week5 30/07 5
将以下查询复制粘贴到我们之前创建的 SQL 参数的默认值部分。
declare @fromDate datetime = '2011-07-1 16:02:02.110'
declare @toDate datetime = '2011-07-30 18:40:50.590'
-- Filler Table
Declare @counter int = 1 Declare @table_days table(weekID int,
endofweek varchar(25),weekNumberDisplay int)
DECLARE @dayofweek int = DATEPART(dw,@fromDate);
declare @enddateofweek datetime = DATEADD(DD,7-@dayofweek,@fromDate)
--Take the first day of the week and find the week number of that date
DECLARE @Index int =
DATEPART(WEEK, DATEADD(DD, 1 - DATEPART(DW, @fromDate), @fromDate))
While DATEDIFF(dd,@enddateofweek,@toDate) >=0 begin insert into @table_days
Values (@Index, 'Week' + cast(@counter as varchar) + ' ' +
CONVERT(varchar(5),@enddateofweek,103) ,@counter)
if DATEDIFF(DD,@enddateofweek,@toDate)> 0
and DATEDIFF(DD,@enddateofweek,@toDate)<7
begin set @enddateofweek = DATEADD(dd,7,@enddateofweek)
set @index = DATEPART(WEEK, DATEADD(DD, 1 -
DATEPART(DW, @enddateofweek), @enddateofweek));
set @counter = @counter + 1 insert into @table_days Values (@Index, 'Week' +
cast(@counter as varchar) + ' ' +
CONVERT(varchar(5),@enddateofweek,103) ,@counter) end
set @enddateofweek = DATEADD(dd,7,@enddateofweek) set @index = DATEPART(WEEK,
DATEADD(DD, 1 - DATEPART(DW, @enddateofweek), @enddateofweek));
set @counter = @counter + 1 end
-- Query to select week based info from Transaction table
select DATEPART(WEEK, DATEADD(DD, 1 - DATEPART(DW, TransactionDate),
TransactionDate)) as xaxis, sum(Quantity) as yaxis into #TEMP
from tbl_Transaction T where TransactionDate between @FromDate and @ToDate
Group by DATEPART(WEEK, DATEADD(DD, 1 - DATEPART(DW, TransactionDate),
TransactionDate))
-- Query that joins Filler query with Actual sql query
select endofweek as xaxis, isNull(yaxis,0) as yaxis,
T.weekNumberDisplay from @table_days T
full outer join #TEMP on #TEMP.xaxis =
T.weekID order by weekNumberDisplay
显示每月信息的季度图表
这是填充查询
declare @fromDate datetime = '2011-05-1 16:02:02.110'
declare @toDate datetime = '2011-07-26 18:40:50.590'
declare @period int = datediff(mm, @fromDate , @toDate)
Declare @table_days table(monthID int, xaxis datetime, yearID int)
Declare @date datetime select @date = @fromDate
Declare @index int Select @index= month(@fromDate);
while @index <= (month(@fromDate) + @period)
begin
Insert into @table_days values(MONTH(@date),@date,YEAR(@date));
-- get the last date of every month and add 1 to it
SELECT @date= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0));
SELECT @date=DATEADD(MONTH, 1, @date)
Select @index=@index+1
end
select * from @table_days
MonthID Xaxis YearID
5 2011-05-01 16:02:02.110 2011
6 2011-06-30 23:59:59.000 2011
7 2011-07-30 23:59:59.000 2011
将以下查询复制粘贴到我们之前创建的 SQL 参数的默认值部分，然后执行图表。
declare @fromDate datetime = '2011-05-1 16:02:02.110'
declare @toDate datetime = '2011-07-26 18:40:50.590'
declare @period int = datediff(mm, @fromDate , @toDate)
Declare @table_days table(monthID int, xaxis datetime, yearID int)
Declare @date datetime select @date = @fromDate
Declare @index int Select @index= month(@fromDate);
while @index <= (month(@fromDate) + @period)
begin
Insert into @table_days values(MONTH(@date),@date,YEAR(@date));
-- get the last date of every month and add 1 to it
SELECT @date= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0));
SELECT @date=DATEADD(MONTH, 1, @date)
Select @index=@index+1
end
Select YEAR(TransactionDate) as yearID, month(TransactionDate)
as xaxis, sum(Quantity) as yaxis into #TEMP
From tbl_Transaction T where TransactionDate between @FromDate and
@ToDate group by YEAR(TransactionDate), month(TransactionDate)
select Case isNUll(T.xaxis,0) When 0 then null else
Left(DATENAME(month,T.xaxis),3) + ' ' + Right(YEAR(T.xaxis),2) end as xaxis,
case isNull(T.monthID,0) When 0 then #Temp.xaxis else T.monthID end as monthid,
case isNull(T.yearID,0) when 0 then #Temp.yearID else T.yearID end as yearid,
isNULL(#TEMP.yaxis,0) as yaxis from #TEMP full outer join @table_days T
on #TEMP.xaxis = T.monthID and #Temp.yearID = T.yearID order by yearid, monthid
