adding missing dates to a range

[rbh123456789]

CR11.5

I have a View that looks like this:

User | eventtype | date
user1 | sickday | Dec 1 2011
user1 | meeting | Dec 1 2011
user2 | vacation | Dec 3 2011
user3 | |
user4 | sickday | Dec 1 2011

this report is supposed to:
-list each user
-display the event
-display the day of the event

i am using a crosstab, and have the rows display the user.
columns will be the dates
this report is supposed to show a 5 day date range (which they select using parameters)

Ideally, if they choose Dec 1 2011 to Dec 3, 2011 i would like to see 3 columns in my crosstab (Dec 1, 2, 3).

but, since i don't have a Dec 2 date, the report just shows Dec 1 and 3.

is there a way to show the Dec2 column, even though its not in the date field of the view?

 

[RTag]

What is you database type ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[rbh123456789]

thanks RTag.

Its a Sql Server 2005 database

 

[SkipVought]

What a waste, to see displayed a column or row with absolutely no data!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[rbh123456789]

its to show who does NOT have any events booked for that specific day.

 

[RTag]

I would create a temp table with the dates , cross join it with the users table and left join it with your current view. This will return all possible combinations for date-user and the available events for each combination and will guarantee you that you will have rows for all users and columns for all dates.
You are aware that you cannot show the actual event name in the crosstab value section right ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[rbh123456789]

thanks, i can try that.

if i use the Mode function to evaluate the field, i believe it will show the eventtype; but i might have a problem if there is more than one event per day..

 

[RTag]

You have to decide about the value section.

About the dates temp table .... I have a function which will return a table with the dates in a period:

---------------------------------------------
CREATE FUNCTION [dbo].[DatesTable]
(
@FromDate DATE,
@ToDate DATE
)
RETURNS @datetable TABLE ([Date] DATE)
AS
BEGIN

WITH DatesTable(CurrentDate) AS
(
SELECT @FromDate AS datetime
UNION ALL
SELECT DATEADD(DAY, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
)

INSERT INTO @datetable ([Date])
SELECT CurrentDate FROM DatesTable OPTION (MAXRECURSION 0)

RETURN;
END
---------------------------------------------

it cann be called in this way:

SELECT * FROM [dbo].[DatesTable]('12/1/2011','12/4/2011')



Here is the script that can demontrate the use of the function in your scenario :


CREATE TABLE #Users (UserID INT, UserName VARCHAR(10))
CREATE TABLE #UsersEvents (UserID INT, EventID INT,EventDate DATETIME)

INSERT INTO #Users (UserID,UserName) VALUES (1,'User1'), (2,'User2')
INSERT INTO #UsersEvents (UserID,EventID, EventDate) VALUES (1,1,'12/1/2011'), (1,3,'12/2/2011'),(2,1,'12/2/2011'), (2,2,'12/1/2011'),(2,4,'12/4/2011'), (1,4,'12/4/2011')

SELECT * FROM #UsersEvents
SELECT * FROM #Users

SELECT d.[Date],u.UserName,ue.EventID
FROM [dbo].[DatesTable]('12/1/2011','12/4/2011') d
CROSS JOIN #Users u
LEFT JOIN #UsersEvents ue ON d.[Date] = ue.EventDate AND ue.UserID=u.UserID

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[rbh123456789]

Rtag - thank you very much.

I don't think the crosstab is going to work for me anymore, unfortunately.

This was a simple sounding report (at first), but its getting complicated now..
I think i will end up having to use subreports in order to get this type of style report:

http://i1226.photobucket.com/albums/ee402/rbh123456789/table.jpg

any suggestions?

 

[RTag]

You can prepare the data outside crystal reports and select it as a normal table. May be this will be helpful:

http://r-tag.com/PivotSample.aspx

It is for City/Date instead of User/Date.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.