Saturday, February 25, 2012

calendar style report

Is there a way to generate a report that looks like a calendar. If i want to
view a schedule with people assigned to days of the month.I'm working on this problem now and here's what I have so far.
Create a query that has the various date parts as columns in the result set.
As an example:
Select datepart(yy,shipDate) as yearnum,
datepart(mm,shipDate) as monthnum,
datename(mm,shipDate) as monthname,
datepart(wk,shipDate) as weeknum,
datepart(dw,shipDate) as daynum,
datename(dw,shipDate) as dayname,
datepart(dd,shipDate) as datenum,
shipmentID
from shipments
Order by shipDate
Now step through the new report wizard.
Choose the Matrix report layout.
Choose yearnum and monthnum for the page fields
Choose daynum for the column fields
Choose weeknum for the row fields
Choose datenum as the detail fields
This will cause the report to page break on month and year, the columns wii
be by day of week (starting with Sunday), and the rows will be the weeks of
the year.
Once you are at the format tab of your report, change the appropriate number
values to the name values so the column and page headers will be
human-friendly. right click on the datenum detail field and add a row below.
drag-and-drop your shipmentID field into the new row.
A little creative application of borders an you have a pretty good calendar
report.
It works pretty well EXCEPT...
when there is more than one row per date, you will only get the first detail
value. Perhaps someone can shed some light on how to get multiple rows in a
single date box without ruining the format. At this point I'm thinking about
resorting to a stored proc as my query so I can cursor through and create a
single text colum that is a concatenation of all the detail data for that
date.

No comments:

Post a Comment