Monday, March 26, 2012

outer join similar to Access

Ok fellas, I need your help. I have a crosstab query that I wrote in Access
that lists all my marketers regardless of their referrals. The outer joins
selects all rows from marketer table. Now in SSRS I recreated the same query,
setup the matrix and populated it correctly. Problem is, that if the marketer
had no referrals, their name does not show up. I assumed that the outer join
would take care of that. Do I need to alter the query in some way to treat
nulls as zeros? Its a very simple query but I cannot determine where to put
the conditioning. Many thanks.isnull(numofreferralscolumn, 0) as numofreferralscolumn
in the select would be the easiest way.
--
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> Ok fellas, I need your help. I have a crosstab query that I wrote in
> Access
> that lists all my marketers regardless of their referrals. The outer joins
> selects all rows from marketer table. Now in SSRS I recreated the same
> query,
> setup the matrix and populated it correctly. Problem is, that if the
> marketer
> had no referrals, their name does not show up. I assumed that the outer
> join
> would take care of that. Do I need to alter the query in some way to treat
> nulls as zeros? Its a very simple query but I cannot determine where to
> put
> the conditioning. Many thanks.|||or you might need to do some more hocus-pocus.. i have to do things
like this to get 0 members showing up in some cubes for example
say you got a table that lists OrderTotals.. right?
Select CustomerID, OrderID, OrderTotalA, OrderTotalB From OrderTotals
UNION ALL
Select CustomerID, 0,0,0 From Customers
Michael Abair wrote:
> isnull(numofreferralscolumn, 0) as numofreferralscolumn
> in the select would be the easiest way.
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> > Ok fellas, I need your help. I have a crosstab query that I wrote in
> > Access
> > that lists all my marketers regardless of their referrals. The outer joins
> > selects all rows from marketer table. Now in SSRS I recreated the same
> > query,
> > setup the matrix and populated it correctly. Problem is, that if the
> > marketer
> > had no referrals, their name does not show up. I assumed that the outer
> > join
> > would take care of that. Do I need to alter the query in some way to treat
> > nulls as zeros? Its a very simple query but I cannot determine where to
> > put
> > the conditioning. Many thanks.

No comments:

Post a Comment