matrix report?

[EFREE]

Is this layout possible in RS using a matrix? The user enters in report parameters for the start and finish date (which could be any amount of time). Then how can it get the format of the report to print in the following way. The weeks would need to be Sunday to Saturday and be labeled week 1 week 2 and so on (the spacing is just off on the sample), it would have to extend for the total time of the parameter 1,2,3,4 weeks etc based on parameters.

Agent | Week 1 | | Week 2
Kim 7/1/2005 7/2/2005 7/3/2005 7/4/2005 7/5/2005 7/8/2005 7/9/2005 …
Field1 22 22 22 33 22 11 11
Field2 88 88 55 44 32 32 11
| Week 1 | | Week2
Jack 7/1/2005 7/2/2005 7/3/2005 7/4/2005 7/5/2005 7/8/2005 7/9/2005 …
Field1 22 22 22 33 22 11 11
Field2 88 88 55 44 32 32 11

Any ideas greatly appreciated

Eric

 

[ookete]

Yes, I believe this is possible. Matrix reports can keep extending to fit all the combinations in your recordset. Just set up your base recordset to look something like:

Week     Date       Agent    Field1    Field2
Week 1   7/1/05     Kim      22        88
Week 1   7/2/05     Kim      23        89
.
.
Week 2   7/8/05     Kim      11        32
Week 2   7/9/05     Kim      12        33
.
.
Week 1   7/1/05     Jack     24        87
.
.
Week 2   7/8/05     Jack     12        32
etc

The trick will be to create your Week field. I don't think RS will do this for you. You probably need to make it in your base stored procedure. You can find out what starting day of the week your SQL Server is set to by doing:

SELECT @@DATEFIRST

Monday = 1 and Sunday = 7. If @@DATEFIRST is not set to 7, then you want to do so (at least for this query... you can set it back at the end of the query if needed). So then you need to find your week numbers. Your procedure might look something like:

-- Set Sunday as your first day
DECLARE @originalFirstDay tinyint  -- saving your server setting
SELECT @originalFirstDay = @@DATEFIRST
SET @@DATEFIRST = 7

-- Now need to find the starting week number.
-- This is so you can shift all weeks to your base "Week 1"
DECLARE @week1 int
SELECT TOP 1 @week1 = DATEPART(wk, DateField) 
    FROM MyTable 
    ORDER BY DateField

-- Perform your final query
SELECT 'Week ' + CAST(DATEPART(wk,DateField) - @week1 +1 AS char(1)) Week,
    DateField, Agent, Field1, Field2
FROM MyTable
ORDER BY Agent, DateField

-- Set back the server's DATEFIRST if needed
SEt @@DATEFIRST = @originalFirstDay

Something like that should get you the recordset you need.

 

[EFREE]

That idea is very sound; I just can’t get it to work in the Query Analyzer. My procedure looks like below:

SET DATEFIRST 7
DECLARE @week1 int
SELECT TOP 1 @week1 = datepart(wk,DATEADD(ss, actiondate, '01-01-1970 00:00:00'))
FROM historyactions
ORDER BY actiondate


select datepart(wk,DATEADD(ss, actiondate, '01-01-1970 00:00:00') - @week1 +1 )AS Weeks, userid, @week1 
from historyactions
where companyid = 156 and userid > 0
Order By Weeks

I took out the “AS char(1)” because it doesn’t allow the use of AS there. I also didn’t need to set the datefirst back to anything. It didn’t return the weeks starting at 1 though, they started at 15.

I also tried just plugging it into the code you displayed, as so:

SET DATEFIRST 7
DECLARE @week1 int
SELECT TOP 1 @week1 = DATEPART(wk, actiondate) 
    FROM historyactions 
    ORDER BY actiondate


SELECT  'Weeks' + CAST(DATEPART(wk,actiondate) - @week1 +1 AS char(1)) As Weeks,
    actiondate, userid, routingid, companyid
FROM historyactions
WHERE companyid = 156 and userid > 0
ORDER BY userid, actiondate

But I get two errors of “Arithmetic overflow error converting expression to data type datatime” on both select statements.

 

[EFREE]

i figured it out thanks