Archive for the ‘ Reporting Services ’ Category

SSRS 2008 Generate Calendar(s) based on a date range

I’m finally able to start blogging again. In addition to my Simple-Talk article I figured my first post would be an updated calendar report based on employee birthdays in the AdeventureWorks Database. Using recursive common-table-expressions I was able to generate the records needed to build a calendar for a given date range. Here is a link to the article (http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/#hr) to give you a basic idea of what the query is doing to generate the data as well as an example of how to build the report .

Here is the updated query:

--DECLARE @StartDate DATETIME = '03/26/2009', @EndDate DATETIME = '03/26/2010'
SELECT @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)) --FirstDayOfMonth
SELECT @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)) --LastDayOfMonth

; WITH Months AS (
	SELECT
	 [Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
	 [Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
	 FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
	 LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
	 FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
	 LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
	UNION ALL SELECT
	 [Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
	 [Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
	 FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
	 LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
	 FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
	 LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
	FROM
	 Months
	WHERE
	 LastDayOfMonth < @EndDate
), Dates AS (
	SELECT
	 [Month],
	 [Year],
	 [Date] = FirstDayOfCalendar,
	 FilterDate = LastDayOfCalendar
	FROM
	 Months
	UNION ALL SELECT
	 [Month],
	 [Year],
	 [Date] = DATEADD(DAY,1,[Date]),
	 FilterDate
	FROM
	 Dates
	WHERE
	 [Date] < FilterDate
), EmployeeBirthDay AS (
	SELECT
	 e.BirthDate,
	 [Day] = DATEPART(DAY, e.BirthDate),
	 [Month] = DATEPART(MONTH, e.BirthDate),
	 [Year] = DATEPART(YEAR, e.BirthDate),
	 FullName = e.FirstName + ' ' + e.LastName
	FROM
	 DimEmployee e
) SELECT
 DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
 d.Month,
 [Day] = DATEPART(DAY,d.[Date]),
 d.Year,
 [WeekDay] = DATEPART(WEEKDAY, d.[Date]),
 [Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date), 
 d.Date,
 ebd.FullName
FROM
 Dates d
 LEFT JOIN EmployeeBirthDay ebd ON ebd.Month = DATEPART(MONTH,d.[Date]) AND ebd.Day = DATEPART(DAY,d.[Date])

OPTION (MAXRECURSION 1000)

The earlier version of the report has been updated to  SSRS 2008. There is only a few changes that need to be made to get multiple calendars to display. Add a new tablix which is grouped by “DisplayOnCalendar” with page break after each group. Move the Month information from the report header into the new tablix as the group header. Drag the previous tablix into the details section on the new tablix. You can now remove the “Note” column from the previous tablix and update the value on the “Time” column to look at “FullName.” I made some additional format changes but won’t go in to details on those. Below is an example of what my report looks like before I preview it.

Once I preview the report I will show you the report which includes all the employees from AdventureWorks with their birthday in February 2010. The Date range I used is 03/26/2009 through 03/26/2010 so you will see there is 13 pages of the report to reflect the 13 months in the date range.

Here is a link to download the RDL file for the report above (http://www.box.net/shared/o7i6pc2znt) the zip file also contains the original RDL for the current month calendar report. The current month report is “Calendar.rdl” the updated version is “RangeCalendar.rdl” these reports will work with SSRS 2008 and the Adventureworks database.

10 Common SSRS Challenges

Check out my Article on Simple-Talk.com: http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/

Reporting Services 2005 – Dynamic Parameter List

I have seen developers writing reports in Reporting Services and taking a lot of time adding a section to the report to list all the parameter selection(s) to be displayed in the report. To address this issue dynamically (my way) you will need your reports deployed to the reporting services server, and have access to the reporting services database. Here you will find some template reports which include the Dynamic Parameter List (http://www.box.net/shared/zi7m4z62tg). Reporting services stores everything about the report in the database back-end. Gain access to the back-end and you will be able to do all sorts of things. For instance maybe create a report of your complete catalog of reports or even write some report metrics by looking at the report Execution log.

Here is my Query for obtaining the report parameter information, from the reporting services back-end:
SELECT
  [Order] = ROW_NUMBER() OVER (ORDER BY tmp.Name),
  ParameterName = Params.p.value('Name[1]','varchar(255)'),
  ParameterLabel = Params.p.value('Prompt[1]','varchar(255)'),
  DefaultValue = Params.p.query('DefaultValues/Value'),
  ParameterXml = Params.p.query('.'),
  ReportName = tmp.Name,
  ReportPath = tmp.Path
FROM
  (SELECT Name, Path, ReportParams = CONVERT(XML,Parameter) FROM dbo.Catalog WHERE Path = @ReportPath) tmp
  CROSS APPLY ReportParams.nodes('(Parameters/Parameter)') AS Params(p)
WHERE NOT Params.p.value('Name[1]','varchar(255)') IN ('ReportPath','ReportNumber')

The parameter “@ReportPath” is defaulted to “=Globals!ReportFolder + “/” + Globals!ReportName” this is how the report is referenced in the catalog.

Now that I have the parameter collection, I have a matrix control at the top of the report to display the parameters.  I am using some custom code to get my parameters to be displayed horizontally in a pre-determined number of columns:

Shared Function SetColumn(ByVal row As Integer, ByVal column As Integer) As String
  While row > column
    If column >= row Then Exit While
    row = row - column
  End While
Return row
End Function
Shared Function SetRow(ByVal row As Integer, ByVal column As Integer) As String
  Dim i As Integer = 1
  While row > column
    i = i + 1
    If column >= row Then Exit While
    row = row - column
  End While
  Return i
End Function

 
I group my rows using the two methods mentioned above, for instance if i want a 2 column list the columns group contains expression “=Code.SetColumn(Fields!Order.Value,2)” and the rows group contains expression “=Code.SetRow(Fields!Order.Value,2)”

 

The Templates in the zip at http://www.box.net/shared/zi7m4z62tg contain multiple features:

  • Horizontal Record display
  • Using Images in Page Header without causing Merged Cell issues in Excel
  • Templates for Landscape and Portrait you can build from
  • Dynamic Parameter List
  • Using Reporting Services database to your advantage
  • Using Custom Code in RDLs
  • Using Parent Groups and Padding based on levels
  • Using rectangles to create Page breaks so different section of the report when exported to excel display on separate worksheets