Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

matrix report?

Status
Not open for further replies.

EFREE

Programmer
Jun 29, 2005
41
US
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
 
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:
Code:
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:
Code:
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:
Code:
-- 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.
 
That idea is very sound; I just can’t get it to work in the Query Analyzer. My procedure looks like below:
Code:
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:

Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top