Model generation (for Report Builder)

I have had to add Calculated columns to some tables in my Data Source View in
order to correctly link some tables but this kills the model generation.
For example, although the job table has a CompanyID column, the job_task
table does not - but I need to be able to lookup a status in CompanyReason
which is indexed on CompanyID and PauseReason (this column is in job_task).
So I added 'theCompanyID' as a Calculated columns to job_task with the
following code:
SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
j.job_id = jt.job_id
Makes sense? But when I create a model with it - this is the error message:
--
An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Line 5: Incorrect syntax near ')'.
Command:
SELECT
COUNT([notes]),
COUNT(DISTINCT [notes]),
COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
j.job_id = jt.job_id)),
COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j
on j.job_id = jt.job_id)),
COUNT('task_status'),
COUNT(DISTINCT 'task_status')
FROM [dbo].[job_task] t
--
Of course, the error is correct - what I need to know is how to either
bypass the column uniqueness test FOR THIS ONE column or another method to
create the link in the Data Source View.
Thank you,
ScottI would use SSIS to get this set up. I have not done such a thing, but since
SSIS can provide report datasources that's the route I would look at.
"ScottB" <ScottB@.discussions.microsoft.com> wrote in message
news:95A89A5B-F36B-4056-96BF-A748B613CA25@.microsoft.com...
>I have had to add Calculated columns to some tables in my Data Source View
>in
> order to correctly link some tables but this kills the model generation.
> For example, although the job table has a CompanyID column, the job_task
> table does not - but I need to be able to lookup a status in CompanyReason
> which is indexed on CompanyID and PauseReason (this column is in
> job_task).
> So I added 'theCompanyID' as a Calculated columns to job_task with the
> following code:
> SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
> j.job_id = jt.job_id
>
> Makes sense? But when I create a model with it - this is the error
> message:
> --
> An error occurred while executing a command.
> Message: Cannot perform an aggregate function on an expression containing
> an
> aggregate or a subquery.
> Line 5: Incorrect syntax near ')'.
> Command:
> SELECT
> COUNT([notes]),
> COUNT(DISTINCT [notes]),
> COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
> j.job_id = jt.job_id)),
> COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job
> j
> on j.job_id = jt.job_id)),
> COUNT('task_status'),
> COUNT(DISTINCT 'task_status')
> FROM [dbo].[job_task] t
> --
> Of course, the error is correct - what I need to know is how to either
> bypass the column uniqueness test FOR THIS ONE column or another method to
> create the link in the Data Source View.
> Thank you,
> Scott|||Hi,
I am new to SSIS. I have been searching for some examples to
use Expression Builder in SSIS to use derived columns
transformation...I basically want to add a new column to my data source
on the run and add this column as the 2nd column before loading into
destination. How can I achieve this? Plz help. Is there a link where I
can get examples online'