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.vPortfolioTransact ion.Portfo lioID, APXUser.vPortfolioTransact ion.Portfo lioTransac tionID, APXUser.vPortfolio.Portfol ioCode,
APXUser.vPortfolio.Portfol ioTypeCode , APXUser.vPortfolioTransact ion.TradeD ate, APXUser.vPortfolioTransact ion.Transa ctionCode,
APXUser.vPortfolioTransact ion.TranCo deLabel, APXUser.vPortfolioTransact ion.Commen t, APXUser.vPortfolioTransact ion.TradeA mount,
APXUser.vContact.LastName, APXUser.vContact.FirstName , APXUser.vContactCustom.Cus tom01 AS CID, APXUser.vContactCustom.Cus tom02 AS PM,
APXUser.vContactCustom.Cus tom10 AS RelClsd, APXUser.vContact.ContactCo de, APXUser.vPortfolioTransact ion.SecTyp eCode1, APXUser.vSecurity.Ticker,
APXUser.vSecurity.Symbol, APXUser.vPortfolioBaseCust om.Managem entAgreeme nt AS MA, APXUser.vContact.TaxID, APXUser.vContact.ContactID ,
APXUser.vPortfolioBase.Clo seDate, APXUser.vContact.BirthDate , APXUser.vPortfolio.TaxStat us
FROM APXUser.vContact INNER JOIN
APXUser.vContactCustom ON APXUser.vContact.ContactID = APXUser.vContactCustom.Con tactID INNER JOIN
APXUser.vPortfolio ON APXUser.vContact.ContactID = APXUser.vPortfolio.OwnerCo ntactID LEFT OUTER JOIN
APXUser.vPortfolioBaseCust om ON APXUser.vPortfolio.Portfol ioID = APXUser.vPortfolioBaseCust om.Portfol ioBaseID LEFT OUTER JOIN
APXUser.vPortfolioBase ON APXUser.vPortfolio.Portfol ioID = APXUser.vPortfolioBase.Por tfolioBase ID LEFT OUTER JOIN
APXUser.vPortfolioTransact ion ON APXUser.vPortfolio.Portfol ioID = APXUser.vPortfolioTransact ion.Portfo lioID LEFT OUTER JOIN
APXUser.vSecurity ON APXUser.vPortfolioTransact ion.Securi tyID1 = APXUser.vSecurity.Security ID
GROUP BY APXUser.vPortfolioTransact ion.Portfo lioID, APXUser.vPortfolioTransact ion.Portfo lioTransac tionID, APXUser.vPortfolioTransact ion.TradeD ate,
APXUser.vPortfolioTransact ion.Transa ctionCode, APXUser.vPortfolioTransact ion.TranCo deLabel, APXUser.vPortfolioTransact ion.Commen t,
APXUser.vPortfolio.Portfol ioCode, APXUser.vPortfolioTransact ion.TradeA mount, APXUser.vContact.LastName, APXUser.vContact.FirstName ,
APXUser.vContactCustom.Cus tom01, APXUser.vContactCustom.Cus tom02, APXUser.vContactCustom.Cus tom10, APXUser.vContact.ContactCo de,
APXUser.vPortfolioTransact ion.SecTyp eCode1, APXUser.vSecurity.Ticker, APXUser.vSecurity.Symbol, APXUser.vPortfolioBaseCust om.Managem entAgreeme nt,
APXUser.vPortfolio.Portfol ioTypeCode , APXUser.vContact.TaxID, APXUser.vContact.ContactID , APXUser.vPortfolioBase.Clo seDate, APXUser.vContact.BirthDate ,
APXUser.vPortfolio.TaxStat us
HAVING (APXUser.vPortfolioTransac tion.Trade Amount <= @MaxTradeAmt) AND (APXUser.vPortfolioTransac tion.Trans actionCode = 'li' OR
APXUser.vPortfolioTransact ion.Transa ctionCode = 'ti') AND (APXUser.vPortfolioTransac tion.Trade Date BETWEEN @StartDate AND @EndDate) AND
(APXUser.vPortfolio.Portfo lioTypeCod e = N'IRA' OR
APXUser.vPortfolio.Portfol ioTypeCode = N'IRA Rollover' OR
APXUser.vPortfolio.Portfol ioTypeCode = N'Roth IRA')
**************
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.vPortfolioTransact
APXUser.vPortfolio.Portfol
APXUser.vPortfolioTransact
APXUser.vContact.LastName,
APXUser.vContactCustom.Cus
APXUser.vSecurity.Symbol, APXUser.vPortfolioBaseCust
APXUser.vPortfolioBase.Clo
FROM APXUser.vContact INNER JOIN
APXUser.vContactCustom ON APXUser.vContact.ContactID
APXUser.vPortfolio ON APXUser.vContact.ContactID
APXUser.vPortfolioBaseCust
APXUser.vPortfolioBase ON APXUser.vPortfolio.Portfol
APXUser.vPortfolioTransact
APXUser.vSecurity ON APXUser.vPortfolioTransact
GROUP BY APXUser.vPortfolioTransact
APXUser.vPortfolioTransact
APXUser.vPortfolio.Portfol
APXUser.vContactCustom.Cus
APXUser.vPortfolioTransact
APXUser.vPortfolio.Portfol
APXUser.vPortfolio.TaxStat
HAVING (APXUser.vPortfolioTransac
APXUser.vPortfolioTransact
(APXUser.vPortfolio.Portfo
APXUser.vPortfolio.Portfol
APXUser.vPortfolio.Portfol
**************
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Really appreciate the help.