Outer Joins in Report Model

Hi,
I already posted that in the MSDN technical forums... But I hope that here
are some other experts perhaps knowing what to do...
Just imagine you have two tables, i.e. customers and notes. You can have one
or more notes per customer. Your database has "clean" primary and foreing
keys defined for that scenario. You build a report model on it. The roles
are detected correctly, so there is a "optional many" cardinality on the one
side and a "one" cardinality on the other. However if you use this in a
report and grab the SQL using the SQL Profiler you'll see that an inner join
is used instead of an outer join (because you defined "OPTIONAL many" in the
model).
So what is this?
Please mark the correct answer:
( ) My fault (explain below)
( ) Bug
( ) Feature (explain below)
_______________________________________________________
Thanks ;-)
ThomasIt's pretty ambiguous whether an INNER JOIN or OUTER JOIN should be used,
without knowing the subject matter being reported.
Consider the case of a referral bonus report, where any employee can make a
referral. That's an optional condition, but you would only want those
employees who made the referral to appear. So there has to be some
discretion left to the report developer.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Thomas Pagel" <thomas.pagel@.community.nospam> wrote in message
news:O4cMFut5FHA.2888@.tk2msftngp13.phx.gbl...
> Hi,
> I already posted that in the MSDN technical forums... But I hope that here
> are some other experts perhaps knowing what to do...
> Just imagine you have two tables, i.e. customers and notes. You can have
> one or more notes per customer. Your database has "clean" primary and
> foreing keys defined for that scenario. You build a report model on it.
> The roles are detected correctly, so there is a "optional many"
> cardinality on the one side and a "one" cardinality on the other. However
> if you use this in a report and grab the SQL using the SQL Profiler you'll
> see that an inner join is used instead of an outer join (because you
> defined "OPTIONAL many" in the model).
> So what is this?
> Please mark the correct answer:
> ( ) My fault (explain below)
> ( ) Bug
> ( ) Feature (explain below)
> _______________________________________________________
>
> Thanks ;-)
>
> Thomas
>|||Jeff,
but why do you have cardinality attributes like "optional many"? I think
that defines exactly what you want! And why isn't that used? And your case
would be handled by a simple filter...
Thanks,
Thomas
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%23sIqudv5FHA.1276@.TK2MSFTNGP10.phx.gbl...
> It's pretty ambiguous whether an INNER JOIN or OUTER JOIN should be used,
> without knowing the subject matter being reported.
> Consider the case of a referral bonus report, where any employee can make
> a referral. That's an optional condition, but you would only want those
> employees who made the referral to appear. So there has to be some
> discretion left to the report developer.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> "Thomas Pagel" <thomas.pagel@.community.nospam> wrote in message
> news:O4cMFut5FHA.2888@.tk2msftngp13.phx.gbl...
>> Hi,
>> I already posted that in the MSDN technical forums... But I hope that
>> here are some other experts perhaps knowing what to do...
>> Just imagine you have two tables, i.e. customers and notes. You can have
>> one or more notes per customer. Your database has "clean" primary and
>> foreing keys defined for that scenario. You build a report model on it.
>> The roles are detected correctly, so there is a "optional many"
>> cardinality on the one side and a "one" cardinality on the other. However
>> if you use this in a report and grab the SQL using the SQL Profiler
>> you'll see that an inner join is used instead of an outer join (because
>> you defined "OPTIONAL many" in the model).
>> So what is this?
>> Please mark the correct answer:
>> ( ) My fault (explain below)
>> ( ) Bug
>> ( ) Feature (explain below)
>> _______________________________________________________
>>
>> Thanks ;-)
>>
>> Thomas
>|||Hi Thomas,
Welcome to MSDN Managed Newsgorup!
I understood you concern is why inner join is used instead of outer join
since "optional many" defined. If I have misunderstood your concern, please
feel free to point it out.
Yes, I have tested it on my side and I believe this is a "by design"
feature of Report Model.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
thanks for the reply... That's really bad news and I really don't understand
what the use of the cardinally attribute is if it's not used...
Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:tu4xGR26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Thomas,
> Welcome to MSDN Managed Newsgorup!
> I understood you concern is why inner join is used instead of outer join
> since "optional many" defined. If I have misunderstood your concern,
> please
> feel free to point it out.
> Yes, I have tested it on my side and I believe this is a "by design"
> feature of Report Model.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Thomas,
I have submit this to our development team via internal channel. If there
is any update, I will reply here.
You are also encouraged to submit this feeling to the MSDN Product Feedback
Center below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Thanks so much for your feedback and question.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.