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'

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

sql

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

Model Builder Field Order and Inheritance

When I create a report model, it is easy to change the order in a given entity. Does anyone know how to order the fields in an entity when you are using inheritance or inlining? I want to have the fields in alphabetical order so that the end user doesn't have to hunt for the correct field.

The other item I need to find out is how to customize the prefixes that inlining uses. Any ideas there?

Ron

Specifying field order across entities is not supported in this release. One thing you can do to mitigate this is use field folders so the user has a "tree" to search instead of a long list that is not ordered very well. In the case of inlining, you can actually place the role in a folder by itself, and all its fields will show up there when the role is expanded.

By "prefixes" I assume you mean the contextual naming that is applied based on the Role.ContextualName and Attribute.ContextualName properties. These are the mechanism for controlling this behavior.

Hope this helps!

|||

That was right on the money with both of the questions. That folder trick is really helpful in cleaning up some seriously unmanageable lists of fields.

R

sql

Missing rows when viewing data in Report Builder. What gives?

I am very confused. To simplify this, I have a report model with two tables named Unit and Unit_Type. I have created and published this model to our Report Server.

When I build an ad-hoc report against this model, I end up with 2 different scenarios.

The first scenario is as follows:

I select a single attribute from the entity "Unit_Number" and add it to my report. I run the report and the footer of the report displays 10576 rows. I then export the report to Excel and when I check the actual number of records, there are only 7652 rows. I have confirmed the correct number of records by writing a simple SQL query which also generates a result with 10576 rows.

The same results are achieved if I add the attribute "Type_Code" from the "Unit_Type" table IF the Type_Code is part of the "Unit" group. To be clear, if I first add an attribute from the Unit table and then add an attribute from the Unit_Type table, my report has a single group and both entities belong to that group. This format will never give me correct number of records.

One more important piece of info... I have validated that there are absolutely NO duplicate records in my result set. All 10575 rows are unique.

The second scenario is as follows:

Rather than first selecting from the Unit table, I select "Unit_Type" from the "Unit_Type" table and then I add the attribute "Unit_Number" from the Unit table. Report Builder creates 2 groups when I set up the report this way.

The result when I run this report is 10576 rows when I import my report using this scenario into Excel! This is correct!

Question

So, why does Report Builder generate an incorrect result set unless attributes from both entities are included in the report and the attributes are in seperate groups? This makes no sense to me and will be extremely difficult to explain to our users.

I would really appreciate any feedback on this.

Scott
Are you running Service Pack 1? We fixed a bug with the wrong number of rows in Report Builder.|||I have the same problem, the solution was not to use Report Builder because it give you wrong results , its not a question of service pack or version, if you have a tool that don't give correct result's can you tell your client, "sorry but you have order $100.000 of product that you have in stock call microsoft they will give your money back"

missing Report Builder menu item after migration from RS2000 to 2005

I've done a clean install of RS2005 and then migrated old RS2000
database to RS2005.
After the migration the Report Builder menu item is missing.
I have system administrator level permission.
I need your advice.
TIA,
Kamelok, have solved it
there was one task unchecked in system administrator role definition -
"Execute Report Definitions"
Kamel
On 26 Sty, 16:40, "kamel" <kwic...@.gmail.com> wrote:
> I've done a clean install of RS2005and then migrated oldRS2000
> databasetoRS2005.AfterthemigrationtheReportBuildermenuitemismissing.
> I have system administrator level permission.
> I need your advice.
> TIA,
> Kamel