Advice required: Matrix report with a static column

First off, I apologize if this is an elementary question. Thanks for your
patience.

I have a requirement to develop a report that shows by calendar day how much
effort a resource has expended on what tasks. So, I have a stored procedure
that returns data that looks like this:

Resource calendarDay description etcHours effortHours
- - -- - --
Smith, John 2006-08-01 Requirements 20.00 8.00
Smith, John 2006-08-02 Requirements 20.00 8.00
Smith, John 2006-08-03 Design 80.00 8.00
Doe, Jane 2006-08-01 Requirements 5.00 4.00
Doe, Jane 2006-08-01 Design 30.00 4.00

And I've developed a matrix report that looks like this:

AUGUST
1 2 3 4 5
-
Name 1 Requirements 8.00 8.00
Design 8.00
Name 2 Requirements 4.00
Design 4.00

What I need to do, however, is add the "ETC" value at the END of the matrix
row for each Task for each Resource, so that it looks like this:

AUGUST
1 2 3 4 5 ETC

Name 1 Requirements 8.00 8.00 20.00
Design 8.00 80.00
Name 2 Requirements 4.00 5.00
Design 4.00 30.00

I can't seem to add a column to the matrix to represent the ETC column. What
would be the best way to accomplish this?

Thanks in advance for any replies,
Dr Jazz

Dr.

You can right click on Column 5 and select "Add Column" this will create ETC, this will add a static column to the matrix.

Hammer

|||

Hammer2 wrote:

You can right click on Column 5 and select "Add Column" this will create ETC, this will add a static column to the matrix.

Thanks for the reply, Hammer. Unfortunately, "Column 5" doesn't exist in the report designer because this is a matrix control. The only items available are "Column Groups" and I have two defined: one for the month, then another for the actual day (since the date range is a parameter specified by the user). I can right-click and add more Column Groups, but I cannot add a single static column, thus my dilemma.

Cheers,
Dr. Jazz

|||

Humm,

Puzzling because I have multiple Matrix reports and a number of them have static column, you should be able to "Add a column" this is a static column - you can also google matrix reports and static columns, this should bring up some examples.

Hope this helps

Hammer

|||

Hi Hammer,

Thanks for your continued patience and assistance. Check out this web page that will hopefully illustrate my problem better:

http://www.duotronic.com/matrix/matrix.htm

As you can see, I am able to get an "Add column" menu item, but I don't want it to be part of the group because this value will then be displayed for every day in the group. I just want it displayed at the end of the row. Is there a way to do this?

Cheers,
Dr Jazz

|||You arent using your query correctly as you should be using aggregates on that column as well.
SQL cannot tell if you just want a column there since your report is a grouping report.

Can you post your SQL query.

http://jhermiz.googlepages.com|||

Hi jhermiz. Thanks for your reply. I have no doubt that the structure of my query is not exactly compatible with how Reporting Services requires it. My query is listed below:

SELECT
emp.LastName + ', ' + emp.FirstName AS Resource,
cal.calendarDay,
task.taskID,
task.taskCode,
task.description,
tass.etcHours,
-- Get billable hours for this user on this day
SUM(tae.effortHours) AS effortHours

FROM
dbo.udf_getDateRangeTable(@.startDate, @.endDate) AS cal
CROSS JOIN HR.dbo.Employee AS emp
LEFT JOIN dbo.TaskAssignment AS tass ON tass.resourceID = emp.employeeID
LEFT JOIN dbo.TaskActualEffort AS tae ON tae.assignmentID = tass.assignmentID
AND tae.startDate = cal.calendarDay
LEFT JOIN dbo.Task AS task ON tass.taskID = task.taskID
LEFT JOIN dbo.TrackingItem AS ti ON task.trackingItemID = ti.trackingItemID

WHERE
ti.masterProjectID = @.masterProjectID
AND emp.employeeID IN (SELECT employeeID FROM HR.dbo.ProjectEmployeeAssignment WHERE projectID = @.masterProjectID)
-- Only return resources who have actually billed time in the reporting period
AND EXISTS
(
SELECT 1
FROM
dbo.TaskAssignment AS tass1
INNER JOIN dbo.Task AS task1 ON tass1.taskID = task1.taskID
INNER JOIN dbo.TaskActualEffort AS tae1 ON tass1.assignmentID = tae1.assignmentID
INNER JOIN dbo.TrackingItem AS ti1 ON ti1.trackingItemID = task1.trackingItemID
WHERE
tass1.resourceID = emp.employeeID
AND tae1.startDate BETWEEN @.startDate AND @.endDate
AND ti1.masterProjectID = @.masterProjectID
)

GROUP BY
emp.LastName + ', ' + emp.FirstName,
calendarDay,
emp.employeeID,
task.taskID,
task.taskCode,
task.description,
tass.etcHours

ORDER BY
task.taskID,
Resource,
cal.calendarDay

Cheers,
Andre

|||

Hey Dr.

Nice pages by the way, Okay, I understand the nature of your problem - When you "Add the column" you can use the "inscope" expression to tell the which group to display for.

Does that make sense?

Hammer