Creation of Dynamic Charts in Report Builder

AnnuAbraham

5.00/5 (1 vote)

9 Aug 2011CPOL5 min read

41.3K

A single report can be used to display different sets of data.

Introduction

Report Builder 3.0 is a report authoring environment to create reports of our own choice, with desired columns and calculations. When you design a report, you specify where to get the data, which data to get, and how to display the data. When you run the report, the report processor takes all the information you have specified, retrieves the data, and combines it with the report layout to generate the report. You can preview your reports in Report Builder, or you can publish your report to a report server or a report server in SharePoint integrated mode, where others can run it.

For more information, please refer http://technet.microsoft.com/en-us/library/dd220460.aspx.

Dynamic Script Generation

For maintainability purpose, we decided to create the reports as dynamic as possible. A single report can be used to display different sets of data.

Using the Code

Before you dive into this section, please make sure that you are comfortable with Report Builder and SSRS Charts. Check this link: http://technet.microsoft.com/en-us/library/dd239351.aspx.

For this article, we will use the below script to generate and populate the Transaction table which is named tbl_Transaction, Product table named tbl_Product, and Company table named tbl_Company.

SQL

-------------------------------------------------------------
      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

Part I: Creating the Dynamic Chart

We will first look into creating a report which can display different sets of information based on the input parameters passed in.

For example, the same report file (rdl) can be used to generate a chart that displays Product versus Amount information as well as Company versus Quantity information.

Create a data source. For development purpose, create a connection embedded in the report; once the report is tested, modify the data source to be read from a parameter.

Create parameters like Report Title, SQL Query… The value for these parameters need to be passed from the application that calls these reports.

For example, the SQL query can be constructed within the ASPX application that calls the RDL report.

For testing purpose, provide a default value for the SQLQuery parameter and remove the value later on.

On clicking the fx button, the below window pops up. Copy paste the select query:

SQL

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.

And click on the OK button.

Create a dataset based on the SQLQuery parameter. Select the ‘Use a dataset embedded in the report’ option. Select the Data Source you have already created. Click on the fx button and select ‘Parameters’ from the Category window, double click on the SQLQuery parameter so that it will get added.

Select the Fields tab in the dataset properties and add two query fields: xaxis and yaxis.

Insert a chart using Chart Wizard, choose SQLDataset, select the chart type, select the Categories and Sigma values.

Drag the ReportTitle parameter into the report’s Title section.

Run the report.

You could parameterize the report further, by adding xaxisTitle and yaxisTitle as report parameters and passing the values from the application that calls the reports.

Right click on the Axis Title and select Axis Title properties. Click on the fx button and select the corresponding axis title parameter. Click on the OK button.

Do the same for both Axis Titles.

The above report can be used to display any chart as long as the dataset returns two values, namely xaxis and yaxis.

Try it out by giving the below statement as the default value to the SQLQuery parameter:

SQL

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

Save and publish the chart in Report Server, the chart could be accessed from your web page using the ReportViewer control. The advantage of using the above approach is a single RDL can be used to display any number of reports.

Part II: Creating the Period Based Chart

In this section, we will focus on creating a dynamic chart that would display time based information on the X axis. For example, weekly data, monthly data, quarterly data, yearly data, etc.

To start with, we will insert some more data into the transaction table, with different transaction date such that it spans across the week, month, and quarter.

SQL

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)

Weekly Chart Displaying Daily Information for a Week

For a weekly chart, we have to fill the data for every day in a week, in case the transaction table doesn’t have any transactions (or data) for every single day in a week.

In a real time scenario, the query should be constructed dynamically in the application which is calling the chart and passed on as a parameter based on the period (week, month, quarter, etc.) selected by the user.

Here is the query to generate all the days between a given from date and to date:

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)
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

The above table has to be joined with the SQL query that retrieves quantity/per week information from the Transaction table.

Copy paste the below query into the default value section of the SQL parameter that we have created earlier.

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

In the Horizontal Axis properties of the chart, click on the Axis Options tab, select the Axis Type as scalar, in the major tick mark section, the Interval value should be Auto and the Interval type should be set as default.

On executing, we will get the below chart:

Monthly Chart Displaying Week Numbers in a Month

SQL

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

Copy paste the below query into the default value section of the SQL parameter that we created earlier.

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

Quarterly Chart Displaying Information Per Month

Here is the filler query:

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 * 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

Copy paste the below query into the default value section of the SQL parameter that we created earlier and execute the chart.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)