Dear ppl, I got a Report Model Project, in which i have created a model of my Database and deployed it on the server... Then I have a Report Server project for my reports. The reports are using Report Model as the DataSource... Now in the Report Designer, inside a report, whenI define query forDataset using Report Model , I can't find a way to write a left Outer Join query... It always performs Inner Joins... was wonderingether Report model supportsOuter Joins?... Imight achive this using two different datasets but then i'll end up using two data regions e.g. 2 tables, but i don't want to do that... instead Iwantall of my data to appear in a single table which can reference tosingledataset. Is there a way to define left outer joins using Report model ? I am new to this Reporting Servces and I might be missing something here... would any body please point out that

I am working inside Report Designer in VS.NET 2005

I've run into the same issue. I created a Report Model where the data source view has 4 tables (3 tables linked to 1 with a key). When I deploy the model and run Report Builder the entities always join by inner join. There doesn't appear to be a way to perform an outer join in Report Builder or define the modelwith outer joins. Unless I'm missing something.....

could you guys describe your model a bit. the part that contains entities where you want to see left outer?

I am having the same problem. Since the other guys haven't posted details yet, here are mine: I have two tables, one of which is dependent on the other (has a foreign key relationship to it). Although my actual DB is more complicated than this, I can easily simplify the DataSourceView to just these two tables, so I will write this as though they are the only things in the DB. Table #1, called SystemType, is defined as follows: SystemTypeGuid: uniqueidentifier, not null, primary key. Name: nvarchar(50), not null. CSINumber: int, not null. Table #2, called SystemTypeTag, is defined as follows: SystemTypeGuid: uniqueidentifier, not null, foreign key into SystemType. TagName: nvarchar(50), not null. Together with SystemTypeGuid, this forms a two-part primary key (so the combination of SystemTypeGuid and TagName must be unique, but neither field has to be unique by itself). TagValue: int (actually, an enum in the code), not null. Options: nvarchar(MAX), nullable. I have defined a DataSource to connect to this DB, and a DataSourceView named MasterTables.dsv in Visual Studio 2005 (in a test project with little else in it). The DSV includes the two tables and the relationship between them. When I click on the relationship and choose Edit Relationship, the source is listed as SystemTypeTag with column SystemTypeGuid, and the destination as SystemType with column SystemTypeGuid. I find the definitions of "source" and "destination" to be quite peculiar (I would think any reasonable person would assume "source" meant the table with the primary key), but there is enough additional text so that theresult is clear. I have then defined a ReportModel (autogenerated)named MasterTables.smdl, which includes the two tables. I trimed this model of some unnecessary aggregation (the CSI Number field is a code and therefore it is not useful to sum, average, min, or max it), but basically arrived at what appears to be a reasonable-looking model: System Type includes #System Types (a means of counting the values), System Type Guid (hidden, but it wasn't in earlier versions so this shouldn't be material), Name, CSI Number, and the role System Type Tags. The role was originally generated with the property Cardinality set to OptionalMany, which sounds correct and was not touched. The role identifies a "related role" called "System Type Gu" (nice naming), which was originally generated with the Cardinality set to One, but which I changed to OptionalOne without any improvement. The model, as described, was deployed to my report database using the Deploy option in VS 2005. Deployment was successful, although after the first time I have gotten a warning about the DataSource (already exists and OverwriteDataSources is not set) that I have not been able to fix. Finally, in the Report Builder (the web tool) I attempt to create a new report that shows all System Types, with System Type Tags if any exist. I have found no way to do this. Any attempt to use data from both System Type and System Type Tag results in an inner join (or, a table structure based on selecting from System Type Tag first, which amounts to the same thing). So, I can get all System Types that have tags -- but I cannot get System Types for which no System Type Tag exists. The behavior I am getting amounts to the following SQL statement: SELECT * FROM SystemTypeTag INNER JOIN SystemType ON SystemTypeTag.SystemTypeGuid = SystemType.SystemTypeGuid The behavior I desire is: SELECT * FROM SystemType LEFT JOIN SystemTypeTag ON SystemType.SystemTypeGuid = SystemTypeTag.SystemTypeGuid But, there seems to be no way to specify this either in the model or in the report (Report Builder; I am well aware that I can control the SQL absolutely in the Report Designer in VS 2005 and that is not my question). Am I missing something?

This is a known limitation of the query language. There are some workarounds. Ok you have two tables, SystemTypes and TypeTags. One system type can have zero or more tags. So this is naturally one-to-optional-many relationship:ST (1-1) ----< (0-*) TTIf you build a model for that schema you will get ST entity with OptionalMany role pointing towards TT entity and "One" role pointing backwards.The current query language is designed to model questiones aligned around only one entity, it is called base entity.One can ask additional fields and aggregates coming from other entities, but these values will always be related to the base entity.This causes the limitation. The question you want to ask relates to both entities "Show me all instances of ST and TT related to each other". This is in fact full outer join because you want to see all types including those with no tags and you want to see all tags including those without corresponding types.Right now if you put ST as base entity, you will get the closest approximation to the answer you want, with one problem: in this question you can only pull aggregates from TT. This is because TT relates to ST as many-to-one and you can not bring scalars (line TT.Name) from TT into ST level without blowing cardinality of ST. You want cardinality of ST preserved because your question was about ST.The other way around is to try to ask the question about TT. Since ST relates to TT as one-to-many, you can bring scalars to the level of TT and preserve the cardinality of the base entity in the answer. There is another problem though. The question was about tags. So the answer will also be about tags, and types attached to these tags. That means types without tags are ingnored. Even if you change optionality of the role on TT from One to OptionalOne, you still get answer about tags, not about types. So some types will still be omitted. Ok, what is the workaround?If you want to ask question about two or more entities using only SMQL constructs, currently you can't.But you can modify your model and create a "junction" entity, based on an outer join (left outer in your case).The outer join will contain keys from the ST and TT:select ST.key, TT.key from ST left outer join TT on ST.key = TT.keyThe junction entity will have roles pointing to ST and TT: ST (1-1) ---< (1-*) JE (1-1) --- (0-1) TT Now you can ask question against JE which is semantically equal to the question about both ST and TE. And you will see all types and all tags.

Hi Alexander,Well i have two simple tables Job and Activity. Job has one to many relationship with Activity. What I want in my report is all the jobs and activities. The activities will be grouped by Job. Job with no activities will also appear in the report.However when i use report model, only the jobs that have activities in the Activity table are returned in the report which means an inner join, which is equivalent to select (all columns) from Job inner join Activity on Job.No = Activity.JobNoWhat i want is an outer join so that I can have the Jobs with no activites as well.select (all columns) from Job left outer join Activity on Job.No = Activity.JobNoI hope u understand my problem. Its pretty simple. Hope to hear from u soonRegardsNabeel Farid

This is exactly the case I described above. We do not do plain joins from to-one entity to to-many. Thus you can not get left outer from Job to Activity.You can do left outer from Activity to Job, but it would not help because you will not list all jobs in this case. You need to create junction entity like I described above. We are aware of this issue and looking to fix it in Katmai.

Hi Alexandre I follow your indications and I like to report what happens Additonal AssumptionsST table has numeric variables (measures) name one of then NVSTST table has attributes , name one of them ATSTTT table has numeric variables (measures) name one of then NVTTTT table has attributes , name one of them ATTT I name junction Entity 'Variables' and define on it (in Report Model) the follow expressions: TotalNVST = SUM(NVST) and TotalNVTT = SUM(NVTT) If I design a report in Report Builder with ATST and ATTT two tags appears, then dragging TotalNVST to the ATST tag and Total NVTT to the ATTT tag it shows each item of ATST with the righ total of TotalNVST and each item of ATTT with the righ sub-total of TotalNVTT. THAT'S VEEERY GOOOOD!!! BUT when I try to define a formula like TotalNVTT/TotalNVST the results dont match :( The reason is that Formula is evaluated in scope of ATTT and TotalNVST is a expresion on the model that has a different evaluation in ATST scope that in ATTT scope, it's a kind of total of NVST in transaction of each item of ATTT that TT.key = ST.key (a kind of prorrated but not exactly a prorrated) 1. If users want to divide with "prorrated" TotalNVST they have to select them in ATTT tag this is easy to understand and explain,but when both numbers appears in table of results of the report. Explain Users that they cant divide them its a VERY HARD TASK 2. TotalNVST in ATTT scope could have sense (if it's a nattural master-detail relationship), but also could have NOT. For example if ST is like a daily budget table (identifying by locationid and timeid) and TT is like Expenses Table (identifying by transaction id, but AK by locationid, timeid and others id) in this case there is not a budget for items of ATTT so the total doesnt have a real meaning So, making your suggestions its possible to make reports of measures of ST and TT like a REAL OUTER JOIN (good!) And the question is if I Can combine measures of ST and TT in formulas that function in ST scope like and outer join. RegardsJulio Diaz

This is what I did to get the outer join - hope it helps your problem: 1.Created a new dataset 2. Click the Generic Query Designer button to switch to the Graphical Query designer 3. Right click in the diagram pane select Add table and selected the tables requred (in your case Job and Activity) 4. The tables are added to the diagram pane. If you defined the relation between the table the relation link will show up 5. Right click the box in the middle of the link between the two tables 6. Select the command Select "All Rows from job" from the context menu. That will give you the left outer join. Then select the fields required from both tables I hope this solve your problem aboutleft outer join.

Thanks Muthu, But I'm asking for outer joins in Report Model to use with Report Builder, your indications are usefull to make querys in Report Designer. Alexandre give some very good indications to make it, I mean Calculated Members on the detail Table scope shows right values , Calculated Members in Header Table scope show right values too, but Calculated Members of Header Table can also be evaluated in Detail Table scope and results not always are consistent. I think that probably that's the way it is, and if you want to evaluate formulas involving Calculated Members of Header and Detail you have to do it in Analysis Service Cube. Regards Julio Daz

Alexander, thanks for the post above. I have been bangning my head against this issue. You indicate that the "junction" entity is created in the model. How does one create an entity in a model based on a query? When i try to bind my new entity, i only have existing items from my data source view as options. I have created a named query in my dsv to perform the left joinbut cannot get the role for it to work in my model. Thanks much.

We did found an work around for left outer join, Consider two tables table1 [id1,id2]and table2 [id2], we have joined both of them with ID2 in the report model, As some of the ID fields dont have values in Table1 they are missed out. So we have created an view which hold all the value of table1 and union it table2 [Id2]values all those ids which are not part of table1. In this way we have all the ids of table2 in table1view, as you join them now in report model. We will have all the required table2 ids. And in the table1 view make your id1 and id2 as logical primary key that way it will display all records, else it will group all the null values as one. Regards, Navin

This sounds like a fundamental flaw of Report builder. We currently use impromptu which let's you use outer joins. Is there any plan to fix this?

Yes, we have plans in the works to address this.

My suggestion would be is to use 'Named Query' to address issues with left outer joins in Report Model. Thanks Venkat

Hi Alex, I am evaluating a solution based on MSAS and MSRS 2008, but I can't see any direct way to configure outer join relationship in the Data Source Views other than creating Named Query. What happened to the Change Request to support outer join easily? Appreciate your clarification.

The enhancement I was referring to in my Aug 2008 post did not ship with SQL Server 2008.We are working to include it in the next SQL Server release although I can't confirm that yet.For now, Named Queries are the best option as mentioned in other posts.

So we have to wait until SQL 2012 to get this? Sweet :)LaCie drives. Failing when you need them most."La" meaning "Terrible", "Cie" meaning "customer service"

No: http://www.microsoft.com/presspass/press/2008/oct08/10-06BI08PR.mspx

Hi Aaron, kindly tell me when outer joins functions will be release.