Alaising the same table multiple times

To All--

First off, I'm new to SQL Server and apologize if this is a trivial question.

What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.

Table1
Emp_Number
Emp_Code_1
Emp_Code_2

Table2 (Contains a list of codes and their related descriptions)
Emp_Code
Long_Desc
Short_Desc

I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.

Hope this makes sense.

CSelect t1.Emp_Number, t1.Emp_Code_1, t2a.Short_Desc, t1.Emp_Code_2, t2b.Short_Desc
FROM Table1 t1
INNER JOIN Table2 t2a on t2a.Emp_Code = t1.Emp_Code_1
INNER JOIN Table2 t2b on t2b.Emp_Code = t1.Emp_Code_2


Same table, different alias.

Aggregating two dimension members

Hi to everybody!

First of all I'd like to apologize for my english!
...Then, this is the problem of a newbie of Sql Server Analysis Services, cubes and so on...,
I've created a cube, and I can easily browse it: what I wanted to obtain is a sort of Profit and Loss statement: dimensions are Revenues, Costs and periods, while "amount" is the only measure. It works!
But my question is: how can I aggregate two member of a same dimension? I.e.: how to create a new "row" in my cube where there are cost A plus cost B?
Does it make sense?

Thanks a lot

Jane Mischis

you can create a DIMENSION calculated member in the CALCULATIONS tab and have its parent hierarchy refer to COST hierarchy in the cost dimensions

in the expressions put

(<uniquename of cost A> + <uniquename of cost B>)

the new calcultion will appear as an additional row in the cost dimension.

hope this helps

|||Thank you so much for the answer..

Actually, what you suggest me is what I had done before writing here, but it didn't work, because:
- when I browse the cube, the new calculation does appear as an additional row in my members cost dimension, but it's transparent, like if it was hidden... and when I try to drag and drop it in row fields column on the right, I can't do it. I can just drop it above, in the filter box...

What's wrong?

Thank you,

Jane Mischis
|||

did you try doing it on a different browser (example Proclarity), cos the default browser that comes with analysis services can't perform all jobs.

|||No I didn't try, but I can't try, because I don't have it and I can't think I need it to solve my problem.
But I think the problem is elsewhere, maybe defining what dimensions and mesaures are.
The most important dimension is really "costs", but just the name of them, numbers come out from measure "amount"... And if I try to add a calculated Measures member, it works (i.e. "amount/1000")... but what I'd like to reach is just aggregate two names and having related sum i amounts.

Maybe I've to study a lot more...

Thank you however, Christina

Jane

|||Oh, great, I solve it.
I had just to put "all" in father member...

Thanks again

Jane

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