Link to home
Start Free TrialLog in
Avatar of ShawnGray
ShawnGray

asked on

SSRS Query

I'd like my SSRS report to return every portfolio, regardless if any transactions are found.
My query (below) is looks at table vPortfolio for a list of [PortfolioCode].
Other tables and data should be returned if found but ultimately I need every [PortfolioCode] to display regardless.

Currently it seems my results omit PortfolioCodes where no [TradeAmounts] are found.

Thank you

***********
 SELECT APXUser.vPortfolioTransaction.PortfolioID, APXUser.vPortfolioTransaction.PortfolioTransactionID, APXUser.vPortfolio.PortfolioCode,
               APXUser.vPortfolio.PortfolioTypeCode, APXUser.vPortfolioTransaction.TradeDate, APXUser.vPortfolioTransaction.TransactionCode,
               APXUser.vPortfolioTransaction.TranCodeLabel, APXUser.vPortfolioTransaction.Comment, APXUser.vPortfolioTransaction.TradeAmount,
               APXUser.vContact.LastName, APXUser.vContact.FirstName, APXUser.vContactCustom.Custom01 AS CID, APXUser.vContactCustom.Custom02 AS PM,
               APXUser.vContactCustom.Custom10 AS RelClsd, APXUser.vContact.ContactCode, APXUser.vPortfolioTransaction.SecTypeCode1, APXUser.vSecurity.Ticker,
               APXUser.vSecurity.Symbol, APXUser.vPortfolioBaseCustom.ManagementAgreement AS MA, APXUser.vContact.TaxID, APXUser.vContact.ContactID,
               APXUser.vPortfolioBase.CloseDate, APXUser.vContact.BirthDate, APXUser.vPortfolio.TaxStatus
FROM  APXUser.vContact INNER JOIN
               APXUser.vContactCustom ON APXUser.vContact.ContactID = APXUser.vContactCustom.ContactID INNER JOIN
               APXUser.vPortfolio ON APXUser.vContact.ContactID = APXUser.vPortfolio.OwnerContactID LEFT OUTER JOIN
               APXUser.vPortfolioBaseCustom ON APXUser.vPortfolio.PortfolioID = APXUser.vPortfolioBaseCustom.PortfolioBaseID LEFT OUTER JOIN
               APXUser.vPortfolioBase ON APXUser.vPortfolio.PortfolioID = APXUser.vPortfolioBase.PortfolioBaseID LEFT OUTER JOIN
               APXUser.vPortfolioTransaction ON APXUser.vPortfolio.PortfolioID = APXUser.vPortfolioTransaction.PortfolioID LEFT OUTER JOIN
               APXUser.vSecurity ON APXUser.vPortfolioTransaction.SecurityID1 = APXUser.vSecurity.SecurityID
GROUP BY APXUser.vPortfolioTransaction.PortfolioID, APXUser.vPortfolioTransaction.PortfolioTransactionID, APXUser.vPortfolioTransaction.TradeDate,
               APXUser.vPortfolioTransaction.TransactionCode, APXUser.vPortfolioTransaction.TranCodeLabel, APXUser.vPortfolioTransaction.Comment,
               APXUser.vPortfolio.PortfolioCode, APXUser.vPortfolioTransaction.TradeAmount, APXUser.vContact.LastName, APXUser.vContact.FirstName,
               APXUser.vContactCustom.Custom01, APXUser.vContactCustom.Custom02, APXUser.vContactCustom.Custom10, APXUser.vContact.ContactCode,
               APXUser.vPortfolioTransaction.SecTypeCode1, APXUser.vSecurity.Ticker, APXUser.vSecurity.Symbol, APXUser.vPortfolioBaseCustom.ManagementAgreement,
               APXUser.vPortfolio.PortfolioTypeCode, APXUser.vContact.TaxID, APXUser.vContact.ContactID, APXUser.vPortfolioBase.CloseDate, APXUser.vContact.BirthDate,
               APXUser.vPortfolio.TaxStatus
HAVING (APXUser.vPortfolioTransaction.TradeAmount <= @MaxTradeAmt) AND (APXUser.vPortfolioTransaction.TransactionCode = 'li' OR
               APXUser.vPortfolioTransaction.TransactionCode = 'ti') AND (APXUser.vPortfolioTransaction.TradeDate BETWEEN @StartDate AND @EndDate) AND
               (APXUser.vPortfolio.PortfolioTypeCode = N'IRA' OR
               APXUser.vPortfolio.PortfolioTypeCode = N'IRA Rollover' OR
               APXUser.vPortfolio.PortfolioTypeCode = N'Roth IRA')
**************
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ShawnGray
ShawnGray

ASKER

Thank you; that seemed to do the trick.
Really appreciate the help.