Problem: Reporting using Report Model

I'm having a reporting problem when using the report model as my data source. I was able to duplicate the problem by trying to create a report dataset using the ModelTutorial (based on the AdventureWorks database) as my data source.

For example, say you wanted to get all products which have illustrations. The query shown below would retrieve the 23 rows as expected (don't worry about duplicate products for this example).

SELECT P.ProductID, P.ProductNumber, P.Name, I.IllustrationID, I.ModifiedDate
FROM Production.Product P
INNER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID
INNER JOIN Production.ProductModelIllustration PMI ON PM.ProductModelID = PMI.ProductModelID
INNER JOIN Production.Illustration I ON PMI.IllustrationID = I.IllustrationID

Trying to create a dataset to get this same information using the report model as the data source is my problem. I am unable to get fields from all the following 4 tables: Product, ProductModel, ProductModelIllustration, and Illustration. I can get fields from the ProductModel, ProductModelIllustration, and Illustration tables, but not the Product table. The circle with the diagonal shows up when I try to drag a field from the Product table into the dataset area. What am I doing wrong?

Are you able to query Product table alone?|||

Yes, I can return records from the Product table alone. I created a report to display the product name and number for all products. It returned 504 rows as expected.

In addition, I can generate a dataset with fields from both the Product and ProductModel table. However, when I try to add a field from the ProductModelIllustration table, I am denied (circle with the diaganol line).

|||It is hard to guess without knowing your model.|||

As mentioned above, I duplicated the problem with the ModelTutorial Model (based on the AdventureWorks database). You can duplicate this model by following the "Creating a Report Model" tutorial in SQL Server Books Online (see below link).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/395a4f26-6401-4ee1-8658-be8b43cc70a8.htm

If the link above does not work for you, you can find the tutorial in books online by searching for tutorials [Reporting Services], then click on Reporting Services Tutorials link, then click on the Creating a Report Model link. Once you have the model built and deployed, you should have no problem duplicating my problem.

|||

My understaning is that relations are:

Product -(many to one)- ProductModel -(one to many)-Illustration

This case is not supported automatically.

|||

You are close, but the relations are as follows:

Product -(many to one)- ProductModel -(one to many)- ProductModelIllustration -(many to one)- Illustration

This scenario will come up frequently for report model designers. In fact, it will occur every time there is a junction (aka intersection) table in the model.

If this case is not supported automatically, can you explain in detail the best way to handle it manually (e.g. view in database, new named query in model, etc.)?

Thanks for your help.

|||

Transition many-one-many is not supported. Additional relation (in this case ProductModelllustration -Illustration) is not important.

Easiest way to do it is to add named query.